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 |
|
rb1373
Yak Posting Veteran
93 Posts |
 Posted - 2005-03-12 : 13:38:06
|
| How do I configure a SQL server alert to detect if a particular drive only has 500M of space left?Thanks,Ray |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-12 : 14:56:35
|
| Create the following procedure in master:CREATE PROCEDURE sp_checkdrivespace @drive char(1)='C', @space int=500 ASSET NOCOUNT ONDECLARE @msg varchar(128)create table #space(drive char(1) not null, mb int not null)insert #space exec master..xp_fixeddrivesselect @msg='Drive ' + @drive + ': has less than ' + cast(@space as varchar)+ ' MB free'if exists(select * from #space where drive=@drive and mb<@space)BEGIN exec master..sp_trace_generateevent @eventid=82, @userinfo=@msg, @userdata=0xFFRAISERROR(@msg, 16, 1)ENDdrop table #spaceCreate a scheduled job with a single T-SQL step of the following:EXEC sp_checkdrivespace 'C', 500Change 'C' to match the drive you want to check, and 500 to the number of MB of free space you want to be alerted for.You have 2 options for being alerted. One way is to have the job email you on failure. The RAISERROR will throw an error if the drive falls below the free space setting. The other option is to create an alert for a SQL Server Performance condition. In Enterprise Manager, create a new alert with the following settings:Type: SQL Server performance condition alertObject: SQL Server:User SettableCounter: QueryInstance: User Counter 1Alert if counter: rises above 0You can then set the Response tab settings to email, page, or net send whatever message you want. The stored procedure will generate a user-defined event that the alert will pick up.You can have both the job and the alert notify you. Schedule the job to run every hour, 2 hours, whatever works best for you. You can also set up alerts for log and data file sizes and autogrow events too, and have them execute the space check job to see if they cause the space to fall below the minimum.You can also run the procedure manually, if it generates an error you'll know you're out of disk space on that drive. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-13 : 08:03:00
|
| I would also want the job to store [in a table] the free space - so that it can be reviewed periodically to predict when upgrade will be necessary. Perhaps record daily High/Low on disk space.Kristen |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-13 : 08:34:55
|
I'll let you write that one.  |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-03-13 : 13:19:40
|
| Why don't you use Performance Monitor or something like NetIQ or MOM to do this? In Performance Monitor, you just need to setup an alert to email, net send, or execute a program when the free space goes below 500mb. You can also set it up to store trace data every 15 minutes (or whatever time you want), so you can do historical tracking.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-14 : 01:53:00
|
| "I'll let you write that one."The service has gone to the bogs ... Kristen |
|
|
|
Calen
Starting Member
2 Posts |
Posted - 2005-03-25 : 15:31:53
|
Thank you Rob!!! This was exactly what I was looking for. I've been struggling with our network admin over how much space will be allocated for our databases. He doesn't believe that tempdb can grow huge during backups, and wants to limit the free space to only 20% of the total size of all the database files (mdf's, ndf's, ldf's, and backups). But, now we can both be notified. What's even better is that he has a pager and I don't, so he'll be the one getting the middle of the night call.  |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-26 : 00:29:33
|
| "Lets see now ... we could set the backups to run at 9am .... or 2am ..." Kristen |
|
|
|
|
|
|
|
|
alert for low drive space