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
Advertisements