Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Mathias
Posting Yak Master
119 Posts |
 Posted - 2008-02-27 : 02:23:16
|
| Is there a way of creating an alert on free disk size?I mean by using WMI event alert |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2008-02-27 : 02:50:09
|
| try this...--==========================CREATE PROCEDURE DBO.[proc_GetSpace] ( @DBName AS VARCHAR(30) )ASBEGIN Create table #DrvSpace ( [DriveLetter] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, MB_Free float null) INSERT #DrvSpace EXECUTE master..xp_fixeddrives select MB_Free,[DriveLetter] from #DrvSpace WHERE DriveLetter IN (SELECT left(filename,1) FROM sys.sysfiles WHERE name=@DBName) drop table #DrvSpace--************ Returns the DB size *************************** declare @dbsize numeric ---This is the varibale create table #tb (Dbname varchar(100),DBsz numeric,remarks varchar(100)) Insert into #tb exec SP_DATABASES set @dbsize =(select DBsz from #tb where dbName=@DBName) select @dbsize/1024 as [Database size] drop table #tbEND |
 |
|
|
Mathias
Posting Yak Master
119 Posts |
Posted - 2008-02-27 : 02:58:58
|
| Excellent but this doesn't use WMI, it means that I have to schedule the job. Who do I use the SQL Alert to get it generated when the event happens. If the time delay between two runs is too big, the message could not have the time to generateI am looking for something like : USE [msdb] GO EXEC msdb.dbo.sp_add_alert @name =N'Test', @enabled =1, @delay_between_responses =0, @include_event_description_in =0, @wmi_namespace =N'\\.\ROOT\CIMV2', @wmi_query =N'SELECT * FROM __InstanceModificationEvent WITHIN 600 WHERE TargetInstance ISA "Win32_LogicalDisk" AND TargetInstance.FreeSpace < 10000000' |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-02-27 : 09:38:04
|
| You might want to look into using a product like Microsoft Operations Manager or HP Open View for something like this.CODO ERGO SUM |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-02-28 : 01:24:57
|
| Check Tivoli Notifications , which is what we useJack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
|
|
|
|
|
|
|
|
Create alert on free disk size?