SQLServerCentral / Discuss Content Posted by Mark Nash / Article Discussions / Article Discussions by Author / Monitoring Drive and Database Free Space / Latest Posts

RE: Monitoring Drive and Database Free Space

Siten0308 — Thu, 14 Jun 2012 09:03:04 GMT

Hello,Thank you for the code, the only problem I am having with this is the:from msdb..DASDwhat table is that? where did you get that from, its throwing the error message 'invalid object name 'msdb..DASD'thanks again

RE: Monitoring Drive and Database Free Space

merchiem — Thu, 27 Aug 2009 16:02:38 GMT

hey Mark,I read this Article and i am trying to implement it, however the Report is coming up with the database name only. 1. How do i get all the information in the database. 2. How do i go about changing the date to run lets say Tuesday at 1:00 PM in the sp_DASD_Report.sql. Thanks in advance

RE: Monitoring Drive and Database Free Space

jim.smith — Thu, 13 Nov 2008 08:21:06 GMT

Hi ThereI'm also trying to get this to work in SQL 2005 and am struggling. If anyone has documented what needs to change in the scripts for this to work in the 2005 environment that would be great!Thanks in advance.

RE: Monitoring Drive and Database Free Space

Dave Myers-429047 — Mon, 27 Oct 2008 16:54:13 GMT

I have installed this into our TEST environment and when I execute the dbo.sp_Monitor_Capture_DB_Space_Info procedureMsg 8152, Level 16, State 2, Line 1String or binary data would be truncated.The statement has been terminated.Msg 8152, Level 16, State 14, Procedure sp_Monitor_Capture_DB_Space_Info, Line 105String or binary data would be truncated.The statement has been terminated.Msg 8152, Level 16, State 14, Procedure sp_Monitor_Capture_DB_Space_Info, Line 146String or binary data would be truncated.The statement has been terminated.Msg 8152, Level 16, State 2, Line 1String or binary data would be truncated.The statement has been terminated.Msg 8152, Level 16, State 14, Procedure sp_Monitor_Capture_DB_Space_Info, Line 105String or binary data would be truncated.The statement has been terminated.Msg 8152, Level 16, State 14, Procedure sp_Monitor_Capture_DB_Space_Info, Line 146String or binary data would be truncated.The statement has been terminated.Msg 8115, Level 16, State 2, Line 1Arithmetic overflow error converting expression to data type int.The statement has been terminated.Msg 8152, Level 16, State 2, Line 1String or binary data would be truncated.The statement has been terminated.Msg 8152, Level 16, State 14, Procedure sp_Monitor_Capture_DB_Space_Info, Line 105String or binary data would be truncated.The statement has been terminated.Msg 8152, Level 16, State 14, Procedure sp_Monitor_Capture_DB_Space_Info, Line 146String or binary data would be truncated.The statement has been terminated.I receive the below errors when I execute the sp_HH_MSSQL_Recovery_Info procedureMsg 208, Level 16, State 1, Procedure sp_HH_MSSQL_Recovery_Info, Line 56Invalid object name 'master..sysfiles1'.And finally when I run the DASD_report , there is no size data (probably because the other procedures failed to populate completely)Any assistance in

RE: Monitoring Drive and Database Free Space

paksyeet — Tue, 03 Jun 2008 19:08:09 GMT

Hi Chunghow will i execute your script?!?;phow can i view the results.. thanks

RE: Monitoring Drive and Database Free Space

k.przewozniak — Wed, 27 Feb 2008 02:31:02 GMT

Hello All, Can you tell me if the procedures need update for SQL Server 2005?

RE: Monitoring Drive and Database Free Space

k.przewozniak — Thu, 10 Jan 2008 08:37:45 GMT

Good morning everyone,I want to implement jobs from the article. With first one I had no problem - Drive Space Monitoring - however my job with the database space info is failing. When I run manually one by one there is no problem, when I start a job then the second step (Capture Space Info on all DBs) finishes with the error message"[i]String or binary data would be truncated. [SQLSTATE 22001] (Error 8152) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.[/i]Do you have any ideas what can cause the problem? I run it under SQL Server 2005Regards, Kasia11.01.08I have turned off ANSI WARNINGS by using the following SET command: SET ANSI_WARNINGS OFF. Now job runs with success, however I am not sure if I am not missing any data..

RE: Monitoring Drive and Database Free Space

Andries Vermaak-433333 — Mon, 08 Oct 2007 06:32:27 GMT

The links to the Stored Procs are broken. Does anybody stil have the code or a new link to the SP's

RE: Monitoring Drive and Database Free Space

Bannji Kumoye — Wed, 11 Apr 2007 03:55:00 GMT

yes i have thesame problem overhere, none of the links are working rightnow, whats up?

RE: Monitoring Drive and Database Free Space

Hagop Hagopian — Tue, 10 Apr 2007 13:38:00 GMT

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

RE: Monitoring Drive and Database Free Space

chung-327878 — Fri, 25 Aug 2006 12:21:00 GMT

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 codingconst 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 CODESconst 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 CODEconst 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 thresholdconst CONVERSION_FACTOR = 1048576const WARNING_THRESHOLD = 2000

Set objFSO = CreateObject("Scripting.FileSystemObject")Set objNewFile = objFSO.CreateTextFile("statusSize.htm")objNewFile.WriteLine H_HEADobjNewFile.WriteLine T_HEADobjNewFile.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_TABLEobjNewFile.WriteLine H_CLOSE

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

STOP

Sub BuildRow(pvsComputer, pvsDriveLetter, pvsDriveDesc)Dim FreeMegaBytesDim SizeMegaBytesDim objWMIServiceDim colProcessesDim 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

RE: Monitoring Drive and Database Free Space

Henrik Staun Poulsen-237657 — Mon, 22 Aug 2005 07:58:00 GMT

Hi Mark,

It works. Running DBCC UPDATEUSAGE works.

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

Best regards,

Henrik Staun Poulsen

RE: Monitoring Drive and Database Free Space

Tory Skyers — Fri, 22 Jul 2005 09:30:00 GMT

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.

RE: Monitoring Drive and Database Free Space

Henrik Staun Poulsen-237657 — Fri, 22 Jul 2005 06:25:00 GMT

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

RE: Monitoring Drive and Database Free Space

markenash — Thu, 21 Jul 2005 08:56:00 GMT

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

RE: Monitoring Drive and Database Free Space

Henrik Staun Poulsen-237657 — Wed, 06 Jul 2005 03:08:00 GMT

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 -19600000 -1109120 -19487992 -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

RE: Monitoring Drive and Database Free Space

Aaron Myers — Fri, 01 Jul 2005 09:51:00 GMT

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

RE: Monitoring Drive and Database Free Space

Tory Skyers — Fri, 01 Jul 2005 09:30:00 GMT

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?

RE: Monitoring Drive and Database Free Space

Aaron Myers — Fri, 01 Jul 2005 07:57:00 GMT

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.

RE: Monitoring Drive and Database Free Space

markenash — Fri, 06 Aug 2004 08:15:00 GMT

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.

RE: Monitoring Drive and Database Free Space

rscarlson — Fri, 06 Aug 2004 06:54:00 GMT

I found this an easier approach

I run the following batch file at logon:

cscript status.vbsrem 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 codingconst 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 CODESconst 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 CODEconst 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 thresholdconst CONVERSION_FACTOR = 1048576const 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_HEADobjNewFile.WriteLine T_HEADobjNewFile.WriteLine B_HEAD & Now() & "</H3>"objNewFile.WriteLine "<FONT COLOR=PURPLE><B>DATABASE SPACE ON DRIVE E:</B></FONT>"objNewFile.WriteLine S_TABLEComputer = "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 IfobjNewFile.WriteLine E_TABLEobjNewFile.WriteLine H_CLOSE

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

RE: Monitoring Drive and Database Free Space

markenash — Tue, 13 Jul 2004 05:52:00 GMT

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

RE: Monitoring Drive and Database Free Space

Sree-121435 — Mon, 12 Jul 2004 19:09:00 GMT

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,

RE: Monitoring Drive and Database Free Space

Joan OBryan — Thu, 01 Jul 2004 08:58:00 GMT

Awsome. Thanks, Steve. Can you also change my vote to "Excellent" for me?

RE: Monitoring Drive and Database Free Space

Steve Jones - SSC Editor — Thu, 01 Jul 2004 07:32:00 GMT

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

RE: Monitoring Drive and Database Free Space

Joan OBryan — Thu, 01 Jul 2004 07:21:00 GMT

This article is a good read, but would be much more useful if the rest of the code was included.

RE: Monitoring Drive and Database Free Space

Kishore.P — Thu, 01 Jul 2004 01:56:00 GMT

Some stored procedures were missed, likeEXEC msdb..sp_Monitor_Capture_DB_Space_InfoEXEC msdb.dbo.sp_Monitor_Email_Low_DB_FreeSpace 0.1EXEC msdb..sp_MSSQL_Recovery_Info

RE: Monitoring Drive and Database Free Space

Fred Salchli — Wed, 30 Jun 2004 23:06:00 GMT

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

Thanks!

Monitoring Drive and Database Free Space

markenash — Mon, 21 Jun 2004 13:39:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnist