Monitoring Drive and Database Free Space

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Mark Nash » Monitoring Drive and Database Free Space

29 posts, Page 2 of 3

««1 2 3 »»

Monitoring Drive and Database Free Space

Rate Topic

Display Mode

Topic Options

Author

Message

Aaron Myers

Posted Friday, July 01, 2005 7:57 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 30, 2006 2:07 AM
Points: 77, 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.

Post #196313

Tory Skyers

Posted Friday, July 01, 2005 9:30 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 25, 2007 1:02 PM
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?

Post #196386

Aaron Myers

Posted Friday, July 01, 2005 9:51 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 30, 2006 2:07 AM
Points: 77, 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.

Post #196396

Henrik Staun Poulsen-237657

Posted Wednesday, July 06, 2005 3:08 AM

Valued Member

Group: General Forum Members
Last Login: Monday, February 25, 2013 6:45 AM
Points: 70, Visits: 15

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

Post #197289

markenash

Posted Thursday, July 21, 2005 8:56 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 14, 2013 11:36 AM
Points: 23, Visits: 115

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..

Post #203141

Henrik Staun Poulsen-237657

Posted Friday, July 22, 2005 6:25 AM

Valued Member

Group: General Forum Members
Last Login: Monday, February 25, 2013 6:45 AM
Points: 70, Visits: 15

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

Post #203475

Tory Skyers

Posted Friday, July 22, 2005 9:30 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 25, 2007 1:02 PM
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.

Post #203577

Henrik Staun Poulsen-237657

Posted Monday, August 22, 2005 7:58 AM

Valued Member

Group: General Forum Members
Last Login: Monday, February 25, 2013 6:45 AM
Points: 70, Visits: 15

Hi Mark,

It works. Running DBCC UPDATEUSAGE works.

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

Best regards,

Henrik Staun Poulsen

Post #212550

chung-327878

Posted Friday, August 25, 2006 12:21 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 08, 2009 7:22 PM
Points: 14, Visits: 115

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

Post #304197

Hagop Hagopian

Posted Tuesday, April 10, 2007 1:38 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 10, 2013 10:19 AM
Points: 5, Visits: 465

it seems that none of the links in the article are working.. could you please fix... thanks

Post #357302

« Prev Topic | Next Topic »

29 posts, Page 2 of 3

««1 2 3 »»

Permissions