CODEWITHSUNDEEP
sqlsql-serversql-server-2008database-administration
Anshul Dubey
Anshul Dubey

Reputation: 378

Reading SQL SERVER Log file

My SQL Server 2008 R2 production server suffered an outage and went completely down. We have to restart the VM in order to bring back it into play. There are multiple jobs running on that server and I doubt it was due to the concurrency issue, that the server went down. However, in order to know the root cause, I am using the below function on my server: 

         select [Current LSN],
         [Operation],
         [Transaction Name],
         [Transaction ID],
         [Transaction SID],
         [SPID],
         [Begin Time]
         FROM   ::fn_dblog(null,null)

However, it is showing me the transaction that happened after the restart. Could you guys suggest any method to know the root cause? Thanks in advance.

Upvotes: 0

Views: 162

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294447

You probably want to look into the ERRORLOG, not the log.

The log is the wrong place for many reasons:

  • there is one log for each database. So you cannot look into the log but into one log.
  • the log is not meant to be read by humans
  • the log does not capture error information

On the other hand the ERRORLOG is the right place for several reasons:

  • there is only one ERRORLOG for the entire server
  • the ERRORLOG is text intended for human reading
  • errors and anomalies are likely traced in the ERRORLOG

There are articles about Viewing the SQL Server Error Log, but my recommendation is to simply open the errorlog in Notepad and read. Keep in mind that the errorlog is cycled so at the last server restart the old errorlog was renamed ERRORLOG.1 and a new ERRORLOG was opened. If multiple restarts occured then the relevant errorlog may be even further back, like ERRORLOG.2, ERRORLOG.3 etc. You need to identify the right one based on time. Every entry in the errorlog is timestamped. Look for entries near the time of the event.

Most likely the cause of the crash is recorded at the end of the ERORRLOG.1

Upvotes: 3

Related Questions