Checking SQL Server Drive Space

Eak… Drivespace was down to 1MB on a customer’s server today (this was due to them deciding that changing passwords and not informing use or changing services settings was a good thing). Well I dialed in and trimmed log files, cleaned up unused file from the OS and such. In the office I have checks daily to make sure my databases are in tiptop condition but we usually leave the maintenance of the customer’s databases to themselves (we have other 400 of them so expecting a daily check is a little time consuming). This can lead to the odd occasion where the drive fills up or a backup script stop running and I’ve decided to do something about it, I created a web service on the sites with checked the most recent backups (see my backup scripts below) and also checks the disk space, I have then made a consuming website which connects to all the servers (eventually) and relays the space and last backup time, I can then from there take action if needed. Simple but very effective, I’ve posted the code below for how I record the drive space for any of you who which to know.

--Create the Database
CREATE DATABASE DBADatabase
GO
-- Switch to the new database for the rest of our setup
USE DBADatabase
GO
-- Create the table we’ll be using to hold the details on the drive space
CREATE TABLE dbo.DriveSpace (
 [ID] uniqueidentifier NOT NULL ROWGUIDCOL,
 [ServerName] varchar(50) NOT NULL,
 [Date] datetime NOT NULL,
 [Drive] char(1) NOT NULL,
 [SpaceMB] int NOT NULL )  ON [PRIMARY]
GO
-- add some indexes and such
ALTER TABLE dbo.DriveSpace ADD CONSTRAINT DF_DriveSpace_ID DEFAULT (newid()) FOR [ID]
GO

ALTER TABLE dbo.DriveSpace ADD CONSTRAINT PK_DriveSpace PRIMARY KEY CLUSTERED ([ID]) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX IX_DriveSpace ON dbo.DriveSpace ([Date]) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

ALTER TABLE dbo.DriveSpace SET (LOCK_ESCALATION = TABLE)
GO

That’s all we need to store it.. bug how to fill it? Well there is a function that returns the information of the drives of which the server is located called xp_fixeddrives, try it for yourselves and see the two columns returned (Drive and MB free). Anywho we need to set up a stored procedure that will record the details so that you can later set up a job using the GUI or sp_add_job to execute the stored proc daily.

USE DBADatabase
GO
CREATE PROCEDURE dba_UpdateDriveInfo AS
BEGIN
 -- Temporary Table for our results
 DECLARE @DriveData AS TABLE ([Drive] CHAR(1), [MB Free] INT) 
 -- Get results
 INSERT INTO @DriveData EXEC xp_fixeddrives
 -- Dump them in our table
 INSERT INTO dbo.DriveSpace ([ServerName], [Date], [Drive], [SpaceMB])
  SELECT DB_NAME(), GETDATE(), [DD].[Drive], [DD].[MB Free] FROM @DriveData AS [DD]
END

Again all you need to do is set up a job to run this daily and if you wanted to maybe send a mail when you lower than a specific amount? Good Luck