InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 00:38:17 GMT20http://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxI created an SSRS report that reads the data table and makes graphs off of that.Mon, 05 Mar 2012 12:49:29 GMTshaun.stuarthttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxThanks Shaun! The latest script works without error.How do you normally display the data in a report? I'm currently running the sproc without using the powershell script.Sun, 04 Mar 2012 18:50:28 GMTEugene.Tehhttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxSure. The latest version can always be found here: http://shaunjstuart.com/archive/2011/07/drive-space-monitoring-page/Wed, 22 Feb 2012 22:10:58 GMTshaun.stuarthttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxThat's odd, it worked correctly for me with servers without numbers in front, but not if there were numbers. Could you send me a link to your routines? Maybe I'm using a different version of your sproc.Wed, 22 Feb 2012 17:31:17 GMTEugene.Tehhttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxEugene - I am unable to duplicate this issue. I created a VM named 1SQLSVR, installed SQL 2008 R2 on it, and loaded my routines. Everything worked correctly.Wed, 22 Feb 2012 12:36:32 GMTshaun.stuarthttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxI believe there's a bug with the sproc, where if the @servername starts with a number, e.g. "1SQLServer", the following error would be thrown.Msg 102, Level 15, State 1, Line 13Incorrect syntax near '1'.Msg 102, Level 15, State 1, Line 13Incorrect syntax near '1'.(0 row(s) affected)Wed, 22 Feb 2012 01:11:37 GMTEugene.Tehhttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxI just wanted to let people know I found a bug in the stored procedure portion of this that may result in the @@SERVERNAME system variable being set to NULL after a restart on the machine this routine is executed on. Details and a fix are at [url=http://shaunjstuart.com/archive/2011/01/drive-space-monitoring-gets-an-update-bug-fix]http://shaunjstuart.com/archive/2011/01/drive-space-monitoring-gets-an-update-bug-fix[/url]. Not sure if this is due to a bug in the sp_dropserver system stored procedure or if it is by design.Mon, 24 Jan 2011 07:53:43 GMTshaun.stuarthttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxI've updated the Powershell portion of this routine to collect the total disk free space amount for the drives SQL Server uses. this also requires adding one column to the DatabaseDiskStats table. Details can be found at my blog: [url=http://shaunjstuart.com/archive/2010/11/drive-space-monitoring-gets-an-update-update]http://shaunjstuart.com/archive/2010/11/drive-space-monitoring-gets-an-update-update[/url] This should allow you to have better insight into your disk space usage.Sorry, still no mount point support - I don't have a configuration I can use for testing that.ShaunMon, 01 Nov 2010 07:53:11 GMTshaun.stuarthttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxHi, we use mountpoints in our clustered SQL instance. I think it would be great to include this into your scriptsThu, 14 Oct 2010 06:02:49 GMTLyn Duonghttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxA WMI Query can also be used for the sameWed, 13 Oct 2010 06:41:25 GMTRaunak Jhawarhttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxThe square brackets I was referring to can be seen in my implemented code. It's just a simple case of adding the square brackets in the dynamic SQL.[code="sql"]-- =============================================-- Author: Shaun Stuart, shaunjstuart.com-- Create date: August 24, 2010-- Description: Procedure to collect disk space usage data-- http://www.sqlservercentral.com/articles/Administration/71168/-- =============================================ALTER PROCEDURE [dbo].[GatherServerDiskUsageData]ASDECLARE @ServerName varchar(128)DECLARE @ServerNamePK intDECLARE @PersistLink bitDECLARE @SQLCmd varchar(1000)DECLARE @LinkedServerLogin varchar(50)DECLARE @LinkedServerLoginpwd varchar(50)DECLARE @SQLServerVersion char(4)DECLARE @TrackTotalDiskSpace bitSET @LinkedServerLogin = '****'SET @LinkedServerLoginpwd = '****'/* Get a list of the servers to gather data from and their SQL version */DECLARE ServersCursor CURSOR FORSELECT ServerName, PK, PersistLink, SQLServerVersion, TrackTotalDiskSpaceFROM ServersToCheckDiskStats OPEN ServersCursorFETCH NEXT FROM ServersCursor INTO @ServerName, @ServerNamePK, @PersistLink, @SQLServerVersion, @TrackTotalDiskSpaceWHILE @@FETCH_STATUS = 0BEGIN/* create linked server to get disk stats. Check PersistLink value to see if linked server should remain or be deleted */ IF Exists ( SELECT * FROM master.sys.servers WHERE @ServerName = name) AND @PersistLink = 0 BEGIN EXEC sp_dropserver @ServerName, droplogins END IF @PersistLink = 0 /*if link is persisted, linked server is already present, no need to add */ BEGIN EXEC sp_addlinkedserver @server=@ServerName END EXEC sp_addlinkedsrvlogin @ServerName, 'false', NULL, @LinkedServerLogin, @LinkedServerLoginPwd/* pull disk usage data for each database file using the SQL version-appropriate system table */ SELECT @SQLCmd = CASE @SQLServerVersion WHEN '2008' THEN 'INSERT DatabaseDiskStats (ServersToCheckPK, Server_name, Database_name, [Filename], Drive_letter, Size_in_MB) SELECT ' + convert(varchar(6),@ServerNamePK) + ',' + ''''+ @ServerName + '''' + ', sdbs.name AS database_name , smf.physical_name, UPPER(LEFT(smf.physical_name, 2)) AS drive_letter , (smf.size * 8 ) / 1024 AS size_in_mb FROM [' + @ServerName + '].master.sys.master_files AS smf, [' + @ServerName + '].master.sys.databases sdbs WHERE smf.database_id = sdbs.database_id AND sdbs.name <> ''tempdb'' ORDER BY smf.database_id' WHEN '2005' THEN /*same as 2008 */ 'INSERT DatabaseDiskStats (ServersToCheckPK, Server_name, Database_name, [Filename], Drive_letter, Size_in_MB) SELECT ' + convert(varchar(6),@ServerNamePK) + ',' + ''''+ @ServerName + '''' + ', sdbs.name AS database_name , smf.physical_name, UPPER(LEFT(smf.physical_name, 2)) AS drive_letter , (smf.size * 8 ) / 1024 AS size_in_mb FROM [' + @ServerName + '].master.sys.master_files AS smf, [' + @ServerName + '].master.sys.databases sdbs WHERE smf.database_id = sdbs.database_id AND sdbs.name <> ''tempdb'' ORDER BY smf.database_id' WHEN '2000' THEN 'INSERT DatabaseDiskStats (ServersToCheckPK, Server_name, Database_name, [Filename], Drive_letter, Size_in_MB) SELECT ' + convert(varchar(6),@ServerNamePK) + ',' + ''''+ @ServerName + '''' + ', sdbs.name AS database_name , LTRIM(RTRIM((saf.filename))), UPPER(LEFT(saf.filename, 2)) AS drive_letter , (saf.size * 8 ) / 1024 AS size_in_mb FROM [' + @ServerName + '].master.dbo.sysaltfiles AS saf, [' + @ServerName + '].master.dbo.sysdatabases sdbs WHERE saf.dbid = sdbs.dbid AND sdbs.name <> ''tempdb'' ORDER BY saf.dbid' ELSE /* not a SQL version this procedure handles */ 'INSERT DatabaseDiskStats (ServersToCheckPK, Server_name, Database_name, [Filename], Drive_letter) SELECT ' + convert(varchar(6),@ServerNamePK) + ',' + ''''+ @ServerName + '''' + ',' + '''' + 'undefined SQL version in table ServersToCheckDiskStats' + '''' + ' AS database_name ,' + '''' + 'ERROR' + '''' + ',' + '''' + '*' + '''' END EXEC (@SQLCmd) /* Tempdb has to be handled differently. In 2005+, the size for tempdb stored in sys.master_files is not the current size of the file on disk. It is the initial size it will be created with when SQL Server restarts. The current size of tmepdb on disk is stored in tempdb.sys.database_files. */ SELECT @SQLCmd = CASE @SQLServerVersion WHEN '2008' THEN 'INSERT DatabaseDiskStats (ServersToCheckPK, Server_name, Database_name, [Filename], Drive_letter, Size_in_MB) SELECT ' + convert(varchar(6),@ServerNamePK) + ',' + ''''+ @ServerName + '''' + ', ''tempdb'' AS database_name , sdf.physical_name, UPPER(LEFT(sdf.physical_name, 2)) AS drive_letter , (sdf.size * 8 ) / 1024 AS size_in_mb FROM [' + @ServerName + '].tempdb.sys.database_files AS sdf' WHEN '2005' THEN /*same as 2008 */ 'INSERT DatabaseDiskStats (ServersToCheckPK, Server_name, Database_name, [Filename], Drive_letter, Size_in_MB) SELECT ' + convert(varchar(6),@ServerNamePK) + ',' + ''''+ @ServerName + '''' + ', ''tempdb'' AS database_name , sdf.physical_name, UPPER(LEFT(sdf.physical_name, 2)) AS drive_letter , (sdf.size * 8 ) / 1024 AS size_in_mb FROM [' + @ServerName + '].tempdb.sys.database_files AS sdf' WHEN '2000' THEN 'INSERT DatabaseDiskStats (ServersToCheckPK, Server_name, Database_name, [Filename], Drive_letter, Size_in_MB) SELECT ' + convert(varchar(6),@ServerNamePK) + ',' + ''''+ @ServerName + '''' + ', ''tempdb'', LTRIM(RTRIM((sf.filename))), UPPER(LEFT(sf.filename, 2)) AS drive_letter , (sf.size * 8 ) / 1024 AS size_in_mb FROM [' + @ServerName + '].tempdb.dbo.sysfiles AS sf' ELSE /* not a SQL version this procedure handles */ 'INSERT DatabaseDiskStats (ServersToCheckPK, Server_name, Database_name, [Filename], Drive_letter) SELECT ' + convert(varchar(6),@ServerNamePK) + ',' + ''''+ @ServerName + '''' + ',' + '''' + 'undefined SQL version in table ServersToCheckDiskStats' + '''' + ' AS database_name ,' + '''' + 'ERROR' + '''' + ',' + '''' + '*' + '''' END EXEC (@SQLCmd) /* Set MaxDriveSize_in_MB to zero for drives we are not tracking the size of */ IF @TrackTotalDiskSpace = 0 BEGIN UPDATE DatabaseDiskStats SET MaxDriveSize_in_MB = 0 WHERE Server_name = @ServerName AND MaxDriveSize_in_MB is NULL END IF @persistLink = 0 BEGIN EXEC sp_dropserver @ServerName, droplogins END FETCH NEXT FROM ServersCursor INTO @ServerName, @ServerNamePK, @PersistLink, @SQLServerVersion, @TrackTotalDiskSpaceENDCLOSE ServersCursorDEALLOCATE ServersCursor[/code]Wed, 13 Oct 2010 04:54:35 GMTd trotmanhttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxAh.. the first error is, as it says, a foreign key violation. Change your entries in the ServersToCheckDiskStats table to have square brackets around the server names.The second error was probably due to the first error.Tue, 12 Oct 2010 13:29:02 GMTshaun.stuarthttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxThat unfortunately doesn't work either. [code="plain"]Msg 547, Level 16, State 0, Line 1The INSERT statement conflicted with the FOREIGN KEY constraint "FK_DatabaseDiskStats_ServersToCheckDiskStats1". The conflict occurred in database "DriveSpaceStats", table "dbo.ServersToCheckDiskStats", column 'ServerName'.The statement has been terminated.Msg 7202, Level 11, State 2, Line 1Could not find server 'SQLSERVERNAME' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.[/code]Thanks for your help. Man, I wish I named these servers! :unsure:Tue, 12 Oct 2010 13:02:09 GMTbbowersockhttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxTry changing the first part of the prodcedure to this:/* Get a list of the servers to gather data from and their SQL version */DECLARE ServersCursor CURSOR FORSELECT '[' + ServerName + ']', PK, PersistLink, SQLServerVersion, TrackTotalDiskSpaceFROM ServersToCheckDiskStats (only the first SELECT line changed)I haven't test this, but I think it will work.Tue, 12 Oct 2010 12:03:58 GMTshaun.stuarthttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxOk, I wasn't really understanding what he was referring to. Are you saying to place brackets around the "ServerName" in the SELECT statement at the beginning of the procedure?[code="sql"]/* Get a list of the servers to gather data from and their SQL version */DECLARE ServersCursor CURSOR FORSELECT [b][i]ServerName[/i][/b], PK, PersistLink, SQLServerVersion, TrackTotalDiskSpaceFROM ServersToCheckDiskStats [/code]I tried putting it in brackets there and the same error occurs. Does the variable [code="sql"]@ServerName[/code] need to be in brackets as well? I'm not understanding the posts.Please bare with me as I am not a T-SQL guru. Thanks for your patience. The script overall is wonderful and is something I've been looking for for a long time.Tue, 12 Oct 2010 11:42:44 GMTbbowersockhttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxModifying the script to put square brackets around the server name should to the trick. This is what d trotman was referring to in post #999321.Tue, 12 Oct 2010 10:50:28 GMTshaun.stuarthttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxIf your server name has a hyphen in it, the stored procedure fails with the following error:[code="plain"][font="Courier New"]Msg 102, Level 15, State 1, Line 13Incorrect syntax near '-'.Msg 102, Level 15, State 1, Line 13Incorrect syntax near '-'.[/font][/code]Is there a way around this?Tue, 12 Oct 2010 09:48:22 GMTbbowersockhttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxI'm a bit busy today to try to work on this, but at first glance, I think you'd need to remove the "\instancename" from the $servername variable in this line:$Driveobject=gwmi win32_logicaldisk -computername $servername -filter "DeviceID='$DriveLetter'"ShaunWed, 06 Oct 2010 09:01:12 GMTshaun.stuarthttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxIn regard to the Server Instances. When you have a servername\instancename as the servername the powershell script does not return a value for the maximum size of the drive. I am rubbish at Powershell and therefore cannot work out how to fix this. Hope you can help Shaun.Wed, 06 Oct 2010 08:24:39 GMTd trotmanhttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxGood point. Thanks!Wed, 06 Oct 2010 07:55:58 GMTshaun.stuarthttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxGreat article and scripts. I'm in the process of trying them out now. Just a small issue. Please remember the square brackets [] in your dynamic SQL either side of the servername. That way server instances will get taken care of also.Wed, 06 Oct 2010 07:54:44 GMTd trotmanhttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxDAMM!!!....sorry guys....I saw now the similar posts :-SWed, 06 Oct 2010 05:00:00 GMTDominic-230373http://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxIt works fine for usual drives with a drive-letterAs soon as a disk has been [b]mounted [/b]as a subfolder in a existing drive and the databases are within the mounted drive....it doesn't work.Regards,DominicWed, 06 Oct 2010 03:22:21 GMTDominic-230373http://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxStill can't get the files. Anyone else have any luck ?Oops ! my bad... I have it working now...ThanX for the great scriptAmcAmxTue, 05 Oct 2010 15:16:07 GMTAmcAmxhttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxGlad to help!Mon, 04 Oct 2010 19:38:07 GMTshaun.stuarthttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxThough, I did not test it yet on my environment right now but I'm very thankful and very grateful to Shaun for having this kind of article... Thanks a lot Shaun!!!! :-)Mon, 04 Oct 2010 17:58:02 GMTmcvilbarhttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxThanks for the article.Mon, 04 Oct 2010 12:36:10 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspx[quote][b]Mat Raftree (10/4/2010)[/b][hr]We had to roll our own solution since we are using windows mountpoints for mapping new SAN luns into our clusters. Any chance you will be adressing that issue?[/quote]It can be done in Powershell using the "win32_mountpoint" class... I've been experimenting with it myself as I have the exact same problem. :-)Mon, 04 Oct 2010 09:49:39 GMTCJ_Cerrohttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxProbably not, since I don't have a setup like that that I can test with. Sorry!Mon, 04 Oct 2010 09:37:43 GMTshaun.stuarthttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxWe had to roll our own solution since we are using windows mountpoints for mapping new SAN luns into our clusters. Any chance you will be adressing that issue?Mon, 04 Oct 2010 09:33:06 GMTMat Raftreehttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxThanksMon, 04 Oct 2010 07:41:23 GMTdavids-1023752http://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxVery cool. Not seeing the attached script either.Mon, 04 Oct 2010 07:41:13 GMTbwolfhttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxThe script is linked to in a zipfile at the bottom of the article. It's under the "Resources" heading and it's called DiskSpaceStats.zip. FYI, I wrote a part 2 to this article on my blog at [url=http://shaunjstuart.com/archive/2010/10/im-published/]http://shaunjstuart.com/archive/2010/10/im-published/[/url] which details how I used the data this collects to make a SSRS report.ShaunMon, 04 Oct 2010 07:39:19 GMTshaun.stuarthttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxThanks.....Paul M.Mon, 04 Oct 2010 07:07:40 GMTPaul MacDonald-321642http://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxI have attached the script to the article.My apologies.Mon, 04 Oct 2010 06:54:08 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxShaun...any update on the files????ThanksMon, 04 Oct 2010 06:47:37 GMTPaul MacDonald-321642http://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxHay, Can you provide scripts?Mon, 04 Oct 2010 06:06:30 GMTjatin-243645http://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxYou have not zip file, nothing is attached make sure to have everything in place and then post the blog.Mon, 04 Oct 2010 04:57:18 GMTAkber Alwanihttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxPhew glad it's not just me as well who can't see the zip files.Mon, 04 Oct 2010 03:45:15 GMTkaren.blakehttp://www.sqlservercentral.com/Forums/Topic997244-2803-1.aspxwhere is the scripts ??Mon, 04 Oct 2010 03:22:14 GMTalnawrass2002