Unable to open SQL ErrorLog (SQL 2005)

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Unable to open SQL ErrorLog (SQL 2005)

16 posts, Page 1 of 2

1 2 »»

Unable to open SQL ErrorLog (SQL 2005)

Rate Topic

Display Mode

Topic Options

Author

Message

Posted Monday, August 10, 2009 10:14 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:07 AM
Points: 385, Visits: 614

Hi,
On one of our SQL2005 Server Standard edition with SP3 (9.0.4220), I am not able to open SQL error log in SSMS and not able to expand SQL Error Log Folder in SSMS\Management\SQL Server Logs tree view.

When I click + sign before SQL Server Logs folder in SSMS, getting an error msg box with error:

Failed to retrieve data for this request.(Microsoft.SqlServer.SmoEnum)

Additional Information:
An exception occurred while executing a Transect-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)

A severe error occurred on the currentcommand. The results, if any, should be discarded
(Microsoft SQL Server)

Application Event log records this:

The description for Event ID ( 17052 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Severity: 16 Error:0, OS: 0 [Microsoft][SQL Native Client]Invalid connection string attribute.

Exec Sp_Enumerrorlogs returns this:

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

SQL Agent log is accessible from SSMS and log shows bunch of below errors:

[382] Logon to server 'DEVSQL05' failed (ConnAttemptCachableOp)
[165] ODBC Error: 0, Invalid connection string attribute [SQLSTATE 01S00]
[252] Failed to retrieve operator 1 from the server

Any help on this issue?

Thanks.

JP

Post #767980

Victor Alvarez-1138365

Posted Monday, August 10, 2009 11:32 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, October 21, 2009 1:18 PM
Points: 20, Visits: 50

Hello,

to work with ERROLOG you can check the following links:

http://sqlpost.blogspot.com/2009/07/sql-server-working-with-errorlog.html
http://sqlpost.blogspot.com/2009/07/sql-server-sql-tips-select-errolog.html

In the other hand, you can check with windows explorer the default path where the errolog files are located:
C:\Program Files\Microsoft SQL Server\MSSQL.#installation\MSSQL\LOG

in my case is
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

you can check there the possible issues, if the files are there and if the issue is the SSMS itself, you can trying resintalling your SSMS tool.

With Regards,

Victor Alvarez
http://sqlpost.blogspot.com

Post #768022

Posted Monday, August 10, 2009 11:44 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:07 AM
Points: 385, Visits: 614

Victor. Thaks for your reply.

I checked error log path assigned to sql and also in registry. They all looks fine. I can open the error log file from windows explorer. Also noticed that SQL error log shows logs related backups/restore but corresponding SQL Agent jobs shows no history for these runs.

I tried to view SQL error log from SSMS from different SQL machines but no success with this particular SQL server. It opens fine for other SQL Servers.

No idea where I should look for.

Thanks.

JP

Post #768028

Shawn Melton

Posted Monday, August 10, 2009 11:54 AM

Right there with Babe

Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:56 PM
Points: 754, Visits: 1,891

Can you view the logs logged onto the SQL server itself as an Administrator or Sysadmin?

--------------------------------------------------------------
Shawn Melton
@wshawnmelton

Post #768039

Posted Monday, August 10, 2009 12:05 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:07 AM
Points: 385, Visits: 614

************
Can you view the logs logged onto the SQL server itself as an Administrator or Sysadmin?

------------
Shawn Melton
Forum Etiquette by Jeff Moden
*************

My account has local Admin/sysadmin rights on SQL Server. I am NOT able to see error logs from remote clients or from local clients on the Server itself.

Thanks.

Post #768051

Jack-294518

Posted Monday, September 14, 2009 10:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 04, 2012 10:43 AM
Points: 3, Visits: 89

I am having the same issue (unable to read the sql ERRORLOG trhough SSMS or sp_readerrorlog).

sp_readerrorlog error message:

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

What I can say with a high level of confidence is that it has nothing to do SSMS. My specific issue is with reading the ERRORLOG on a SQL Server 2005 Express Edition instnace (multiple to be more exact) which is running Windows Vista. The same SSMS client is able to read the ERRORLOG on approximately 70 other SQL Server Instances.

I still do not know what the solution is but am wondering if it may have something to do with firewall \ security \ networking settings or possibly MSDTC.

I will provide feedback when I find a solution - if anyone has additional ideas I would appreciate the help.

Thanks,

Jack

Post #787607

SA-1

Posted Monday, September 14, 2009 1:12 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 02, 2013 7:35 AM
Points: 1,140, Visits: 933

Are you running the services under a domain account? If so make sure the service account has access to the folder.

Post #787706

Jack-294518

Posted Monday, September 14, 2009 2:56 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 04, 2012 10:43 AM
Points: 3, Visits: 89

The SQL Server services actually are configured to run under the Network Service account.

I think you are on to something as I am getting a login failure being logged in Event Log when I try to view the SQL ERRORLOG.

I will switch the SQL services to run under a domain account (which is a member of the local ADMINs group) and see if that resolves the issue.

As a side note, here are the exceprts from the Application event log.

Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. [CLIENT: ]

The description for Event ID 17052 from source MSSQLSERVER cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event:

Severity: 16 Error:18456, OS: 18456 [Microsoft][SQL Native Client][SQL Server]Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

the message resource is present but the message is not found in the string/message table

Post #787766

Jack-294518

Posted Monday, September 14, 2009 4:22 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 04, 2012 10:43 AM
Points: 3, Visits: 89

I changed the MSSQLSERVER service to run under a domain account and that resolved the issue.

Interestingly enough, on other SQL Instances running on Win2K3 - we run the MSSSQLSERVER service as the Network Service built-in account and vieing the ERRORLOG works just fine.

Details

SQL Ver = 2005.090.2047.00
SQL Edition = Express Edition
Windows Ver = 5.2 (3790) (Win2K3)
SQL services ruuning under - Network Service
ERRORLOG viewing = Ok

SQL Ver = 2005.090.2047.00
SQL Edition = Express Edition
Windows Ver = 6.0 (6001) (Vista)
SQL services ruuning under - Network Service
ERRORLOG viewing = Fails

But, the issue that I mentioned here is happening on the Vista machine. So, it must be a Vista setting issue, but I do not know exacly what. I may open a case with Microsoft.

Also, if you have any additional isight, I would appreciate it.

Post #787810

Jeffrey Hunter

Posted Thursday, August 05, 2010 7:47 PM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, July 02, 2011 4:58 PM
Points: 1, Visits: 21

I ran into the same issue. My solution was to add the machine name to the loopback address:

127.0.0.1 localhost vmwindows1

Where vmwindows1 is the node hosting the SQL Server instance.

Post #964808

« Prev Topic | Next Topic »

16 posts, Page 1 of 2

1 2 »»

Permissions