Monitoring Disk Space and Sending Alerts with TSQL

CREATE PROCEDURE usp_DiskFreeSpaceAlert
 @DriveCBenchmark int = 1024,
 @OtherDataDriveBenchmark int = 2048
AS
--By: Haidong "Alex" Ji  This procedure sends out an alert message when hard disk space is below a predefined value. This procedure can be scheduled to run daily so that DBA can act quickly to address this issue.
IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = object_id(N'[tempdb]..[#disk_free_space]'))
DROP TABLE #disk_free_space
CREATE TABLE #disk_free_space (
 DriveLetter CHAR(1) NOT NULL,
 FreeMB INTEGER NOT NULL)

DECLARE @DiskFreeSpace INT
DECLARE @DriveLetter CHAR(1)
DECLARE @AlertMessage VARCHAR(500)
DECLARE @MailSubject VARCHAR(100)

/* Populate #disk_free_space with data */
INSERT INTO #disk_free_space
 EXEC master..xp_fixeddrives

SELECT @DiskFreeSpace = FreeMB FROM #disk_free_space where DriveLetter = 'C'

IF @DiskFreeSpace < @DriveCBenchmark
Begin
SET @MailSubject = 'Drive C free space is low on ' + @@SERVERNAME
SET @AlertMessage = 'Drive C on ' + @@SERVERNAME + ' has only ' +  CAST(@DiskFreeSpace AS VARCHAR) + ' MB left. Please free up space on this drive. C drive usually has OS installed on it. Lower space on C could slow down performance of the server'
-- Send out email
EXEC master..xp_sendmail @recipients = 'MyEmail@MyCompany.com',
@subject = @MailSubject,
@message = @AlertMessage
End

DECLARE DriveSpace CURSOR FAST_FORWARD FOR
select DriveLetter, FreeMB from #disk_free_space where DriveLetter not in ('C')

open DriveSpace
fetch next from DriveSpace into @DriveLetter, @DiskFreeSpace

WHILE (@@FETCH_STATUS = 0)
Begin
if @DiskFreeSpace < @OtherDataDriveBenchmark
Begin
set @MailSubject = 'Drive ' + @DriveLetter + ' free space is low on ' + @@SERVERNAME
set @AlertMessage = @DriveLetter + ' has only ' + cast(@DiskFreeSpace as varchar) + ' MB left. Please increase free space for this drive immediately to avoid production issues'

-- Send out email
EXEC master..xp_sendmail @recipients = 'MyEmail@MyCompany.com',
@subject = @MailSubject,
@message = @AlertMessage
End
fetch next from DriveSpace into @DriveLetter, @DiskFreeSpace
End
close DriveSpace

deallocate DriveSpace

DROP TABLE #disk_free_space

GO

This demonstrates 2005's ability to bring back filegroups one at a time,

/* SQL Server 2005 Testing */

/* Create our test database */


CREATE DATABASE [MultipleFilesDB] ON  PRIMARY
( NAME = N'MultipleFilesDB_Primary', FILENAME = N'e:\mssql.1\MSSQL\data\MultipleFilesDB_Primary.mdf' , SIZE = 4096KB , FILEGROWTH = 10%),
FILEGROUP [SECONDARY]
( NAME = N'MultipleFilesDB_Secondary', FILENAME = N'e:\mssql.1\MSSQL\data\MultipleFilesDB_Secondary.ndf' , SIZE = 4096KB , FILEGROWTH = 10%)
LOG ON
( NAME = N'MultipleFilesDB_log', FILENAME = N'e:\mssql.1\MSSQL\data\MultipleFilesDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'MultipleFilesDB', @new_cmptlevel=90
GO

ALTER DATABASE [MultipleFilesDB] SET RECOVERY FULL
GO



USE [MultipleFilesDB]
GO

IF NOT EXISTS (SELECT groupname FROM dbo.sysfilegroups WHERE (status & 0x10) != 0 AND groupname = N'PRIMARY') ALTER DATABASE [MultipleFilesDB] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

/* Create a test table on each filegroup */

USE [MultipleFilesDB]
GO

/****** Object:  Table [dbo].[Orders]    Script Date: 01/12/2009 19:47:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[OrdersPrimary](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[OrderDate] [datetime] NULL,
CONSTRAINT [PK_OrdersPrimary] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO



CREATE TABLE [dbo].[OrdersSecondary](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[OrderDate] [datetime] NULL,
CONSTRAINT [PK_OrdersSecondary] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
) ON [SECONDARY]
) ON [SECONDARY]
 GO



/* Populate the first order in each table */

INSERT INTO dbo.OrdersPrimary (OrderDate) VALUES (GETDATE())
INSERT INTO dbo.OrdersSecondary (OrderDate) VALUES (GETDATE())

/* Do a full backup */

BACKUP DATABASE [MultipleFilesDB] TO  DISK = N'e:\mssql.1\MSSQL\BACKUP\MultipleFilesDB.bak' WITH NOFORMAT, INIT,
NAME = N'MultipleFilesDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

/* Add another set of rows so we can tell if the t-logs have been applied */

INSERT INTO dbo.OrdersPrimary (OrderDate) VALUES (GETDATE())
INSERT INTO dbo.OrdersSecondary (OrderDate) VALUES (GETDATE())



/* Do a transaction log backup */

BACKUP LOG [MultipleFilesDB] TO  DISK = N'e:\mssql.1\MSSQL\BACKUP\MultipleFilesDB.trn' WITH NOFORMAT, INIT,
NAME = N'MultipleFilesDB-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO


/* Restore just the primary filegroup */

USE master
GO

RESTORE DATABASE [MultipleFilesDB]
FILE = N'MultipleFilesDB_Primary'
FROM  DISK = N'E:\MSSQL.1\MSSQL\Backup\MultipleFilesDB.bak'
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

/* You'll get an error saying more data is required to come completely online. That's OK.  Now restore the second filegroup: */

RESTORE DATABASE [MultipleFilesDB]
FILE = N'MultipleFilesDB_Secondary'
FROM  DISK = N'E:\MSSQL.1\MSSQL\Backup\MultipleFilesDB.bak'
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

/* Refresh your list of databases in SSMS, and presto, the database is online.  This is different than 2000 handled it. */

/* Now let's try it again, but we'll add the new "PARTIAL" parameter at the end telling SQL that we're doing a piecemeal restore: */

RESTORE DATABASE [MultipleFilesDB]
FILE = N'MultipleFilesDB_Primary'
FROM  DISK = N'E:\MSSQL.1\MSSQL\Backup\MultipleFilesDB.bak'
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10, PARTIAL
GO


/* Refresh your list of databases in SSMS, and the database is already online! Let's do a select; */

SELECT * FROM MultipleFilesDB.dbo.OrdersPrimary



/* It shows one row returned because we didn't get our transaction logs.  Now select from the secondary filegroup: */

SELECT * FROM MultipleFilesDB.dbo.OrdersSecondary



/* You'll get an error that the table is in a filegroup that isn't online yet. So let's bring it online. */

RESTORE DATABASE [MultipleFilesDB]
FILE = N'MultipleFilesDB_Secondary'
FROM  DISK = N'E:\MSSQL.1\MSSQL\Backup\MultipleFilesDB.bak'
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO


/* And do another select */
SELECT * FROM MultipleFilesDB.dbo.OrdersSecondary


/* Presto, we're back online. This demonstrates 2005's ability to bring back filegroups one at a time,

but it only works if you restore the primary first.  Watch what happens if we try to restore the second one by itself: */

RESTORE DATABASE [MultipleFilesDB]
FILE = N'MultipleFilesDB_Secondary'
FROM  DISK = N'E:\MSSQL.1\MSSQL\Backup\MultipleFilesDB.bak'
WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

/* Uh oh - it warns us that additional roll forwards are required, and sure enough, if we try a select - */

SELECT * FROM MultipleFilesDB.dbo.OrdersSecondary


/* It fails. The filegroup is offline until we bring it up to speed with matching transaction logs - even though we didn't change it!

So we can't just go restoring filegroups at random without doing some t-log syncing.  The database will be online, but the

restored filegroup may not be online yet. */