Disk space Notification by Email

  • addankireddyprasad

    SSC Rookie

    Points: 31

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

  • Henrik Staun Poulsen

    SSCertifiable

    Points: 6356

    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:

  • Jochen Ruhland

    SSC Enthusiast

    Points: 194

    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

  • Jai Kumar

    SSC Eights!

    Points: 872

    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.

  • Jochen Ruhland

    SSC Enthusiast

    Points: 194

    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

  • Jai Kumar

    SSC Eights!

    Points: 872

    Thanks Ruhland, thanks very much.

    Regards,

    Jai Kumar K.

  • Joe Bigler

    SSC Journeyman

    Points: 78

    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.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply