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
- Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc
- Log file type: 1 or NULL = SQLServer Error log, 2 = SQL Server Agent Error log
- Search string 1: String one you want to search for
- Search string 2: String two you want to search for to further refine the results
- Search from start time
- Search to end time
- 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]
