Monitoring and Troubleshooting SQL Server

Question 1

Humongous Insurance has hired you to evaluate its SQL Server infrastructure, make recommendations, and manage projects to improve the production environment. During your evaluation, you have learned that database files are stored across dozens of different drives and mount points. Data and log files are mixed with backup files. System databases exist on the same disk drives as user databases. Your first project is to move all the system databases to separate drives from the user databases. You will also be moving the tempdb database to dedicated storage on each instance because many very poorly written queries move massive quantities of data through tempdb. The on-call DBA is performing the maintenance and has tested the procedures several times in a lab environment. You receive a call that the first instance, following the database moves, does not start. You have verified service account permissions for the folder containing the master database files, that the master database files are in the correct location, and the startup parameters are correct. What is the most likely cause of the problem?

• The service account does not have permissions to the folder containing the tempdb database files.
• The mssqlsystem resource database is corrupted.
• The master database is corrupted.
• You have a bad memory module in the server.

Answer 1

• The service account does not have permissions to the folder containing the tempdb database files.

Question 2

You are the DBA at Blue Yonder Airlines and the phone rings. The main ticket booking application has just gone offline and cannot be reconnected to the database. You attempt to connect to the SQL Server and find that it is unreachable. You find that the service has stopped, and upon inspecting the error logs, you find a large number of device activation errors. What is the most likely cause of the problem?

• The disk storage system underneath the ticket booking database went offline.
• The disk storage system underneath the master or tempdb databases went offline.
• The SQL Server service account was locked out.
• Someone deleted the ticketing database files

Answer 2

• The disk storage system underneath the master or tempdb databases went offline.

Question 3

Which of the following are used to locate blocked processes? (Choose all that apply.)

1. sp_who2 system stored procedure
2. sys.dm_os_waiting_tasks view
3. sys.dm_exec_requests view
4. sys.dm_exec_sessions view

Answer 3

3. sys.dm_exec_requests view
4. sp_who2 system stored procedure

Question 4

Which types of SQL Server events are logged to the Windows Application Event log? (Choose all that apply.)

1. Stack dumps
2. Startup configuration messages
3. Job failures
4. Killed processes

Answer 4

1. Stack dumps
2. Killed processes