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 . Microsoft Data Platform MVP

"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.