You administer a Microsoft SQL Server 2016 instance t

hat has multiple databases.

You have a two-node SQL Server failover cluster.

The cluster uses a storage area network (SAN). You discover I/O issues. The SAN is at capacity and additional disks cannot be added.

You need to reduce the I/O workload on the

SAN at a minimal cost.

What should you do?

A. Move user databases to a local disk.

B. Expand the tempdb data and log files.

C. Modify application code to use table variables.

D. Move the tempdb files to a local disk.

You can configure TempDB

on a local disk when you, for example, installing your SQL Server cluster.

References: https://www.mssqltips.com/sqlservertip/2817/sql-server-2012-cluster-with-tempdb-on-local-disk/

You administer a Microsoft SQL Server 2016 instance.

You discover that the SQL Agent Error Log file is rapidly growing in size.

You need to ensure that the SQL Agent Error Log file does not grow rapidly

when SQL Server agent jobs execute.

What should you do?

A. Execute the sp_cycle_agent_errorlog stored procedure.

B. Configure event forwarding.

C. Enable the Auto Shrink option on the master database.

D. Enable the Auto Shrink option on the msdb database

.

E. Disable the Include execution trace messages feature.

Because the Include execution trace messages option can cause the error log to become large, only include execution trace messages in SQL Server Agent error logs when investigating a spe

cific SQL Server Agent problem.

References: https://docs.microsoft.com/en-us/sql/ssms/agent/write-execution-trace-messages-to-sql-server-agent-log-ssms

You administer a Microsoft SQL Server 2016 instance.

You need to stop a blocking process that has an SPID of 64 without stopping other processes.

What should you do?

A. Execute the following

Transact-SQL statement: EXECUTE sp_KillSPID 64

B. Restart the SQL Server service.

C. Execute the following Transact-SQL statement: KILL 64

D. Execute the following Transact-SQL statement: ALTER SESSION KILL 64

KILL can be used to terminate a

normal connection, which internally terminates the transactions that are associated with the specified session ID.

References: http://msdn.microsoft.com/en-us/library/ms173730.aspx

You administer a Microsoft SQL Server 2016 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?

A. Modify tempdb location in startup

parameters.

B. Start SQL Server in minimal configuration mode.

C. Start SQL Server in single-user mode.

D. Configure SQL Server to bypass Windows application logging.