Click here to monitor SSC
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
markenash
markenash
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 129
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnist



Fred Salchli
Fred Salchli
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 2

Where's the beef? Most of the code to utilize this outstanding monitoring system is not included! Please make source available.

Thanks!


Kishore.P
Kishore.P
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1395 Visits: 619
Some stored procedures were missed, like

EXEC msdb..sp_Monitor_Capture_DB_Space_Info
EXEC msdb.dbo.sp_Monitor_Email_Low_DB_FreeSpace 0.1
EXEC msdb..sp_MSSQL_Recovery_Info



Joan OBryan
Joan OBryan
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 552
This article is a good read, but would be much more useful if the rest of the code was included.
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36232 Visits: 18751
Apologies!

My fault. Mark asked me to link it in and I forgot . It's up there now.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Joan OBryan
Joan OBryan
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 552
Awsome. Thanks, Steve. Can you also change my vote to "Excellent" for me?
Sree-121435
Sree-121435
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1

I have followed all steps but couldn't able to produce the report when i ran sp_DASD_Report it only shows servername and list of databases on that particular server.

Is there anyting need to be modified for the given code?

Thanks,


markenash
markenash
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 129

Good morning Sree,

I am sorry to hear you have had problems. Here are the points I check first.

If the dates on the report are all '20201225' (December 25, 2020) then you are just reporting the 'separator' row. I put in this row just to create a break between databases. I figured I would never be using this system in 2020 :-)

- Do a TSQL select on the DASD table and just make sure there are rows in it other than the above separator rows.

- We run the job that populates the DASD table once daily, between 04:00 and 04:59 on all servers. We found that reporting on all days of the week was too much detailed information for a general report, so we limited it to just report DASD rows created on Friday between 04:00 - 04:59.

Possibly the issue in the 'insert into #DASDRpt' statement. The 'select' in this statement was designed to only select rows created on

1) Friday "datepart(dw,(convert(....)))) = 6"

2) created during hour '04' "substring(createDTM,9,2) = '04'"

If you have not scheduled the job that creates DASD rows to run at least on Friday between 04:00 - 04:59 you will not return any rows.

If the date, time selects are the issue, modify the report sql as you wish.

Let me know if this information helps, if not we will look further!!





rscarlson
rscarlson
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 6

I found this an easier approach

I run the following batch file at logon:

cscript status.vbs
rem pause

The status.vbs is as below, you need admin access, the name of the computer and a drive mapped to same, and the drive letter of the drive you want to read the size of. This returns a web page with the drive size in mega bytes and alerts if less than 2 gigabytes (the threshold = 2000). I monitor several databases with this script and the web page shows at a glance potential trouble spots (just repeat the code for other dbs).

' 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>STATUS 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

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer)
Set colProcesses = objWMIService.ExecQuery _
("SELECT * FROM Win32_Process")
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 "<FONT COLOR=PURPLE><B>DATABASE SPACE ON DRIVE E:</B></FONT>"
objNewFile.WriteLine S_TABLE
Computer = "ComputerName"
Set objWMIService = GetObject("winmgmts://" & Computer)
Set objLogicalDisk = objWMIService.Get("Win32_LogicalDisk.DeviceID='e:'")
FreeMegaBytes = objLogicalDisk.FreeSpace / CONVERSION_FACTOR
If FreeMegaBytes < WARNING_THRESHOLD Then
objNewFile.WriteLine "<TR><TD BGCOLOR=WHITE><FONT COLOR=RED SIZE=+1><B>INADEQUATE</B></FONT></TD>"
objNewFile.WriteLine "<TD BGCOLOR=WHITE>Actual Space " & Int(FreeMegaBytes) & " Megabytes.</TD></TR>"
Else
objNewFile.WriteLine "<TR><TD BGCOLOR=CCFFCC>ADEQUATE</TD>"
objNewFile.WriteLine "<TD BGCOLOR=CCFFCC>Actual Space " & Int(FreeMegaBytes) & " Megabytes.</TD></TR>"
End If
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





markenash
markenash
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 129

Good morning rscarlson,

Our space monitoring system was presented in the article for review and evaluation by fellow DBA's. Those that deemed it useful were free to install all or part of it at their sites. This system fits our institutions needs for space management.

You have developed your own system for space management, we are glad it fulfills your sites needs.





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