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):
- Modify the entry for your local SQL Server in the ServersToCheckDiskStats table so that the PersistLink flag is set to True.
- 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
Great work, Shaun.
Hi Shaun…this is a great work…I think you should report it to MS. Thanks.
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.
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’
It does. I use it with named instances daily. Be sure to get the latest version though, which can always be found at http://shaunjstuart.com/archive/2011/07/drive-space-monitoring-page/. FYI, I use the same name format as you mentioned, so it should be ok. What is the specific error you are getting?