Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Monitoring Drive and Database Free Space Expand / Collapse
Author
Message
Posted Friday, July 1, 2005 7:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Friday, July 1, 2005 9:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, July 1, 2005 9:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Wednesday, July 6, 2005 3:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Thursday, July 21, 2005 8:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 24, 2014 2:43 PM
Points: 23, 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..




Post #203141
Posted Friday, July 22, 2005 6:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Friday, July 22, 2005 9:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, August 22, 2005 7:58 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Friday, August 25, 2006 12:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 8, 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
Posted Tuesday, April 10, 2007 1:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 12:33 PM
Points: 5, Visits: 495
it seems that none of the links in the article are working.. could you please fix... thanks
Post #357302
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse