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;