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