Keep watch on your database file size in SQL Server 2008/2005

Well, in few of my past articles, I have saw the method of how to keep watch on your disk drive, how to get information about your data and log file size as those are the very important task of DBA. Today, I am going to show one more important job which DBA simply can’t ignore. DBA needs to keep watch on every data and log file of databases server wide.  If it reaches at certain limits, DBA should be notified by email immediately so that s/he can take any action regarding this. Let me show you one very simple script which can make this task very easy for DBA.

DECLARE @DiskSpace bigint

DECLARE @DataBaseName VARCHAR(50)

DECLARE @Name VARCHAR(50)

DECLARE @DriveLetter CHAR(1)

DECLARE @DiskSize bigint

Declare @fileName varchar(max)

SET   @DiskSpace = 1

DECLARE DriveSpace CURSOR FAST_FORWARD FOR

      select

      databasename=DB_NAME(dbid),

      name, 

      filename,   

      drive=LEFT(filename,1),

      [size]=convert(bigint,[size])*8/1024

      from  sysaltfiles

      order by dbid

open

DriveSpace

fetch

next from DriveSpace into @DataBaseName, @Name,@fileName, @DriveLetter, @DiskSize

WHILE

(@@FETCH_STATUS = 0)

Begin

if

@DiskSize > @DiskSpace

Begin

     

     

      declare @mes varchar(max)

    set @mes='Hi Ritesh, ' + @Name + ' file of ' + @DataBaseName + ' database exceeds 1000 MB limit, current size is ' + cast(@DiskSize as varchar(10)) + ' and file name is ' + @fileName

    --Print @mes

    EXEC

          

          msdb..sp_send_dbmail  @recipients = 'Rits4Friends@gmail.com',

          @subject= 'Drive is about to full',

          @body= @mes

     

End

fetch

next from DriveSpace into @DataBaseName, @Name,@fileName, @DriveLetter, @DiskSize

End

close

DriveSpace

deallocate

DriveSpace

GO

All you need to do is, keep this script in JOBS and run that job once or twice everyday. If you don’t have database email configured than you could check this by remove comment before “Print” statement in script, don’t forget to comment email sending code, if you don’t have email configured in database.

 

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