Monitoring Disk Space
-
Occasionally I'll have one of the transaction logs get huge and use up most of my disk space. Does anyone have any thoughts on a way to monitor for free disk space and send an alert when it hits a predetermined size ??
For example, if there's only 5% free space on the E drive, send me an email.
-
What other monitoring tools do you have in your environment? BindView? Argent? HP's SIM?
K. Brian Kelley
@kbriankelley -
We have "WhatsUp" ...... I should be able to get our network guy to implement that for me.
-
I don't believe WhatsUp does disk space monitoring, but I could be wrong. The other products I mentioned do. Also, you could throw together a quick script to monitor disk usage. Plenty of scripts of this sort at the Script Center on TechNet:
The Script Center Script Repository
K. Brian Kelley
@kbriankelley -
You could use BigBrother ("Big Brother is Free for Non-Commercial Use")
karl
Best regards
karl -
Along the script lines, we run the following task which records the size of all databases in a table. This could then be queried and if a critical size has been reached, you could send yourself a message.
insert into DBFileSize (db, fileID, filesize)
exec sp_MSforeachdb @command1 =
'select db_name(dbid), sf.fileid, sf.size
from sysdatabases sd, ?..sysfiles sf
where db_name(dbid) = ''?'''
-
I happen to know that there's going to be an article published here in a couple of days that details this sort of thing using performance monitor and an SMTP server.
But only cause I wrote it
-
I monitor not only my sql servers but the other servers on the network . I found a vbscript here that uses wmi to get drive statistics
http://www.databasejournal.com/features/mssql/article.php/10894_3296731_2
Look at method 4. This gathers drive space info and stores it in a sql table.
I run this daily to collect the information. Then daily I query the results to find any drives that are under 20% free space and send those results to my boss, Weekly I run a query for statistics for all drives on all the servers on the network so we know where we stand.
It's not fancy but it was free and does what we need.
-
You could create a Stored Proc (should be on this site too) and put it in Master on each server you want to monitor. Make all the rest of your servers as linked servers on your master ( tracking )server. Run a job looping thru all of your linked servers. It really fast. We run it every hour in our environment and we have hundeds of servers to monitor.
-
I use the following script to put the freespace in a table.
set @sql = 'insert into #TmpFS select * from OPENQUERY ([' + @ServerName + '], ''SET FMTONLY OFF exec master.dbo.xp_fixeddrives'')'
exec(@sql)
insert into Table_FreeSpace (server_name, Drive, FreeSpace)
select @ServerName, Dr, Fs
from #TmpFS
Put a cursor around it to fill @ServerName (it must off course also be a linked server). Then compare the FreeSpace against a treshold.
The thing i like about it is: you only have scripts, tables, etc on your tracking server.
Bye Gimli
-
We wrote a simple batch file that fires off svrinfo.exe (Resource Kit) at all servers we want to monitor and dumps the results out to file like so:
<ServerInfo.bat>
"c:\tools\srvinfo.exe" \\server1 > "c:\tools\server1.txt"
"c:\tools\srvinfo.exe" \\server2 > "c:\tools\server2.txt"
Set this batch file to run as a scheduled task on any machine that has network access to the servers in the list at the interval you require.
A colleague then wrote a simple ASP page to pass each text file in the folder and pull out the disk information from the srvinfo.exe results. He then staretd showing off and used VML to graph each drive and the percentage free space. We now use this as part of our daily checks.
So there's another possible solution. As with a lot of admin tasks, it's sometimes easier to write a quick script yourself rather than purchase a massive monitoring product that needs configuration and management, you then end up with a nice suite of tools.
ll
-
The solution suggested here ( http://www.databasejournal.com/features/mssql/article.php/10894_3296731_2 ) caused some problem for me because some services were turned off on the server.
I prefer some simple custom sp. I monitor db locks, freespace, connectivity, heartbeat and standby delta using xp_smtp_sendmail so I put all parameters in a single table and schedule a different job for each. The 4-5 steps to set up everything are too long to be posted so please let me know if you’d like to see it.
For freespace, I list each drive to be watched in the parameter table, including their total size and threshold. An sp is used to check every hour. You can optionally turn off each drive or adjust the threshold. The codes are mostly based on or from other true experts on or offline. I apologize for losing track of their names in advance. If you see anything familiar, my sincerely thanks to those contributors.
You’ll need xp_smtp_sendmail and a wrapper (sp_smtp_sendmail) from http://www.sqldev.net.
Due to smtp relay restrictions the script will send alert to optionally a second smtp server for delivery to a second group or recipients in a different domain.
It’s a shame that MS SQL developers condescendingly dish out these half-baked sp’s or xp’s like xp_fixeddrives. It could’ve return the total sizes as well as freespace but no, they can’t be bothered.
What’s more, I find it now necessary to monitor mapped drives as well and there is simply no built-in functionality to do that. If anyone know of something simple and elegant, please kindly let me know.
-
Thanks for all the great replies. Our "WhatsUp" product does monitor free space, so I've asked my network admin to set it up a few specific instances. I'm going to sort through all the posts here because I'd still like my own utility to alert me so I don't have to go ask every time, and I can then run it on any server.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply