November 23, 2007 at 12:33 am
Comments posted to this topic are about the item Disk space Notification by Email
November 23, 2007 at 1:11 am
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:
November 23, 2007 at 2:13 am
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
November 23, 2007 at 6:30 am
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.
November 23, 2007 at 6:43 am
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
November 23, 2007 at 6:57 am
Thanks Ruhland, thanks very much.
Regards,
Jai Kumar K.
November 23, 2007 at 1:30 pm
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply