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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: