Monitoring Databases  «Prev  Next»

Lesson 1

Monitoring and Maintaining SQL-Server Databases

Monitoring SQL-Server Databases

As a SQL Server system administrator you need to monitor your databases. You will need to do this to either
  1. check the database’s size,
  2. the database’s performance, and
  3. to make sure that the database is healthy and does not become corrupt.

SQL Server database systems are more than ever before being chosen as the preferred backend database solution for large business's critical systems and SQL Server richly deserves this status. As a result of this, more users than ever before are blocked in their daily activity when the database is not available, or in a bad shape.

It is your responsibility as a DBA to know the health of your SQL Server so you can take proactive action to minimize chances anything bad happens to your system. But do not panic. If you have the right tools in place, your system will tell you how it feels and will warn you well before it reaches the alarm phase. Through its performance behavior, you can spot potential problems not yet visible to the end-user. If, for example, for no reason response times of a certain query slow down from 100 to 500 ms, the end-user will not be alarmed, but you should.

Monitoring Database Maintenance

It is not enough to simply schedule tasks; they must be monitored as well. In larger installations with dozens of SQL Servers spread around the globe, monitoring the health of SQL Server and the databases is itself a full-time job. Table 4-1 provides a sample DBA daily checklist that can be used as a starting point for developing a database monitoring plan.

Sample DBA daily checklist
Table 4-1: Sample DBA daily checklist

Depending on the number of servers and the complexity, the DBA daily checklist can be maintained manually with an Excel spreadsheet or tracked in a SQL Server table.
Another task of a SQL Server administrator is to monitor and maintain databases. You monitor and maintain databases for three reasons:
  1. Keep track of a database’s size
  2. Make sure that the database is healthy
  3. Keep performance consistent

Learning Objectives

By the time you complete this module, you should be able to:
  1. Monitor the size of a database
  2. Monitor the size of a database with SQL-EM
  3. Use dbcc to monitor databases
  4. Monitor the consistency of individual tables
  5. Perform the miscellaneous database monitoring with dbcc
  6. Monitor the transaction log
  7. Maintain statistics
  8. View statistics information
  9. Run sqlmaint.exe
  10. Use the Database Maintenance plan Wizard
The next lesson will cover how databases free space.