It's 13:30 AM and you get a call from the Applications On-Call person. Application XYZ is down, looks like an SQL Server problem. You find that a drive or database is out of space, causing the SQL Server to crash. Now the difficult part, find some free space and hope the SQL Server will successfully recover!! We will not even consider the Application downtime, corrupt databases, etc…

We began addressing this issue several years ago, as the number of SQL Servers at our institution increased (currently 60+). We have since developed a system of stored procedures/SQL jobs that monitor drive/database space, send email/page notifications as necessary, produce weekly reports and record the information for historical review. We feel they are worth sharing, possibly you can use all or part of this system.

For this system to properly function, all SQL Servers must have the ability to send email, we use ‘xp_sendmail’. We decided several years ago that all our SQL Servers would use email to notify us of problems. We use 2 types of email accounts. The first is for events that can wait for the next workday, e.g. problems on development SQL Servers, where an email will suffice. The second is for events that require immediate attention, e.g. a failed DBCC check on a production SQL Server, where an email-generate page is used. In the following examples, we will be using the first email type.

Drive Space Monitoring.

Monitoring of space begins at the Disk Drive level. We run the ‘Disk Drive Space Info’ job hourly, using SQL Server Agent. The basic job steps are:

• Step 1 – Create DrvSpace Table.
• Step 2 – Get Drive Space Data.
• Step 3 – Alter Table DrvSpace
• Step 4 – Enter Drive Total Space.
• Step 5 – MonitorEmailLowDriveFreeSpace

Here is a detailed look at these steps:

Step 1 – Create DrvSpace Table. This step simply creates an empty table ‘DrvSpace’ in tempdb for each run.

use tempdb
go
If exists (select name from tempdb..sysobjects where name = 'DrvSpace' and type = 'U')
 begin
   drop    table DrvSpace
 end
Create table DrvSpace (
    DriveLetter   char(02)  null,
    MB_Free       float     null)

Step 2 – Get Drive Space Data. This step executes xp_fixeddrives, and inserts the output into table DrvSpace.

use tempdb
go
INSERT DrvSpace EXECUTE master..xp_fixeddrives

Step 3 – Alter Table DrvSpace. This step alters table DrvSpace by adding a column for Total Drive Space.

use tempdb
go
Alter Table DrvSpace ADD MB_Total float NULL

Step 4 – Enter Drive Total Space. This step requires editing. Here you enter the Total Drive Space in GB for a given drive. In this example the ‘C’ drive has 3.99 GB of Total Drive Space. This portion of code needs to be repeated for each drive on the server that will be monitored.

use tempdb
go
update DrvSpace
 set MB_Total = (3.99 * 1024) 
 where DriveLetter = 'C'

Step 5 – MonitorEmailLowDriveFreeSpace. This step executes the Stored Procedure sp_Monitor_Email_Low_Drive_Free_Space’. As executed here, it will look for local drives with under 10% (0.1) free space. Email notifications are sent, identifying these drives. This procedure is actually more flexible than displayed here, the C: Drive is handled separately with a 15% free space limit and any other Drive can be specified as an ‘exception’, with it’s own specific free space limit. Documentation embedded in this Stored Procedure details its operation.

EXEC msdb.dbo.sp_Monitor_Email_Low_Drive_FreeSpace 0.1

Code:sp_Monitor_Email_Low_Drive_FreeSpace.sql

Database Space Monitoring

Now that we have Drive Space Information, we gather Database Space Information and current SQL Server Disaster Recovery Information. We run the ‘Database Info’ job daily, using SQL Server Agent. The basic job steps are:

• Step 1 – Initialize Tables in msdb.
• Step 2 – Capture Space Info on all DBs.
• Step 3 – MonitorEmailLowDBFreeSpace
• Step 4 – Current SQL Server Disaster Recovery Info

Here is an detailed look at these steps:

Step 1 - Initialize Tables in msdb. This step simply creates a table ‘DASD’ in msdb. This table name is a throw back to earlier days, when Disk Drives were referred to as Direct Access Storage Devices. We elected to use the msdb database, rather than create a new database for just one table. The size of DASD is managed in Step 3.

use msdb
go
if not exists (select name from msdb..sysobjects
                where name = 'DASD'
                and type   = 'U')
 begin
   create table msdb..DASD
   (
      createDTM  varchar(20),
      SQL_Server  varchar(30),
      db_name  varchar(30),
      group_name varchar(30),
      group_alias varchar(30),
      total_DB_space  varchar(10),
      group_type  varchar(20),
      free_DB_space  varchar(10),
      total_drive_space  varchar(10),
      free_drive_space  varchar(10),
      drvLetter  varchar(5),
      db_maxsize  int,
      db_growth  int
  )
 end

Step 2 - Capture Space Info on all DBs. This step execute Stored Procedure ‘sp_Monitor_Capture_DB_Space_Info’, which captures space information on all databases. It also combines relevant Disk Drive information (drives containing that database’s files), and writes this information to table DASD. An issue with this Stored Procedure is that it queries system tables to obtain its information. This may become a problem with MS SQL 2005, and will have to be addressed as more information on MS SQL 2005 system tables becomes available. Documentation embedded in this Stored Procedure details its operation.

EXEC msdb..sp_Monitor_Capture_DB_Space_Info

Code:sp_Monitor_Capture_DB_Space_Info.sql

Step 3 - MonitorEmailLowDBFreeSpace. This step executes Stored Procedure ‘sp_Monitor_Email_Low_DB_FreeSpace’. As with Disk Drives, an overall Free Space % can be set for all databases, with 2 exception databases/sizes specified. Free Space on databases with no growth is computed from un-allocated database space, while Free Space on databases with max size limits included potential yet unused database size in its computations. Databases with no growth limits are not included in Free Space computations or Emails. Emails from the Disk Drive Free Space portion of the system are considered sufficient. This Stored Procedure also purges all rows in DASD > 1 year old, except for those generated on Fridays, thus leaving 1 space recording/week/database. Finally, this Stored Procedure sends Emails notifications on any database , other then ‘tempdb’, that was created 1 or 4 days ago, keeping track of over ambitious Application Staff. Documentation embedded in this Stored Procedure details its operation.

EXEC msdb.dbo.sp_Monitor_Email_Low_DB_FreeSpace 0.1

Code:sp_Monitor_Email_Low_DB_FreeSpace.sql

Step 4 – Current SQL Server Disaster Recovery Info. Finally, this step executes Stored Procedure ‘sp_MSSQL_Recovery_Info’, which captures disaster recovery information on all databases. It has nothing to do with Space Usage, it was just convenient to run it here. The output of this Stored Procedure is saved to a local Drive. This information is finally copied to a central information server using ftp, along with similar information from all MS SQL Servers. Documentation embedded in this Stored Procedure details its operation.

EXEC msdb..sp_MSSQL_Recovery_Info

Code:sp_MSSQL_Recovery_Info.sql

Weekly Database Space Reports

We generate weekly Database Space Reports, from a Support Server, using osql commands in bat files. These jobs are scheduled using the standard W2K Scheduler. The prior weeks reports are overlayed with the current weeks reports. These reports are viewed as needed and are a wonderful source of information for Disk Drive Space Predictions. The Stored Procedure sp_DASD_Report is run locally on each SQL Server.

An example of this weekly report follows.

Date     SQL SERVER    DB Name               Total DB MB  Free DB MB  Autogrow  Max DB Size  Free Disk MB
-------- ------------- --------------------- ------------ ----------- --------- ------------ -------------
20040326 THESQLSERVER  AdminReports          46.69        18.93       YES       500          15065 
20040402 THESQLSERVER  AdminReports          46.69        18.70       YES       500          14331 
20040409 THESQLSERVER  AdminReports          46.69        15.70       YES       500          13927 
20040416 THESQLSERVER  AdminReports          51.38        20.86       YES       500          12490 
20040423 THESQLSERVER  AdminReports          51.38        20.02       YES       500          11652 
20040430 THESQLSERVER  AdminReports          51.38        18.99       YES       500          10920
20040507 THESQLSERVER  AdminReports          51.38        17.48       YES       500          9861         
20201225               AdminReports
20040326 THESQLSERVER  Management            3243.88      1423.38     YES       Unrestricted 15065
20040402 THESQLSERVER  Management            3243.88      1423.38     YES       Unrestricted 14331
20040409 THESQLSERVER  Management            3243.88      1345.60     YES       Unrestricted 13927
20040416 THESQLSERVER  Management            3243.88      1345.59     YES       Unrestricted 12490
20040423 THESQLSERVER  Management            3243.88      1345.59     YES       Unrestricted 11652
20040430 THESQLSERVER  Management            3243.88      1345.59     YES       Unrestricted 10920
20040507 THESQLSERVER  Management            3243.88      1345.59     YES       Unrestricted 9861
20201225               Management
20040326 THESQLSERVER  Employee              131072.00    10749.23    NO        131072.00    53093
20040402 THESQLSERVER  Employee              131072.00    9828.36     NO        131072.00    53093
20040409 THESQLSERVER  Employee              131072.00    9363.62     NO        131072.00    53093
20040416 THESQLSERVER  Employee              131072.00    8423.04     NO        131072.00    53093
20040423 THESQLSERVER  Employee              131072.00    7513.55     NO        131072.00    53093  
20040507 THESQLSERVER  Employee              131072.00    6848.62     NO        131072.00    53093
20201225               Employee

Code:sp_DASD_Report.sql

Conclusion

We have far too many SQL Servers to visit each day and review Database/Drive Free Space. This system allows us to focus on other aspects of Database Administration, knowing we will be informed if Free Space is becoming an issue on any of our SQL Servers.

Code: sp_Monitor_Email_Low_Drive_FreeSpace.sql
sp_Monitor_Capture_DB_Space_Info.sql
sp_MSSQL_Recovery_Info.sql
sp_Monitor_Email_Low_DB_FreeSpace.sql
sp_DASD_Report.sql