Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Drive Space Monitoring Gets An Update


Drive Space Monitoring Gets An Update

Author
Message
shaun.stuart
shaun.stuart
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1998 Visits: 846
Good point. Thanks!
d trotman
d trotman
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 653
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.
shaun.stuart
shaun.stuart
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1998 Visits: 846
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
bbowersock
bbowersock
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 50
If your server name has a hyphen in it, the stored procedure fails with the following error:

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '-'.


Is there a way around this?
shaun.stuart
shaun.stuart
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1998 Visits: 846
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.
bbowersock
bbowersock
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 50
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.
shaun.stuart
shaun.stuart
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1998 Visits: 846
Try 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 FOR
SELECT '[' + ServerName + ']',
PK,
PersistLink,
SQLServerVersion,
TrackTotalDiskSpace
FROM ServersToCheckDiskStats


(only the first SELECT line changed)

I haven't test this, but I think it will work.
bbowersock
bbowersock
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 50
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
shaun.stuart
shaun.stuart
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1998 Visits: 846
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.
d trotman
d trotman
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 653
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 */

DECLARE ServersCursor CURSOR FOR
SELECT ServerName,
PK,
PersistLink,
SQLServerVersion,
TrackTotalDiskSpace
FROM ServersToCheckDiskStats

OPEN ServersCursor
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
'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, @TrackTotalDiskSpace

END

CLOSE ServersCursor
DEALLOCATE ServersCursor





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