Lesson 1

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.

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.

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

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.