Microsoft® SQL Server® 2008 Administration with Windows PowerShell™

Chapter 19. Monitoring Disk Space Usage, Database Files, and Backups

In an enterprise environment, an unavoidable task is monitoring the free disk space on SQL server hosts to ensure that no system or database drives are running low on space. In addition, the growth of the database files needs to be monitored to ensure there is sufficient space on disks to accommodate future storage needs. It is better to plan ahead, rather than panic when you run out of disk space. In this chapter, we will create extra tables in the inventory databases to hold space information. This chapter also presents sample Windows PowerShell scripts that can be executed against the list of servers stored in the inventory database to collect the space information.

Backup and recovery is one of the most important aspects of database administration. Important information saved in a database needs to be protected against hardware, software, or human errors. Therefore, you need to develop a backup and recovery plan, and monitor the database backups to ensure conformance to the plan.

This chapter covers the following topics:

• Monitoring disk space usage

• Monitoring database file growth

• Monitoring backups

Monitoring Disk Space Usage

In Chapter 15, an inventory database named SQL_Inventory was set up to hold information about hosts, servers, and databases. We can add a DiskSpace table to the SQL_Inventory database to hold the information regarding disk space usage. SQL Server database files can reside on the local drive only. ...