Microsoft® SQL Server® 2008 Administration with Windows PowerShell™

Chapter 18. Monitoring SQL Server

Proactive monitoring is essential to ensure the stability of your SQL Server environment. Proactive monitoring means finding problems and potential service outages before they occur. As a DBA, you need to ensure that every SQL server instance in your environment is running and healthy, and that no users experience connection problems. You also need to keep track of the reboot schedule of each SQL Server host because regular reboots help improve server performance, and capture any OS or SQL Server errors before they evolve into critical issues.

Oftentimes, an improperly designed application causes blockings and deadlocks. When processes are blocked, users think something is wrong with the database server (and that you are not doing your job). To save everyone a lot of grief, you should capture the blockings or deadlocks as they occur, and notify users that their processes need to be examined before they call you on your hotline.

In this chapter and Chapter 19, we will implement these critical monitoring tasks. This chapter covers the following topics.

• Pinging SQL Server hosts

• Checking SQL Server–related services on SQL Server hosts

• Checking uptime of SQL Server hosts

• Monitoring Windows event logs

• Monitoring SQL Server error log

• Monitoring blockings

• Monitoring deadlocks

Pinging SQL Server Hosts

Even before monitoring the various components of SQL Server, the most important thing to monitor is whether the host machine is reachable. You can use the Ping-Host