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

Well, one of the important tasks 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:

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]

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 our server reached <20000>

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://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com