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

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

  • Comments 3

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

Leave a Comment
  • Please add 8 and 5 and type the answer here:
  • Post
  • SQL Sever 2012 Enterprise (HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLServer\Environment does not exist)

    sp_enumerrorlogs - works as it should

    sp_readerrorlog - works as it should

    Still unable to view Error Logs via SSMS???

  • Found problem:

    HKEY_LOCAL_MACHINE\SYSTEM\Software\Microsoft\SQLServer\InstanceName\SQLServerAgent\ErrorLogFile (Log path missing)

    Added Error Log path and SQLAGENT.OUT file to above registry key resolved the problem.

  • CORRECTION:

    Correcting "Found problem:" entry with the following:

    At the Registry location, HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<YourInstanceName>\SQLServerAgent\, the <ErrorLogFile.xxx> is missing.

    Simply add (1) the error log path, and (2) the SQLAGENT.OUT file to the above registry key to resolve the problem.

Page 1 of 1 (3 items)