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
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
0 comments:
Post a Comment