Keep watch on your database file size in SQL Server 2008/2005
Well, in my previous article, I saw the method of how to keep watch on your disk drive (Click here to view), 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.
Here is the script with Cursor:
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 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
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles .