“Drive Space Monitoring Gets An Update” Bug Fix

A couple of months ago, I created a routine to gather disk drive space statistics from several SQL Servers. Today, I discovered a bug in that program that could cause the @@SERVERNAME variable on the server that runs the job to get reset to NULL after a restart of SQL Server.

The problem comes from what may or may not be a bug in SQL Server – I haven’t decided if it is a true bug or not. Please leave a comment and let me know your opinion on this.

The @@SERVERNAME variable is populated when SQL Server starts with the value in the name field of the sys.servers table with a server_id equal to zero. In other words, on startup, it does the equivalent of this:

SELECT @@SERVERNAME = name FROM sys.servers WHERE server_id = 0

My routine gets a list of SQL Servers to check from a table and then cycles through that list, creating a linked server entry to the server, gathering the data, and then, optionally, dropping the linked server entry (based on a flag in the table). The problem arises if you are gathering data for the local SQL Server the job is running on AND you did NOT set the flag for the local server to have a persisted link. (And why would you – technically, you don’t need a linked server entry to the local server!) The system stored procedure to drop the linked server, sp_dropserver, will, if you supply the local SQL Server name (as my code did), delete the entry from sys.servers for the local server (i.e. where server_id = 0). When the server restarts, the above query returns no records and @@SERVERNAME gets assigned a value of NULL. If you look at the Books Online entry for sp_dropserver, it makes no mention of this. If fact, it only talks about linked and remote servers, so you may not even know information about the local server is stored there. I feel this is a potentially troublesome issue that should be documented. I’ve got scripts that make use of the @@SERVERNAME system variable and run into problems when it has a value of NULL.

You can recreate the local server entry by running the following command:

sp_addserver '<LocalServerName>', local

You can verify the behavior of this by running the following commands:

-- Verify local entry is there
select * from sys.servers where server_id=0

-- Now delete it
sp_dropserver '<LocalServerName>', droplogins
-- Now the local entry will be gone
select * from sys.servers where server_id=0

-- Now add it back
sp_addserver '<LocalServerName>', local
-- Here it is
select * from sys.servers where server_id=0

If you want to test how this affects the @@SERVERNAME variable, on a test system, stop and restart the SQL Server service after each of those commands and see what the @@SERVERNAME variable contains.

As for my routine, you can fix it by either of these methods (I recommend #2):

  1. Modify the entry for your local SQL Server in the ServersToCheckDiskStats table so that the PersistLink flag is set to True.
  2. Replace the GatherServerDiskUsageData stored procedure with the modified code below.

If you were running this code already, I highly suggest you check the value of @@SERVERNAME on the server it runs on, as well as the sys.servers table. If you do not have an entry where server_id = 0, run the sp_addserver line above. If @@SERVERNAME returns NULL, you’ll need to restart SQL Server to get it repopulated with the correct value. If it currently contains the correct value, no restart is needed, even if you did not have a sys.servers entry where server_id = 0 previously.

So what do you think? Should I file a bug report for the behavior of sp_dropserver? Leave a comment with your opinion.

-- =============================================
-- 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

CREATE 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 = 'DiskStatsUser'
SET	@LinkedServerLoginpwd = 'password'

/* 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
		AND @@SERVERNAME <> @ServerName

	BEGIN
		EXEC sp_dropserver @ServerName, droplogins
	END

	IF @PersistLink = 0 AND @@SERVERNAME <> @ServerName	/*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 AND @@SERVERNAME <> @ServerName
	BEGIN
		EXEC sp_dropserver @ServerName, droplogins
	END

	FETCH NEXT FROM ServersCursor INTO @ServerName, @ServerNamePK, @PersistLink, @SQLServerVersion, @TrackTotalDiskSpace

END

CLOSE ServersCursor
DEALLOCATE ServersCursor

6 thoughts on ““Drive Space Monitoring Gets An Update” Bug Fix

  1. Thanks. At the time, I did look into it, but I think I discovered this was by design. Can’t really remember why. I think it had to do with renaming a server. This was the method used to update the system tables with the new server’s name.

  2. Hi. Does this monitoring work for named instances too? I have issues getting it working when I have a named instance in ServerName of ServersToCheckDiskStats. ServerName used is ‘myserver\myinstancename’

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.

This site uses Akismet to reduce spam. Learn how your comment data is processed.