Professional Microsoft SQL Server 2014 Administration

Chapter 12Monitoring Your SQL Server

WHAT'S IN THIS CHAPTER?

• Monitoring SQL Server behavior with dynamic management objects
• Monitoring SQL Server Error Log and the Windows Event logs
• Monitoring SQL Server with the System Center Advisor

WROX.COM CODE DOWNLOADS FOR THIS CHAPTER

The wrox.com code downloads for this chapter are found at www.wrox.com/go/prosql2014admin on the Download Code tab. The code is in the Chapter 12 download and individually named according to the names throughout the chapter.

Implementing good monitoring enables you to move from reactively dealing with events to proactively diagnosing problems and fixing them before your users are even aware there is a problem. This chapter teaches you how to proactively monitor your SQL Server system so that you can prevent or react to events before the server gets to the point where users begin calling.

Here's a quick example. Say you recently took over an existing system after the DBA moved to a different team. This system's applications ran well, but there was something that needed fixing every other day—transaction logs filling up, tempdb out of space, too many locks, and filegroups filling up—nothing major, just a slow, steady trickle of problems that needed fixing. This is the DBA's death of 1,000 cuts. Your time is sucked away doing these essential maintenance tasks until you get to the point where you don't have time to do anything else.

After a few weeks of this, you manage to put some new monitoring in place ...