5

Just upgraded to SQL Server 2016 from SQL Server 2014 SP1 CU4.

This is a clustered instance

I am no longer able to use xp_readerrorlog and xp_enumerrorlog

Msg 22004, Level 16, State 1, Line 10
Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 10
Error log location not found.

Found this link: Unable to open SQL Server Logs

  • I can open the error logs manually via file explorer
  • The startup parameters for SQL Server Service indicate the right location for the error logs.
  • Running repair on the instance didn't resolve the problem
  • Disabling Shared Memory for the instance didn't resolve the problem.
  • rebooting after all these issues and repairs didn't help
  • No aliases are being used to connect to this instance
  • 127.0.0.1 is in the hosts file

Basically they are saying to repair the instance.

Any other options?

Improve this question
4
  • I believe those XPs get the error log location from the registry, so it might just need a registry fix. Have you validated the location of the error log (you can find it manually in File Explorer)? What happens when you open the error log manually through Object Explorer? Have you checked the event log for clues, like the error message suggests?
    – Aaron Bertrand
    Jun 6, 2016 at 10:56
  • Can you see if shared memory protocol is disabled or not. Please disablle it and check if you can run the query. This Blog has more information on what could be possible cause.
    – Shanky
    Jun 6, 2016 at 11:28
  • Has the server been renamed (especially after 2014 was installed)? What exactly is in sys.servers? Have you tried dropping the local server and re-creating it (sp_dropserver, sp_addserver local)?
    – Aaron Bertrand
    Jun 6, 2016 at 13:30
  • Hello @AaronBertrand, no the server wasn't renamed. We are going to create a clustered test environment and continue our research there. Its time also to open a support case with Microsoft
    – Craig Efrein
    Jun 6, 2016 at 14:26

3 Answers 3

Reset to default
3

Windows patching and majid's solution is what worked for us too. Just adding my 2c that may help others. We have a cluster with 2008, 2014 SP2 and 2016 CU2. After upgrading an instance to 2016, we had the following errors:

  1. 2014 and 2016: syspolicy_purge_history job failed on step 3
  2. 2016: SQL agent would start then stop
  3. 2016: Couldn't view SQL error logs

Windows patching, which primarily applied .net security patches fixed the syspolicy_purge_history job in 2014. The new ODBC driver fixed remaining issues in 2016.

Note: installing 2016 SP1 before installing this driver (https://www.microsoft.com/en-us/download/details.aspx?id=53339), will install a newer ODBC driver, that appears to have the same issues when running on a cluster. We attempted this on a test environment, but had to uninstall SP1 and the new ODBC 13.1 driver, then install CU2 and the other ODBC 13.1 driver. Haven't attempted to install SP1 again yet

Improve this answer
2

We also have some 2016 instances besides 2008 all in a cluster environment. I tried every possible solution on the net without success.

I found out SQL Server tries to connect to an instance via ODBC driver ver.13. Release date is well behind of RTM version of SQL Server 2016.

Just download and install the latest ODBC driver for SQL Server (ver 13.1) from Microsoft and the issue is solved.

No need to disable shared memory, aliases, hosts file etc.

Improve this answer
0
0

For later versions of SQL Server you may need to use double quotes or you might get this error.

Try the example below instead:

EXEC master.dbo.xp_readerrorlog 0, 1, "backup", "failed", "2017-01-02", "2017-02-02", "desc"
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.