[Free] 2019(Nov) EnsurePass Microsoft 70-765 Dumps with VCE and PDF 31-40

Get Full Version of the Exam

Question No.31

You administer a Microsoft SQL Server 2014 database named Contoso on a server named Server01.

You need to diagnose deadlocks that happen when executing a specific set of stored procedures by recording events and playing them back on a different test server.

What should you create?

  1. A Database Audit Specification

  2. A Policy

  3. An Alert

  4. A SQL Profiler Trace

  5. A Resource Pool

  6. An Extended Event session

  7. A Server Audit Specification

Correct Answer: D


Use SQL Server Profiler to identify the cause of a deadlock. A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. Using SQL Server Profiler, you can create a trace that records, replays, and displays deadlock events for analysis.



Question No.32

You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size.

The Sales database is configured as shown in the following table.


You discover that all files except Sales_2.ndf are corrupt.

You need to recover the corrupted data in the minimum amount of time. What should you do?

  1. Perform a file restore.

  2. Perform a transaction log restore.

  3. Perform a restore from a full backup.

  4. Perform a filegroup restore.

Correct Answer: A


In a file restore, the goal is to restore one or more damaged files without restoring the whole database.


https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/file-restores-simple- recovery-model

Question No.33

You administer a Microsoft SQL Server 2014.

A process that normally runs in less than 10 seconds has been running for more than an hour.

You examine the application log and discover that the process is using session ID 60. You need to find out whether the process is being blocked.

Which Transact-SQL statement should you use?

  1. EXEC sp_who 60

  2. SELECT * FROM sys.dm_exec_sessions WHERE sessionid = 60

  3. EXEC sp_helpdb 60


Correct Answer: A


sp_who provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session.


Displaying a specific process identified by a session ID EXEC sp_who #39;10#39; specifies the process_id;


https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-who- transact-sql

Question No.34

You administer a Microsoft SQL Server 2014 database named Orders. Orders contains a table named OrderShip that is defined as follows:


A NULL value represents a domestic order. Ninety percent of the values in CountryCode are NULL.

Customers require a procedure that will return orders for all customers from a specified country. You create a new procedure:


Performance on this procedure is slow.

You need to alter the schema to optimize this query. Objects created must use a minimum amount of resources.

Which Transact-SQL statement should you use?

  1. CREATE NONCLUSTERED INDEX IX_CountryCode ON Ordership (CountryCode) WHERE CountryCode IS NOT NULL

  2. CREATE STATISTICS ST_CountryCode ON OrderShip (CountryCode) WHERE CountryCodeIS NOT NULL

  3. CREATE CLUSTERED INDEX IX_CountryCode ON OrderShip (CountryCode)

  4. CREATE INDEX IX_CountryCode ON OrderShip (CustomerID) WHERE CountryCode IS NOT NULL

Correct Answer: B


Here creating statistics is relevant. The CREATE STATISTICS command creates query optimization statistics on one or more columns of a table, an indexed view, or an external table. For most queries, the query optimizer already generates the necessary statistics for a high-quality query plan; in a few cases, you need to create additional statistics with CREATE STATISTICS or modify the query design to improve query performance.



Question No.35

You administer a Microsoft SQL Server 2014 database.

The database contains a Product table created by using the following definition:


You need to ensure that the minimum amount of disk space is used to store the data in the Product table.

What should you do?

  1. Convert all indexes to Column Store indexes.

  2. Implement Unicode Compression.

  3. Implement row-level compression.

  4. Implement page-level compression.

Correct Answer: D

Question No.36

You administer a Microsoft SQL Server 2014 instance that contains a financial database hosted on a storage area network (SAN).

The financial database has the following characteristics:

The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day.

The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database.

These data load operations must occur in the minimum amount of time.

A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.

On Wednesday at 10:00 hours, the development team requests you to refresh the database on a development server by using the most recent version.

You need to perform a full database backup that will be restored on the development server. Which backup option should you use?


  2. FULL



  5. Differential




  9. SKIP

  10. Transaction log

  11. DBO ONLY


  13. SIMPLE


Correct Answer: L


COPY_ONLY specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. A copy-only backup is created independently of your regularly scheduled, conventional backups. A copy-only backup does not affect your overall backup and restore procedures for the database.



Question No.37

You administer a Windows Azure SQL Database database named Orders. You need to create a copy of Orders named Orders_Reporting.

Which Transact-SQL command should you use?

  1. BACKUP DATABASE Orders TO DISK = #39;D:\Orders.bak#39;RESTORE DATABASE Orders_ReportingFROM DISK = #39;D:\Orders.bak

  2. BACKUP DATABASE Orders TO DISK = #39;D:\Orders.bak#39;CREATE DATABASE Orders_ReportingFROM DISK = #39;D:\Orders.bak

  3. CREATE DATABASE Orders_Reporting AS COPY OF Orders

  4. BACKUP DATABASE Orders TO DISK = #39;D:\Orders.bak#39;MIRROR TO DISK = #39;Orders_Reporting

Correct Answer: C


BACKUP DATABASE …AS COPY OF [source_server_name.]source_database_name Is used for copying a database to the same or a different SQL Database server.


https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-azure- sql-database

Question No.38

You administer two Microsoft SQL Server 2014 servers. Each server resides in a different, untrusted domain.

You plan to configure database mirroring.

You need to be able to create database mirroring endpoints on both servers. What should you do?

  1. Configure the SQL Server service account to use Network Service.

  2. Use a server certificate.

  3. Use a database certificate.

  4. Configure the SQL Server service account to use Local System.

Correct Answer: B


To enable certificate authentication for database mirroring on a given server instance, the system administrator must configure each server instance to use certificates on both outbound and inbound connections.


https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/use-certificates-for-a- database-mirroring-endpoint-transact-sql

Question No.39

You administer a Microsoft SQL Server 2014 instance. After a routine shutdown, the drive that contains tempdb fails.

You need to be able to start the SQL Server. What should you do?

  1. Modify tempdb location in startup parameters.

  2. Start SQL Server in minimal configuration mode.

  3. Start SQL Server in single-user mode.

  4. Configure SQL Server to bypass Windows application logging.

Correct Answer: B


If you have configuration problems that prevent the server from starting, you can start an instance of Microsoft SQL Server by using the minimal configuration startup option.

When you start an instance of SQL Server in minimal configuration mode, note the following: Only a single user can connect, and the CHECKPOINT process is not executed.

Remote access and read-ahead are disabled. Startup stored procedures do not run.

tempdb is configured at the smallest possible size.


https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/start-sql-server-with- minimal-configuration

Question No.40

You administer a Microsoft SQL Server 2014 instance that has several SQL Server Agent jobs configured.

When SQL Server Agent jobs fail, the error messages returned by the job steps do not provide the required detail.

The following error message is an example error message:

quot;The job failed. The Job was invoked by User CONTOSO\ServiceAccount. The last step to run was step 1 (Subplan_1).quot;

You need to ensure that all available details of the job step failures for SQL Server Agent jobs are retained.

What should you do?

  1. Configure output files.

  2. Expand agent logging to include information from all events.

  3. Disable the Limit size of job history log feature.

  4. Configure event forwarding.

Correct Answer: B



Get Full Version of the Exam
70-765 Dumps
70-765 VCE and PDF