READING THE SQL SERVER LOG FILES USING TSQL

One of the issues that I have always faced is to read the sql error log due to its large size. At times like this, i used to ponder how to read them by not stressing too much or get worried about its size. After few experiences and search in Google i learned how to read them in a proper way by using few tips and tricks.

Solution:

SQL server 2005 offers an undocumented system stored procedure called “sp_readerrorlog” , it takes four parameters.  This store procedure allows us to read the contents of the error log files directly from the query window and also allows us to read them with certain keywords. 

Elaboration of the parameters:

Ø  Value of error log file you want to read: 0 = current, 1 = Archive #1, etc...

Ø  Log file type: 1 or NULL = error log, 2 = SQL Agent 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

Query:

sp_readerrorlog  1,1,'2012'

This returns just 7 rows wherever the value 2012 appears.

We do have one more store procedure by which we can read the error logs. The difference between them are one accepts only four parameters which you'll have seen above.The other accepts seven parameters

Elaboration of the parameters:

Ø  Value OF The Error Log You Want to Read: 0 = Current, 1 = Archive, & etc…

Ø  Log File Type: 1 or NULL = Error Log, 2 = SQL Agent 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.

Query:

EXEC master.dbo.xp_readerrorlog 1, 1, '2013', 'Backup', NULL, NULL, N'desc' EXEC master.dbo.xp_readerrorlog 1, 1, '2013', 'Backup', NULL, NULL, N'asc'

By using the above tips and tricks it becomes much easier for us to read the error logs of SQL server