Keep watch on your disk space in SQL Server 2008/2005

Keep watch on your disk space in SQL Server 2008/2005
Disk always plays an important role in life of DBA. We have o keep watch on disk size, free space, read & write capacity and much more. One of the important task for DBA is to keep watch on the free space of hard drive so that no databases face any space related issue. There are so many ways to check it, you can either manually check free space from your explorer, you can use sp_fixedDrives extended stored procedure in SQL Server. What I am going to show you is how you can atomize this process, which keeps an eye on the disk space and can send an email to administrator when it reaches below our warning level.
Let us see one simple stored procedure for this task:
[sourcecode language=”sql”]USE
[master]
GO
CREATE PROCEDURE [dbo].[SpDiskSpaceAlert]
AS
IF OBJECT_ID(N'[tempdb]..[#Temptbl]’) is not null
DROP TABLE #Temptbl
CREATE TABLE #Temptbl
(
Drive CHAR(1) NOT NULL,
MB INTEGER NOT NULL
)

DECLARE @dsc INT
DECLARE @FreeSpace INT
DECLARE @Drive CHAR(1)
SET @dsc = 20000

INSERT
INTO #Temptbl
EXEC master..xp_fixeddrives
DECLARE DriveSpace CURSOR FAST_FORWARD FOR select Drive, MB from #Temptbl

OPEN DriveSpace
FETCH
NEXT FROM DriveSpace INTO @Drive, @FreeSpace
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF
@FreeSpace < @dsc
BEGIN
DECLARE @mes VARCHAR(MAX)
SET @mes=’Hi Ritesh’
+ @Drive + ‘: drive reached warning limit, current status is ‘ + cast(@FreeSpace as varchar) + ‘ MB only’
EXEC
msdb..xp_send_dbmail @recipients = ‘Rits4Friends@gmail.com’,
@subject= ‘Drive is about to full’,
@body= @mes
End

FETCH NEXT FROM DriveSpace INTO @Drive, @FreeSpace
End
CLOSE DriveSpace
DEALLOCATE DriveSpace
DROP TABLE #Temptbl
GO
–check whether this works or not
EXEC [Master].[dbo].[SpDiskSpaceAlert][/sourcecode]



After creating this stored procedure, you can call it in SQL Server jobs, one or may be twice a day, which sends you an email, if warning level reaches.  Right now I have set it to 20000 MB so if any of the drive in server reached to this capacity, it will shoot you an email. This is just a basic script, need modification as per the need of individual.
Note: You should have Database mail enable. If you don’t want to send an email, you can either create log or store information in any of the table.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles .

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.