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