Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»»

Drive Space Monitoring Gets An Update Expand / Collapse
Author
Message
Posted Wednesday, October 6, 2010 7:55 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 8:24 AM
Points: 1,453, Visits: 740
Good point. Thanks!
Post #999323
Posted Wednesday, October 6, 2010 8:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 1, 2014 7:46 AM
Points: 29, Visits: 554
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.
Post #999383
Posted Wednesday, October 6, 2010 9:01 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 8:24 AM
Points: 1,453, Visits: 740
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
Post #999460
Posted Tuesday, October 12, 2010 9:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 10, 2011 3:45 PM
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?
Post #1002955
Posted Tuesday, October 12, 2010 10:50 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 8:24 AM
Points: 1,453, Visits: 740
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.
Post #1003028
Posted Tuesday, October 12, 2010 11:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 10, 2011 3:45 PM
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.
Post #1003072
Posted Tuesday, October 12, 2010 12:03 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 8:24 AM
Points: 1,453, Visits: 740
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.
Post #1003087
Posted Tuesday, October 12, 2010 1:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 10, 2011 3:45 PM
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!
Post #1003142
Posted Tuesday, October 12, 2010 1:29 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 8:24 AM
Points: 1,453, Visits: 740
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.
Post #1003167
Posted Wednesday, October 13, 2010 4:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 1, 2014 7:46 AM
Points: 29, Visits: 554
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




Post #1003471
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse