Disk space Notification by Email

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by addankireddyprasad » Disk space Notification by Email

Disk space Notification by Email

Rate Topic

Display Mode

Topic Options

Author

Message

addankireddyprasad

Posted Friday, November 23, 2007 12:33 AM

Grasshopper

Group: General Forum Members
Last Login: Saturday, April 13, 2013 8:09 AM
Points: 11, Visits: 48

Comments posted to this topic are about the item Disk space Notification by Email

Post #425159

henrik staun poulsen

Posted Friday, November 23, 2007 1:11 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 5:20 AM
Points: 1,131, Visits: 854

Here is my suggestion.
Tested on SQL Server 2005, with sp_send_dbmail instead. Tests all drive letters.

SQL Code only:

--Author : Addanki Reddyprasad
--Usage : According to threshold value the respective person will get notified
-- ide fra http://www.sqlservercentral.com/scripts/Maintenance%2f+Administration/61277/
SET NOCOUNT ON
declare @MB_Free INT, @recipients VARCHAR(MAX), @lowlimit INT, @highlimit INT, @loopcounter INT, @driveletter CHAR(1), @subject NVARCHAR(max)
DECLARE @debug INT
SET @debug=1 -- 1=testing, else live
SET @recipients='hsp@stovi.com'
SET @lowlimit=5120 -- smallest limit (used on small disk drives)
SET @highlimit=51200 -- somewhat higher limit on the other drives

create table #FreeSpace(
Drive char(1),
MB_Free int)

insert into #FreeSpace exec xp_fixeddrives

SET @loopcounter=0
WHILE @loopcounter < 26 BEGIN
SET @loopcounter=@loopcounter+1
SET @driveletter=CHAR(66 + @loopcounter) -- 66 = letter C
SET @MB_Free = null
select @MB_Free = MB_Free from #FreeSpace where Drive = @driveletter
-- Free Space on x drive Less than Threshold
IF @MB_Free < @lowlimit BEGIN
SET @subject= 'SQL SERVER - Free Space Issue on the ' + @driveletter + ' Drive. Free space is ' + CONVERT(VARCHAR(30), @mb_free) + ' MB. Treashold is ' + CONVERT(VARCHAR(30), @lowlimit) + ' MB.'
IF @debug= 1 SELECT @driveletter, @MB_free
exec msdb.dbo.sp_send_dbmail @recipients =@recipients, @subject =@subject, @body = @subject
END
-- for anything else than c: we use the higher limit
SET @lowlimit = @highlimit
END
IF @debug= 1 SELECT * FROM #FreeSpace
drop table #FreeSpace

--*********************************************

and here is the job schedule as well:

USE [msdb]
GO
/****** Object: Job CheckDiskspace Script Date: 09/14/2007 22:02:54 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 01/15/2007 22:02:54 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
BEGIN TRY
EXEC msdb.dbo.sp_delete_job @job_name=N'CheckDiskspace'
END TRY
BEGIN CATCH
END CATCH
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'CheckDiskspace',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa',
@notify_email_operator_name=N'All Database operators', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [step 1] Script Date: 01/15/2007 22:02:54 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CheckDiskspace',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=4,
@on_success_step_id=2,
@on_fail_action=4,
@on_fail_step_id=3,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'

--Author : Addanki Reddyprasad
--Usage : According to threshold value the respective person will get notified
-- ide fra http://www.sqlservercentral.com/scripts/Maintenance%2f+Administration/61277/
SET NOCOUNT ON
declare @MB_Free INT, @recipients VARCHAR(MAX), @lowlimit INT, @highlimit INT, @loopcounter INT, @driveletter CHAR(1), @subject NVARCHAR(max)
DECLARE @debug INT
--SET @debug=1 -- 1=testing, else live
SET @recipients=''hsp@stovi.com''
SET @lowlimit=5120 -- smallest limit (used on small disk drives)
SET @highlimit=51200 -- somewhat higher limit on the other drives

create table #FreeSpace(
Drive char(1),
MB_Free int)

insert into #FreeSpace exec xp_fixeddrives

SET @loopcounter=0
WHILE @loopcounter < 26 BEGIN
SET @loopcounter=@loopcounter+1
SET @driveletter=CHAR(66 + @loopcounter) -- 66 = letter C
SET @MB_Free = null
select @MB_Free = MB_Free from #FreeSpace where Drive = @driveletter
-- Free Space on x drive Less than Threshold
IF @MB_Free < @lowlimit BEGIN
SET @subject= ''SQL SERVER - Free Space Issue on the '' + @driveletter + '' Drive. Free space is '' + CONVERT(VARCHAR(30), @mb_free) + '' MB. Treashold is '' + CONVERT(VARCHAR(30), @lowlimit) + '' MB.''
IF @debug= 1 SELECT @driveletter, @MB_free
exec msdb.dbo.sp_send_dbmail @recipients =@recipients, @subject =@subject, @body = @subject
END
-- for anything else than c: we use the higher limit
SET @lowlimit = @highlimit
END
IF @debug= 1 SELECT * FROM #FreeSpace
drop table #FreeSpace

',
@database_name=N'vdc',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [stop 2 (Step1 OK)] Script Date: 01/15/2007 22:02:55 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step 2 (Step1 OK)',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'select @@version',
@database_name=N'VDC',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Step 3 (Step 1 failed)] Script Date: 01/15/2007 22:02:55 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step 3 (Step 1 failed)',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=2,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'select @@version',
@database_name=N'vdc',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'checkdiskspace',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20060420,
@active_end_date=99991231,
@active_start_time=230000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Post #425165

Jochen Ruhland

Posted Friday, November 23, 2007 2:13 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, December 06, 2007 1:53 AM
Points: 24, Visits: 40

Hi,

just a small error ...

henrik staun poulsen (11/23/2007)

Here is my suggestion.
SET @loopcounter=0
WHILE @loopcounter < 26 BEGIN
SET @loopcounter=@loopcounter+1
SET @driveletter=CHAR(66 + @loopcounter) -- 66 = letter C

if you start with drive C: you do not have 26 driveletters left, so it should be

WHILE @loopcounter < 24 BEGIN

Post #425180

Jai Kumar

Posted Friday, November 23, 2007 6:30 AM

Right there with Babe

Group: General Forum Members
Last Login: Monday, May 30, 2011 5:15 AM
Points: 782, Visits: 41

Is the procedure "xp_fixeddrives" documented in the MSDN? I am unable to find the documentation for this extended stored procedure. I ams using the SQL 2000 and it's MSDN.

Please advise if I am missing anything.

Thanks and regards,
Jai Kumar K.

Post #425262

Jochen Ruhland

Posted Friday, November 23, 2007 6:43 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, December 06, 2007 1:53 AM
Points: 24, Visits: 40

Hi,

according to http://www.sqlservercentral.com/articles/Administering/someundocumentedstoredprocedures/400/ it is an undocumented SP.

xp_fixeddrives
This very useful extended stored procedure returns the list of all
fixed hard drives and the amount free space in Mb for each hard drive.

You can find it under Databases -> master -> Programability -> Extended Stored Procedures -> System Extended Stored Procedures

Post #425265

Jai Kumar

Posted Friday, November 23, 2007 6:57 AM

Right there with Babe

Group: General Forum Members
Last Login: Monday, May 30, 2011 5:15 AM
Points: 782, Visits: 41

Thanks Ruhland, thanks very much.

Regards,
Jai Kumar K.

Post #425272

Joe Bigler

Posted Friday, November 23, 2007 1:30 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, November 23, 2007 1:27 PM
Points: 4, Visits: 4

I use a script very similar to this on SQL Server 2000. The documentation on xp_fixeddrives and xp_smtp_sendmail can be found using a simple Google search. I run the script once a day and send myself an email telling me the amount of space free. I also run the script about every hour using a second job. This time it only sends an email if a drive is below a threshold and it sends a copy to the network admin as well.

Post #425353

« Prev Topic | Next Topic »

Permissions