Unable to view Error logs in SQL Server Management Studio on a cluster

  • Article

While trying to view the SQL Error logs in SQL Server Management Studio or using xp_readerrorlogs it internally executes sp_enumerrorlogs.

And sp_enumerrorlogs – tries to execute the below command:

         serverproperty('ServerName')

- The command was providing the cluster name(CLUS180633) instead of the SQL virtual server name (SQL).

- Further examination of the registry revealed the following:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer\Environment

-This was pointing the windows cluster name instead of the SQL Virtual server name

- SQL Server is using the above registry key and it was not able to locate [CLUS180633 \MSSQLServer].

- As opposed to [SQL\MSSQLServer] to access the error logs and enumerate them – so it was failing.

- Changing the registry key to reflect the correct server name directly – will not work as the Cluster over writes it every time, based on the way the cluster resources are configured.

- Always the SQL server resources should be dependent on the disk. Here Disk itself was depend on cluster network name.

- So, if there are any unnecessary dependencies created - eliminate them.

- Internally, the Network Name resource returns the name of the virtual server. If there are any disks resources which are depended on cluster network name then it returns the name of the windows cluster (Windows Cluster resources and SQL server resources were in same group in this case).

- We set the dependency in the following way:

          IP

          Network Name -> IP

          SQL Server -> Network Name, Disks

          SQL Agent -> SQL Server

          Full Text -> Disks

- Doing this will automatically update the above registry key with proper values and you will be able to access the SQL Error log with sp_enumerrorlog

By:

Venkatraghavan N

SE, Microsoft SQL Server

Reviewed By:

Ouseph Devis T

Tech Lead, Microsoft SQL Server