Passionately curious about Data, Databases and Systems Complexity. Data is ubiquitous, the database universe is dichotomous (structured and unstructured), expanding and complex. Find my Database Research at SQLToolkit.co.uk

"The important thing is not to stop questioning. Curiosity has its own reason for existing" Einstein

Tuesday, 2 June 2009

Reading SQL Server log files

There is an undocumented system stored procedure sp_readerrorlog. This script returns all of the rows from the 3rd archived error log.

EXEC sp_readerrorlog 3

sp_readerrolog accepts only 4 parameters, but the extended stored procedure accepts at least 7 parameters.

EXEC sys.xp_readerrorlog @p1,@p2,@p3,@p4

@P1 = Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc…
@P2 = Log file type: 1 or NULL = error log, 2 = SQL Agent log
@P3 = Search string 1: String one you want to search for
@P4 = Search string 2: String two you want to search for to further refine the results

A few examples

This returns a result when 2008 appears.
EXEC sp_readerrorlog 3, 1, '2008'

Rows are returned where the value '2008' and 'backup' exist.
EXEC sp_readerrorlog 3, 1, '2008', 'backup'

Sample log

2009-06-02 16:38:39.12 Server Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

2009-06-02 16:38:39.13 Server (c) 2005 Microsoft Corporation.
2009-06-02 16:38:39.19 Server All rights reserved.
2009-06-02 16:38:39.19 Server Server process ID is 856.
2009-06-02 16:38:39.19 Server Authentication mode is MIXED.
2009-06-02 16:38:39.19 Server Logging SQL Server messages in file 'D:\MSSQL10.SQLMANAGE\MSSQL\Log\ERRORLOG'.....

No comments:

Post a Comment

Note: only a member of this blog may post a comment.