Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Disk space Notification by Email


Disk space Notification by Email

Author
Message
addankireddyprasad
addankireddyprasad
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 95
Comments posted to this topic are about the item Disk space Notification by Email
henrik staun poulsen
henrik staun poulsen
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1880 Visits: 1212
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
Jochen Ruhland
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
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
Jai Kumar
Jai Kumar
Right there with Babe
Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)

Group: General Forum Members
Points: 788 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.
Jochen Ruhland
Jochen Ruhland
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
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
Jai Kumar
Jai Kumar
Right there with Babe
Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)Right there with Babe (788 reputation)

Group: General Forum Members
Points: 788 Visits: 41
Thanks Ruhland, thanks very much.

Regards,
Jai Kumar K.
Joe Bigler
Joe Bigler
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search