April 23, 2007 at 8:34 pm
Dear Friends,
I want to automatically alert my colleagues reg the disk space for every two hours.
Thanks & Regards
Chandra Mohan N
[font="Verdana"]Thanks
Chandra Mohan[/font]
April 23, 2007 at 9:58 pm
have a look at sp_spaceused. you could run it in each database to generate a report.
---------------------------------------
elsasoft.org
April 24, 2007 at 7:20 am
Or set a performance alert on this counter.
April 24, 2007 at 7:37 am
I did not ask for the space left in the database. I want the diskspace in the drive where the database resides to be sent to everyone of my colleauge through net send for every two hours.
[font="Verdana"]Thanks
Chandra Mohan[/font]
April 25, 2007 at 2:44 am
I don't know about net send but you can use the extended stored procedure xp_fixeddrives to tell you how much disk space you have left...
EXEC master..xp_fixeddrives
Mark
April 25, 2007 at 10:36 am
I second Steve Jones.
May 1, 2007 at 12:18 am
Write a VBS Script (WMI) to check up the space left and set up a scheduled task. In my opinion this is a OS task for the system administrator.
May 2, 2007 at 3:33 am
Hello
I use the following SP (I think taken from SQL server central and altered) and SQL job to monitor disk space and issue an e mail alert when any of the drives falls below 'X%' free space. I am sure that you could amend the part that sends the email to be specific to a drive and issue an net send instead. The SQL job can be scheduled to your own requirements.
--Create the procedure
/****** Object: Stored Procedure dbo.sp_server_disk_space Script Date: 10/11/2006 15:31:46 ******/
CREATE procedure sp_disk_growth_Monitor(@Limit int)
as
/**
Issues e mail when percentage of available disk space on any drive falls below 'X'
Uses SQL job scheduled to run every hour between 08:00 and 17:00
**/
set nocount on
declare
@SQL varchar(1000),
@subject varchar(100),
@message varchar(100)
SET @SUBJECT = @@servername + ' Low Disk Space'
set @message = 'Available disk space is reported as below ' + convert(varchar(2), @limit) + '%' + char(10)
create table Tbl_Temp_disk_space
(Extract_date datetime,
dletter varchar(2),
fspace DECIMAL(9,2),
tspace DECIMAL(9,2))
/*****************************************
* populate table
*****************************************/
INSERT INTO
Tbl_Temp_disk_space (dletter, fspace)
EXEC master.dbo.xp_fixeddrives
Update Tbl_Temp_disk_space
set extract_date = convert(varchar(11), getdate(), 113)
where extract_date is null
/*****************************************
* Update temp table info with total disk sizes
*****************************************/
-- Create cursor for disk space table
DECLARE c_disks CURSOR FOR
SELECT dletter, fspace, tspace FROM Tbl_Temp_disk_space
FOR UPDATE
DECLARE @dletter VARCHAR(2), @fspace INT, @tspace BIGINT
-- Create FileSystemObject
DECLARE @oFSO INT, @oDrive INT, @drsize VARCHAR(255), @ret INT
EXEC @ret = master.dbo.sp_OACreate 'scripting.FileSystemObject', @oFSO OUT
-- Open cursor and fetch first row
OPEN c_disks
FETCH NEXT FROM c_disks
INTO @dletter, @fspace, @tspace
-- Loop through all records in the cursor
WHILE @@fetch_status = 0
BEGIN
-- Get disk size
SET @dletter = @dletter + ':\'
EXEC @ret = master.dbo.sp_OAMethod @oFSO, 'GetDrive', @oDrive OUT, @dletter
EXEC @ret = master.dbo.sp_OAMethod @oDrive, 'TotalSize', @drsize OUT
-- Update table
UPDATE Tbl_Temp_disk_space
SET tspace = --CAST(@drsize AS BIGINT)
isnull(convert(varchar(10), round((CAST(CAST(@drsize AS BIGINT) AS FLOAT) / 1024) /1024,0)), 'Not recorded')
WHERE CURRENT OF c_disks
-- Destory oDrive
EXEC master.dbo.sp_OADestroy @oDrive
-- Fetch next row
FETCH NEXT FROM c_disks
INTO @dletter, @fspace, @tspace
END
-- Close cursor
CLOSE c_disks
DEALLOCATE c_disks
-- Destroy FSO
EXEC master.dbo.sp_OADestroy @oFSO
--end
if exists (select * from Tbl_Temp_disk_space where CAST((fspace/tspace)*100 as int) <=@limit )
begin
set @sql = 'select ' + char(10) +
'dletter,' + char(10) +
'fspace Current_Free_Space_mb,' + char(10) +
'tspace Current_Total,' + char(10) +
'CAST((fspace/tspace)*100 AS DECIMAL(9, 2)) Percentage_free,' + char(10) +
'case' + char(10) +
' when CAST((fspace/tspace)*100 AS int) <= ' + convert(varchar(3), @limit) + ' then ''Low Disk Space''' + char(10) +
' else ''''' + char(10) +
'end warning' + char(10) +
'from database_administrator_monitoring.dbo.Tbl_Temp_disk_space'
--exec(@sql)
EXEC master..xp_sendmail @recipients = '[Your recipients]',
@query = @sql,
@subject =@subject,
@message = @message
end
drop table Tbl_Temp_disk_space
go
--create the job
-- Script generated on 02/05/2007 10:19
-- By: sa
-- Server: [Your server name]
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'sp_disk_growth_Monitor')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''sp_disk_growth_Monitor'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'sp_disk_growth_Monitor'
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'sp_disk_growth_Monitor', @owner_login_name = N'sa', @description = N'Monitors disk space and issues E mail', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 2, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0, @notify_email_operator_name = N'Jim Exon'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'sp_disk_growth_Monitor', @command = N'sp_disk_growth_Monitor 5', @database_name = N'Database_Administrator_Monitoring', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'sp_disk_growth_monitor', @enabled = 1, @freq_type = 8, @active_start_date = 20061120, @active_start_time = 80000, @freq_interval = 127, @freq_subday_type = 8, @freq_subday_interval = 1, @freq_relative_interval = 0, @freq_recurrence_factor = 1, @active_end_date = 99991231, @active_end_time = 170000
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy