Drive Space Monitoring Update – October 2015

Over five years ago, I wrote my first (and so far, only) article for SQLServerCentral.com with a routine for gathering database disk stats for your SQL Server databases that live on different SQL servers. I realized that the last update I posted here was four years ago. I continue to use the script and have made several more revisions to it over the years. Here’s the latest version. Please see the original article for full details of how it works. The entire solution consists of a couple of SQL tables, a stored procedure, and an optional PowerShell script. The article also discusses the permissions needed for the script to run correctly.

For this version, there is a modification needed to the main table, DatabaseDiskStats. I added a column to track databases that are using Enterprise edition-only features, such as compression or partitioning (valid only for SQL 2008+). This is handy when you are dealing with apps such as Sharepoint, which will enable enterprise edition features without any DBA input if it detects the SQL Server supports them.

The below script will create a version of the table that is compatible with this version of the stored procedure. The only index the script creates is on the primary key. You’ll want to look at your own reporting queries to determine if any other ones should be added for your environment.

CREATE TABLE [dbo].[DatabaseDiskStats](
	[PK] [INT] IDENTITY(1,1) NOT NULL,
	[ServersToCheckPK] [INT] NOT NULL,
	[Server_name] [VARCHAR](128) NOT NULL,
	[Database_name] [VARCHAR](128) NOT NULL,
	[Filename] [VARCHAR](260) NOT NULL,
	[Drive_letter] [CHAR](2) NOT NULL,
	[Size_in_MB] [INT] NULL,
	[MaxDriveSize_in_MB] [INT] NULL,
	[StatDate] [SMALLDATETIME] NULL CONSTRAINT [DF_DatabaseDiskStats_StatDate]  DEFAULT (CONVERT([VARCHAR](10),GETDATE(),(1))),
	[DriveFreeSpace_in_MB] [INT] NULL,
	[EntDevFeaturesEnabled] [VARCHAR](200) NULL CONSTRAINT [DF_DatabaseDiskStats_EntDevFeaturesEnabled]  DEFAULT (''),
 CONSTRAINT [PK_DatabaseDiskStats] PRIMARY KEY CLUSTERED 
(
	[PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

There is also a change to the other table the solution uses, ServersToCheckDiskStats. I added three columns:

  • ServerDisplayName – this is a user-friendly name for the server that can be displayed on reports. Useful if you have servers in other domains that can only be reached by IP address and you want a report to show a more meaningful name.
  • IncludeInCheck – A flag to indicate if this server should be included in the disk gathering routine.
  • Notes – A short field for random notes you might want to include, such as why a server is not included in the routine.

The below script creates an appropriate version of this table.

CREATE TABLE [dbo].[ServersToCheckDiskStats](
	[PK] [INT] IDENTITY(1,1) NOT NULL,
	[ServerName] [VARCHAR](128) NOT NULL,
	[ServerDisplayName] [VARCHAR](128) NOT NULL,
	[PersistLink] [BIT] NOT NULL CONSTRAINT [DF_ServersToCheckDiskStats_PersistLink]  DEFAULT ((0)),
	[SQLServerVersion] [CHAR](4) NOT NULL,
	[TrackTotalDiskSpace] [BIT] NULL CONSTRAINT [DF_ServersToCheckDiskStats_TrackTotalDiskSpace]  DEFAULT ((1)),
	[IncludeInCheck] [BIT] NOT NULL CONSTRAINT [DF_ServersToCheckDiskStats_IncludeInCheck]  DEFAULT ((1)),
	[Notes] [VARCHAR](255) NULL,
 CONSTRAINT [PK_ServersToCheckDiskStats] PRIMARY KEY CLUSTERED 
(
	[PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
 CONSTRAINT [IX_ServersToCheckDiskStats] UNIQUE NONCLUSTERED 
(
	[ServerName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
 CONSTRAINT [IX_ServersToCheckDiskStats1] UNIQUE NONCLUSTERED 
) ON [PRIMARY]

GO

Supported values for the SQLServerVersion field are 2005, 2008 (which includes 2008 R2), 2012, 2014, and 2016.

Here is the latest version of the stored procedure. The major changes since I last published this are as follows:

  • Deletes existing records with same date as today’s date. This allows you to run the script multiple times on the same day without getting duplicate results. (Useful if a connection to a linked server failed for some reason and you need to re-run the script.)
  • Added some output for troubleshooting.
  • Added support for SQL 2012, 2014, and 2016.
  • Removed support for SQL 2000. (Good riddance!)
  • Improved code dealing with persisted linked servers.

In reviewing this code before posting it here, I noticed there is a lot of duplicate code. The command syntax generated is based on the version of SQL Server specified. This was mainly done to support SQL 2000, which had different syntax for some commands from SQL 2005,  SQL 2008, and SQL 2008 R2, which was the latest version at the time this was originally written. When I added SQL 2012 support later and then later still added support for SQL 2014 and 2016, I kept up this practice, even though the syntax for all those versions are the same. Now that I removed SQL 2000 support, it looks kind of silly because the resulting command is always the same. However, I find it useful and it allows me to easily add in some other option in the future that might have different syntax between versions. I like to have identical scripts on all my SQL Servers and this version specific coding helps with that.

Again, please be sure to read my original article on what the script does and how to use it. Thanks!

CREATE PROCEDURE [dbo].[GatherServerDiskUsageData]
AS
-- =============================================
-- Author: Shaun Stuart, shaunjstuart.com
-- Create date: August 24, 2010
-- Description: Procedure to collect disk space usage data
-- =============================================
-- 1/20/11 - Change server linked code to not run if @ServerName is same as @@SERVERNAME
--      (will remove entry for local server in sysservers otherwise and render
--      @@SERVERNAME = null on next restart of SQL Server) - SJS
--
-- 7/21/11 - Added IncludeInCheck flag to server cursor to allow skipping servers
--		   - Added ServerDisplayName field support
--
-- 12/25/12 - Added support for 2012 servers (same code as 2008)
--
-- 3/1/13 - Add step to delete existing records with current date to avoid
--				doubling counts if proc is re-run.
-- 12/13/13 - Added output showing which server we are trying to connect to (for
--				troubleshooting purposes)
-- 7/11/14	SJS	Moved code executing sp_addlinkedsrvlogin into begin/end block
--				adding linked server.
-- 7/24/14	SJS	Print status of @@SERVERNAME for troubleshooting
-- 8/18/15	SJS	Happy birthday!
--				Added support for SQL 2014 and 2016
-- 8/20/15	SJS	Removed support for SQL 2000
--				Added code to log Enterprise / Developer edition-only features (>2005 only)
--				Alphabetized server list for processing
--				Added logic to better handle persisted linked servers. They might not use
--					logins with permissions we need, so this now creates another linked
--					server with a different name that has the permissions we need.

 
DECLARE @ServerName VARCHAR(128);
DECLARE @TempServerName VARCHAR(132); /* Used for servers with persisted links */
DECLARE @ServerDisplayName 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 = 'DiskStatsUser';
SET @LinkedServerLoginpwd = 'password';
 
 /* Delete records for the current day, if any exist. This allows this procedure to
 be re-run if it fails without duplicating counts for the servers that did not fail. */


IF EXISTS ( SELECT  1
            FROM    DatabaseDiskStats
            WHERE   StatDate = CONVERT([VARCHAR](10), GETDATE(), (1)) )
   DELETE   FROM DatabaseDiskStats
   WHERE    StatDate = CONVERT([VARCHAR](10), GETDATE(), (1)); 


/* Get a list of the servers to gather data from and their SQL version */
 
DECLARE ServersCursor CURSOR
FOR
        SELECT  ServerName
               ,ServerDisplayName
               ,PK
               ,PersistLink
               ,SQLServerVersion
               ,TrackTotalDiskSpace
        FROM    ServersToCheckDiskStats
        WHERE   IncludeInCheck = 1
        ORDER BY ServerName;
 
OPEN    ServersCursor;
FETCH NEXT FROM ServersCursor INTO @ServerName,
      @ServerDisplayName, @ServerNamePK, @PersistLink,
      @SQLServerVersion, @TrackTotalDiskSpace;


PRINT '@@SERVERNAME system variable is: '
      + ISNULL(@@servername, 'NULL') + '.';
PRINT 'If this is NULL, procedure will fail. Use sp_addserver ''<LocalServerName>'', local to correct';
PRINT 'Restart of SQL is required for fix. If NULL, something executed sp_dropserver <servername> using';
PRINT 'the name of the server and then the server was restarted.';
SET NOCOUNT ON;
SELECT  '';
SET NOCOUNT OFF;

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 */
            SET NOCOUNT ON;
            PRINT 'Attempting to connect to ' + @ServerName;
            SET NOCOUNT OFF;
            SET @TempServerName = @ServerName; 

            IF EXISTS ( SELECT  *
                        FROM    master.sys.servers
                        WHERE   @ServerName = name )
               AND @PersistLink = 0
               AND @@SERVERNAME <> @ServerName
               BEGIN
                     PRINT 'Dropping existing linked server...';
                     EXEC sp_dropserver
                        @ServerName
                       ,droplogins;
               END;

            IF @PersistLink = 0
               AND @@SERVERNAME <> @ServerName
               BEGIN
                     PRINT 'Adding linked server...';
                     EXEC sp_addlinkedserver
                        @server = @TempServerName;
                     PRINT 'Adding linked server login...';
                     EXEC sp_addlinkedsrvlogin
                        @ServerName
                       ,'false'
                       ,NULL
                       ,@LinkedServerLogin
                       ,@LinkedServerLoginpwd;
 
               END;

            IF @PersistLink = 1	/* Server link is already present, but it might not
											be using a login with permissions we need, so
											make our own linked server using a different name */
               AND @@SERVERNAME <> @ServerName
               BEGIN
                     SET @TempServerName = @ServerName
                         + 'Temp';
                     PRINT 'Adding linked server...';
                     EXEC sp_addlinkedserver
                        @server = @TempServerName
                       ,@srvproduct = ''
                       ,@provider = 'SQLNCLI'
                       ,@datasrc = @ServerName;

                     PRINT 'Adding linked server login...';
                     EXEC sp_addlinkedsrvlogin
                        @TempServerName
                       ,'false'
                       ,NULL
                       ,@LinkedServerLogin
                       ,@LinkedServerLoginpwd;
 
               END;


            SET NOCOUNT ON;
            PRINT 'Connection to ' + @ServerName
                  + ' was successful. Now gathering data...';
            SET NOCOUNT OFF;

/* pull disk usage data for each database file using the SQL version-appropriate system table */
 
            SELECT  @SQLCmd = CASE @SQLServerVersion
                                WHEN '2016'
                                THEN	/* same as 2014 */ 'INSERT    DatabaseDiskStats
                    (ServersToCheckPK,
                     Server_name,
                     Database_name,
                     [Filename],
                     Drive_letter,
                     Size_in_MB)
                SELECT  '
                                     + CONVERT(VARCHAR(6), @ServerNamePK)
                                     + ',' + ''''
                                     + @ServerDisplayName
                                     + ''''
                                     + ',
                        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    [' + @TempServerName
                                     + '].master.sys.master_files AS smf,
                        [' + @TempServerName
                                     + '].master.sys.databases sdbs
                WHERE   smf.database_id = sdbs.database_id
                    AND sdbs.name <> ''tempdb''
                ORDER BY    smf.database_id'
                                WHEN '2014'
                                THEN	/* same as 2012 */ 'INSERT    DatabaseDiskStats
                    (ServersToCheckPK,
                     Server_name,
                     Database_name,
                     [Filename],
                     Drive_letter,
                     Size_in_MB)
                SELECT  '
                                     + CONVERT(VARCHAR(6), @ServerNamePK)
                                     + ',' + ''''
                                     + @ServerDisplayName
                                     + ''''
                                     + ',
                        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    [' + @TempServerName
                                     + '].master.sys.master_files AS smf,
                        [' + @TempServerName
                                     + '].master.sys.databases sdbs
                WHERE   smf.database_id = sdbs.database_id
                    AND sdbs.name <> ''tempdb''
                ORDER BY    smf.database_id'
                                WHEN '2012'
                                THEN	/* same as 2008 */ 'INSERT    DatabaseDiskStats
                    (ServersToCheckPK,
                     Server_name,
                     Database_name,
                     [Filename],
                     Drive_letter,
                     Size_in_MB)
                SELECT  '
                                     + CONVERT(VARCHAR(6), @ServerNamePK)
                                     + ',' + ''''
                                     + @ServerDisplayName
                                     + ''''
                                     + ',
                        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    [' + @TempServerName
                                     + '].master.sys.master_files AS smf,
                        [' + @TempServerName
                                     + '].master.sys.databases sdbs
                WHERE   smf.database_id = sdbs.database_id
                    AND sdbs.name <> ''tempdb''
                ORDER BY    smf.database_id'
                                WHEN '2008'
                                THEN 'INSERT    DatabaseDiskStats
                    (ServersToCheckPK,
                     Server_name,
                     Database_name,
                     [Filename],
                     Drive_letter,
                     Size_in_MB)
                SELECT  '
                                     + CONVERT(VARCHAR(6), @ServerNamePK)
                                     + ',' + ''''
                                     + @ServerDisplayName
                                     + ''''
                                     + ',
                        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    [' + @TempServerName
                                     + '].master.sys.master_files AS smf,
                        [' + @TempServerName
                                     + '].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)
                                     + ',' + ''''
                                     + @ServerDisplayName
                                     + ''''
                                     + ',
                        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    [' + @TempServerName
                                     + '].master.sys.master_files AS smf,
                        [' + @TempServerName
                                     + '].master.sys.databases sdbs
                WHERE   smf.database_id = sdbs.database_id
                    AND sdbs.name <> ''tempdb''
                ORDER BY    smf.database_id'
                                ELSE        /* not a SQL version this procedure handles */ 'INSERT    DatabaseDiskStats
                        (ServersToCheckPK,
                         Server_name,
                         Database_name,
                         [Filename],
                         Drive_letter)
                SELECT  '
                                     + CONVERT(VARCHAR(6), @ServerNamePK)
                                     + ',' + ''''
                                     + @ServerDisplayName
                                     + '''' + ',' + ''''
                                     + 'undefined SQL version in table ServersToCheckDiskStats or version < 2005'
                                     + ''''
                                     + ' 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 tempdb on disk is stored in tempdb.sys.database_files. */
 
            SELECT  @SQLCmd = CASE @SQLServerVersion
                                WHEN '2016'
                                THEN	/* same as 2014 */ 'INSERT    DatabaseDiskStats
                    (ServersToCheckPK,
                     Server_name,
                     Database_name,
                     [Filename],
                     Drive_letter,
                     Size_in_MB)
                SELECT  '
                                     + CONVERT(VARCHAR(6), @ServerNamePK)
                                     + ',' + ''''
                                     + @ServerDisplayName
                                     + ''''
                                     + ',
                        ''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    [' + @TempServerName
                                     + '].tempdb.sys.database_files AS sdf'
                                WHEN '2014'
                                THEN	/* same as 2012 */ 'INSERT    DatabaseDiskStats
                    (ServersToCheckPK,
                     Server_name,
                     Database_name,
                     [Filename],
                     Drive_letter,
                     Size_in_MB)
                SELECT  '
                                     + CONVERT(VARCHAR(6), @ServerNamePK)
                                     + ',' + ''''
                                     + @ServerDisplayName
                                     + ''''
                                     + ',
                        ''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    [' + @TempServerName
                                     + '].tempdb.sys.database_files AS sdf'
                                WHEN '2012'
                                THEN	/* same as 2008 */ 'INSERT    DatabaseDiskStats
                    (ServersToCheckPK,
                     Server_name,
                     Database_name,
                     [Filename],
                     Drive_letter,
                     Size_in_MB)
                SELECT  '
                                     + CONVERT(VARCHAR(6), @ServerNamePK)
                                     + ',' + ''''
                                     + @ServerDisplayName
                                     + ''''
                                     + ',
                        ''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    [' + @TempServerName
                                     + '].tempdb.sys.database_files AS sdf'
                                WHEN '2008'
                                THEN 'INSERT    DatabaseDiskStats
                    (ServersToCheckPK,
                     Server_name,
                     Database_name,
                     [Filename],
                     Drive_letter,
                     Size_in_MB)
                SELECT  '
                                     + CONVERT(VARCHAR(6), @ServerNamePK)
                                     + ',' + ''''
                                     + @ServerDisplayName
                                     + ''''
                                     + ',
                        ''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    [' + @TempServerName
                                     + '].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)
                                     + ',' + ''''
                                     + @ServerDisplayName
                                     + ''''
                                     + ',
                        ''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    [' + @TempServerName
                                     + '].tempdb.sys.database_files AS sdf'
                                ELSE        /* not a SQL version this procedure handles */ 'INSERT    DatabaseDiskStats
                        (ServersToCheckPK,
                         Server_name,
                         Database_name,
                         [Filename],
                         Drive_letter)
                SELECT  '
                                     + CONVERT(VARCHAR(6), @ServerNamePK)
                                     + ',' + ''''
                                     + @ServerDisplayName
                                     + '''' + ',' + ''''
                                     + 'undefined SQL version in table ServersToCheckDiskStats or version < 2005'
                                     + ''''
                                     + ' AS database_name ,'
                                     + '''' + 'ERROR' + ''''
                                     + ',' + '''' + '*'
                                     + ''''
                              END;
 
            EXEC (@SQLCmd);

/* This next bit gets which databases are using Enterprise / Developer edition-only features */
/* This IS ONLY valid post-SQL 2005 */

            IF @SQLServerVersion > '2005'
               BEGIN


		/* ##LinkedServerDBsTable contains a list of the databases on the linked server */
		/* Due to linked server, we cannot use a cursor without using dynamic SQL everywhere */


                     IF OBJECT_ID('tempdb..##LinkedServerDBsTable',
                                  'U') IS NOT NULL
                        BEGIN
                              DROP TABLE ##LinkedServerDBsTable;
                        END;


                     CREATE TABLE ##LinkedServerDBsTable
                            (
                             ServerName VARCHAR(128)
                            ,DBName VARCHAR(128)
                            );


                     SET @SQLCmd = 'INSERT INTO ##LinkedServerDBsTable
		        SELECT  ''' + @ServerDisplayName + ''',
				name
				FROM    [' + @TempServerName
				             + '].master.sys.databases
				WHERE   name NOT IN (''master'', ''tempdb'', ''model'',
                             ''msdb'')
                AND state = 0
				ORDER BY name;';
                     
					 EXEC(@SQLCmd);

                     DECLARE @DBName VARCHAR(128);

		/* ##TempTable1 holds Enterprise / Developer edition options in use
			for current db in cursor - one row per option */

                     IF OBJECT_ID('tempdb..##TempTable1',
                                  'U') IS NOT NULL
                        BEGIN
                              DROP TABLE ##TempTable1;
                        END;


                     CREATE TABLE ##TempTable1
                            (
                             ServerName VARCHAR(128)
                            ,DBName VARCHAR(128)
                            ,feature_name VARCHAR(400)
                            );


		/* ##TempTable2 holds Enterprise / Developer options in use for all dbs on
			server with options concatenated into comma separated string */

                     IF OBJECT_ID('tempdb..##TempTable2',
                                  'U') IS NOT NULL
                        BEGIN
                              DROP TABLE ##TempTable2;
                        END;


                     CREATE TABLE ##TempTable2
                            (
                             ServerName VARCHAR(128)
                            ,DBName VARCHAR(128)
                            ,feature_name VARCHAR(400)
                            );

                     DECLARE @LoopCounter INT;
                     SELECT @LoopCounter = COUNT(*)
                     FROM   ##LinkedServerDBsTable;
                     WHILE @LoopCounter > 0
                           BEGIN
                                 SELECT TOP 1
                                        @DBName = DBName
                                 FROM   ##LinkedServerDBsTable;

                                 SET @SQLCmd = '
			       INSERT  INTO ##TempTable1
					        (ServerName
							,DBName
		                    ,feature_name)
				            SELECT  ''' + @ServerDisplayName
						                     + ''' AS [ServerName]
								   ,''' + @DBName + ''' AS [DBName]
		                           ,feature_name
				            FROM    [' + @TempServerName + '].['
						                     + @DBName
								             + '].sys.dm_db_persisted_sku_features;';
								--PRINT @SQLCmd;
                                 EXEC (@SQLCmd);

                                 INSERT INTO ##TempTable2
                                        (ServerName
                                        ,DBName
                                        ,feature_name)
                                        SELECT DISTINCT
                                            ServerName
                                           ,DBName
                                           ,STUFF(
												(SELECT   N', ' + feature_name
												FROM     ##TempTable1
	                                            FOR
		                                        XML
			                                    PATH('')
				                               ,TYPE)
												.value('text()[1]', 'nvarchar(max)'), 1, 2, N'')
                                        FROM
                                            ##TempTable1;

                                 DELETE FROM ##TempTable1;

                                 DELETE FROM ##LinkedServerDBsTable
                                 WHERE  DBName = @DBName;

                                 SELECT @LoopCounter = COUNT(*)
                                 FROM   ##LinkedServerDBsTable;

                           END;

		/* Now update DatabaseDiskStats table with info we just gathered */

                     UPDATE dds
                     SET    dds.EntDevFeaturesEnabled = ISNULL(tt2.feature_name,'')
                     FROM   ##TempTable2 tt2
                            INNER JOIN DatabaseDiskStats dds ON tt2.ServerName = dds.Server_name
                                            AND tt2.DBName = dds.Database_name
                                            AND dds.StatDate = (CONVERT([VARCHAR](10), GETDATE(), (1)));

					/* Clean up */
                     DROP TABLE ##LinkedServerDBsTable;
                     DROP TABLE ##TempTable1;
                     DROP TABLE ##TempTable2;

               END;
            ELSE /* Version < 2005 */
               BEGIN
                     UPDATE DatabaseDiskStats
                     SET    EntDevFeaturesEnabled = 'Only valid for versions after SQL 2005'
                     WHERE  Server_name = @ServerDisplayName;
               END;


    /* 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;

            SET NOCOUNT ON;
            PRINT 'Data gathered from ' + @ServerName
                  + '. Disconnecting...'; 
            SELECT  '    ';
            SET NOCOUNT OFF;
 
            IF /* @persistLink = 0 AND */ @@SERVERNAME <> @ServerName
               BEGIN
                     EXEC sp_dropserver
                        @TempServerName
                       ,droplogins;
               END;
 

            FETCH NEXT FROM ServersCursor INTO @ServerName,
                  @ServerDisplayName, @ServerNamePK,
                  @PersistLink, @SQLServerVersion,
                  @TrackTotalDiskSpace;
 
      END;
 
CLOSE ServersCursor;
DEALLOCATE ServersCursor;


Share

Leave a Reply

Your email address will not be published. Required fields are marked *

I am a real person and can prove it by doing this math problem: (required) Time limit is exhausted. Please reload CAPTCHA.