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.
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.