Trace the Errors, Deadlocks from SQL Server LOGS


Many times when you hit the performance down, or deadlocks in your DB, you need to trace what happened ? Depends on setting done in SQL Server to log the errors, you can view the logs as per in your Logs folder

Default path is :

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log

You can query this logs using undocumented store procedure ‘xp_readerrorlog’

This will be really useful since most of us used to import the error log into table then query it.exec

xp_readerrorlog 0, 1,'','','2009-04-10 11:55:00.000','2009-04-20 11:55:00.000','asc'

Usage Info:
This procedure takes 7 parameters

  1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc
  2. Log file type: 1 or NULL = SQLServer Error log, 2 = SQL Server Agent Error log
  3. Search string 1: String one you want to search for
  4. Search string 2: String two you want to search for to further refine the results
  5. Search from start time
  6. Search to end time
  7. Sort order for results: N’asc’ = ascending, N’desc’ = descending

NOTE : Above option will work only for SQL 2005 or later.

In logs you will see statements like

[Database Id = 49 Object Id = 541791025]

instead of actual database name.

To know the actual Database simply fire another query :

SELECT DB_Name(Database Id) as [Database Name]

To know the Object you can execute

SELECT * FROM sys.objects WHERE object_id = [Object Id]

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: