October 6, 2010 at 7:55 am
Good point. Thanks!
October 6, 2010 at 8:24 am
In 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.
October 6, 2010 at 9:01 am
I'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'"
Shaun
October 12, 2010 at 9:48 am
If your server name has a hyphen in it, the stored procedure fails with the following error:
[font="Courier New"]Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '-'.[/font]
Is there a way around this?
October 12, 2010 at 10:50 am
Modifying 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.
October 12, 2010 at 11:42 am
Ok, 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?
/* Get a list of the servers to gather data from and their SQL version */
DECLARE ServersCursor CURSOR FOR
SELECT ServerName,
PK,
PersistLink,
SQLServerVersion,
TrackTotalDiskSpace
FROM ServersToCheckDiskStats
I tried putting it in brackets there and the same error occurs. Does the variable @ServerName 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.
October 12, 2010 at 12:03 pm
Try changing the first part of the prodcedure to this:
/* Get a list of the servers to gather data from and their SQL version */
DECLAREServersCursor CURSOR FOR
SELECT'[' + ServerName + ']',
PK,
PersistLink,
SQLServerVersion,
TrackTotalDiskSpace
FROMServersToCheckDiskStats
(only the first SELECT line changed)
I haven't test this, but I think it will work.
October 12, 2010 at 1:02 pm
That unfortunately doesn't work either.
Msg 547, Level 16, State 0, Line 1
The 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 1
Could 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.
Thanks for your help. Man, I wish I named these servers! :unsure:
October 12, 2010 at 1:29 pm
Ah.. 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.
October 13, 2010 at 4:54 am
The 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.
-- =============================================
-- 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]
AS
DECLARE@ServerName varchar(128)
DECLARE@ServerNamePK int
DECLARE@PersistLink bit
DECLARE@SQLCmd varchar(1000)
DECLARE@LinkedServerLogin varchar(50)
DECLARE@LinkedServerLoginpwd varchar(50)
DECLARE@SQLServerVersion char(4)
DECLARE@TrackTotalDiskSpace bit
SET@LinkedServerLogin = '****'
SET@LinkedServerLoginpwd = '****'
/* Get a list of the servers to gather data from and their SQL version */
DECLAREServersCursor CURSOR FOR
SELECTServerName,
PK,
PersistLink,
SQLServerVersion,
TrackTotalDiskSpace
FROMServersToCheckDiskStats
OPENServersCursor
FETCH NEXT FROM ServersCursor INTO @ServerName, @ServerNamePK, @PersistLink, @SQLServerVersion, @TrackTotalDiskSpace
WHILE @@FETCH_STATUS = 0
BEGIN
/* 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
'INSERTDatabaseDiskStats
(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
WHEREsmf.database_id = sdbs.database_id
AND sdbs.name <> ''tempdb''
ORDER BYsmf.database_id'
WHEN '2005' THEN/*same as 2008 */
'INSERTDatabaseDiskStats
(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
WHEREsmf.database_id = sdbs.database_id
AND sdbs.name <> ''tempdb''
ORDER BYsmf.database_id'
WHEN '2000' THEN
'INSERTDatabaseDiskStats
(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
WHEREsaf.dbid = sdbs.dbid
AND sdbs.name <> ''tempdb''
ORDER BYsaf.dbid'
ELSE/* not a SQL version this procedure handles */
'INSERTDatabaseDiskStats
(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
'INSERTDatabaseDiskStats
(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 */
'INSERTDatabaseDiskStats
(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
'INSERTDatabaseDiskStats
(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 */
'INSERTDatabaseDiskStats
(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
UPDATEDatabaseDiskStats
SETMaxDriveSize_in_MB = 0
WHEREServer_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, @TrackTotalDiskSpace
END
CLOSE ServersCursor
DEALLOCATE ServersCursor
October 13, 2010 at 6:41 am
A WMI Query can also be used for the same
Raunak J
October 14, 2010 at 6:02 am
Hi, we use mountpoints in our clustered SQL instance. I think it would be great to include this into your scripts
November 1, 2010 at 7:53 am
I'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: http://shaunjstuart.com/archive/2010/11/drive-space-monitoring-gets-an-update-update 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.
Shaun
January 24, 2011 at 7:53 am
I 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 http://shaunjstuart.com/archive/2011/01/drive-space-monitoring-gets-an-update-bug-fix. Not sure if this is due to a bug in the sp_dropserver system stored procedure or if it is by design.
February 22, 2012 at 1:11 am
I 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 13
Incorrect syntax near '1'.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '1'.
(0 row(s) affected)
Viewing 15 posts - 31 through 45 (of 64 total)
You must be logged in to reply to this topic. Login to reply