SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Monitoring Drive and Database Free Space


Monitoring Drive and Database Free Space

Author
Message
Aaron Myers
Aaron Myers
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 1
Thanks for the script Rcarlson! It doesn't quite do the same thing but it is more useful in general (non sql server administration). I think I'll parameterize the inputs so there will be nothing hardcoded inside the script.



Tory Skyers
Tory Skyers
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1
I'm an SQL newbie but very familiar w/ os level scripting, WMI can be costly be careful where and when you use it! . Your script is great btw, i use a similar script locally on some servers to monitor and kick out to an opensource system monitor however i've found that certain WMI queries can be taxing on systems.

Using a native to the db call on the system the db is running on in theory should be less costly cpu/resource wise than using wmi. Then there is wmi security, network transport security etc, all of which you avoid by using the db system service to run the queries.

On a semi-related note, did you use wbemtest to find the properties of the disk device? I've been having some trouble locating particular wmi attribute detail. Do you know if sql publishes info via wmi?
Aaron Myers
Aaron Myers
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 1
I totally agree, Mark's monitor is great for efficiently tracking individual DB's inside SQL. I get a little nervous modifying MSDB or running XP procedures so there is a little tweaking I may do there to isolate the security and data.

Tory, what is the open source system monitor you are using? I've been playing around with Perfmon logging directly to SQL, and building some of my own ASP.NET to tie the data together in web pages.

http://aaronsmyers.blogspot.com/2005/04/nlogger-update.html

If there is already something out there to help, by all means I'd like to give it a try. MOM is just too expensive to justify for most of our servers/applications.



Henrik Staun Poulsen-237657
Henrik Staun Poulsen-237657
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 16

Mark,

I'm getting a negative free_DB_Space for two of our databases. When running these two queries: select size, maxsize 'maxsize' from sysfiles where (status & 64 = 0) compute sum(size)
select sum(reserved) 'totalreserved' from [sysindexes] where indid in (0, 1, 255) I get

size maxsize
----------- -----------
319872 -1
9600000 -1
109120 -1
9487992 -1

===========
19516984


totalreserved
-------------
20830649

So the sum from sysindexes is bigger than sysfiles. Is this because we're using filegroups on these databases?

TIA

Henrik Staun Poulsen, Denmark


markenash
markenash
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 129

Hello Henrik,

Sorry for the slow response, been on vacation since 7/2/05.

Whenever we get negative numbers for free_DB_Space there has been a problem with sysindexes. Try running DBCC UPDATEUSAGE (dbname) and see if you get positive numbers..





Henrik Staun Poulsen-237657
Henrik Staun Poulsen-237657
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 16

Hi Mark,

Thank you very much for writing back to me, even just before I go on holiday (today) as well. I have tried DBCC UPDATEUSAGE (dbname) locally, and found problems, so I expect that I'll find problems on the client site as well.

Best regards

Henrik


Tory Skyers
Tory Skyers
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1
Aaron, I use nagios, http://www.nagios.org, webmin, http://www.webmin.com, and nagmin http://nagmin.sourceforge.net. It is extensible, free, cross platform, and very powerful. It takes a little getting used configs to, the can get ugly. I use nagmin to manage my configs but after a bit of reading i'm sure you can make it work for you.
Henrik Staun Poulsen-237657
Henrik Staun Poulsen-237657
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 16

Hi Mark,

It works. Running DBCC UPDATEUSAGE works.

Thank you very much for your script, and for your help.

Best regards,

Henrik Staun Poulsen


chung-327878
chung-327878
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 116

Hi Rcarlson ,

Thanks for the VB script. It works great for me. I took the liberty to parametize the server name so that I can use for multiple servers and drives. (Sorry Aaron Myers, I am one step ahead of you.) Here is the modified script:

' Returns drive size results to an HTML file.
On Error Resume Next
' HTML coding
const H_HEAD = "<HTML><HEAD>"
const T_HEAD = "<TITLE>STATUS REPORT</TITLE></HEAD>"
const B_HEAD = "<BODY BACKGROUND=one.jpg><CENTER><H3>SERVER DISK SPACE UTILIZATION REPORT - - DATE: "
const H_CLOSE = "</CENTER></BODY></HTML>"
' HTML TABLE CODES
const S_TABLE = "<TABLE BORDER=4 CELLPADDING=3>"
const T_TABLE = "<TR><TH>FILE NAME</TH><TH>STATUS</TH></TR>"
const E_TABLE = "</TABLE><BR>"
' HTML SUCCESS OR FAILURE CODE
const F_SUCCESS = "<TD ALIGN=CENTER><FONT COLOR=GREEN>OK</FONT></TD></TR>"
const F_FAILED = "<TD ALIGN=CENTER BGCOLOR=WHITE><FONT COLOR=RED SIZE=+1><B>FAILED</B></FONT></TD></TR>"
' Conversion codes Bites to Megabytes and 2000 megabytes threshold
const CONVERSION_FACTOR = 1048576
const WARNING_THRESHOLD = 2000

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objNewFile = objFSO.CreateTextFile("statusSize.htm")
objNewFile.WriteLine H_HEAD
objNewFile.WriteLine T_HEAD
objNewFile.WriteLine B_HEAD & Now() & "</H3>"
objNewFile.WriteLine S_TABLE


'*** Start building display rows ***
objNewFile.WriteLine "<TR><TD>SERVER</TD> <TD>DRIVE</TD> <TD>DESC</TD> <TD>Total Size MB</TD> <TD>FREE MB</TD> <TD>STATUS</TD></TR>"

'--- Enter as many server names and drives as you like ---
Call BuildRow("servername", "C:", "Windows")

objNewFile.WriteLine "<TR></TR>"
Call BuildRow("servername", "E:", "Application")
'-------------------------------------------------------

objNewFile.WriteLine E_TABLE
objNewFile.WriteLine H_CLOSE

const MAXIMIZE_WINDOW = 3
Set objNetwork = Wscript.CreateObject("Wscript.Network")
Set objShell = Wscript.CreateObject("Wscript.Shell")
objShell.Run "statusSize.htm", MAXIMIZE_WINDOW

STOP


Sub BuildRow(pvsComputer, pvsDriveLetter, pvsDriveDesc)
Dim FreeMegaBytes
Dim SizeMegaBytes
Dim objWMIService
Dim colProcesses
Dim objLogicalDisk

Set objWMIService = GetObject("winmgmts:\\" & pvsComputer)
Set colProcesses = objWMIService.ExecQuery _
("SELECT * FROM Win32_Process")

'-- Drive Letter --
'------------------
Set objLogicalDisk = objWMIService.Get("Win32_LogicalDisk.DeviceID='" & pvsDriveLetter & "'")
FreeMegaBytes = objLogicalDisk.FreeSpace / CONVERSION_FACTOR
SizeMegaBytes = objLogicalDisk.Size / CONVERSION_FACTOR
If FreeMegaBytes < WARNING_THRESHOLD Then
strColor = "RED"
Else
strColor = "YELLOW"
End If

objNewFile.WriteLine "<TR BGCOLOR=" & strColor & " >"
objNewFile.WriteLine "<TD>" & pvsComputer & "</TD>"
objNewFile.WriteLine "<TD>" & pvsDriveLetter & "</TD>"
objNewFile.WriteLine "<TD>" & pvsDriveDesc & "</TD>"
objNewFile.WriteLine "<TD>" & Int(SizeMegaBytes) & "</TD>"
objNewFile.WriteLine "<TD>" & Int(FreeMegaBytes) & "</TD>"
If strColor = "RED" Then
objNewFile.WriteLine "<TD>" & "LOW" & "</TD>"
Else
objNewFile.WriteLine "<TD>" & "OK" & "</TD>"
End If
objNewFile.WriteLine "</TR>"
'*** End building display rows ***
End Sub


Hagop Hagopian
Hagop Hagopian
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 513
it seems that none of the links in the article are working.. could you please fix... thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search