As a SQL Server system administrator you need to monitor your databases. You will need to do this to either
check the database’s size,
the database’s performance, and
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.
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:
Keep track of a database’s size
Make sure that the database is healthy
Keep performance consistent
Learning Objectives
By the time you complete this module, you should be able to:
Monitor the size of a database
Monitor the size of a database with SQL-EM
Use dbcc to monitor databases
Monitor the consistency of individual tables
Perform the miscellaneous database monitoring with dbcc
Monitor the transaction log
Maintain statistics
View statistics information
Run sqlmaint.exe
Use the Database Maintenance plan Wizard
The next lesson will cover how databases free space.