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

HOW TO READ THE SQL SERVER DATABASE TRANSACTION LOG

Query

Have you ever wondered how SQL Server logs track transactions and what information SQL Server captures in the transaction log file?  We have a SQL Server function that can be used to read the transaction log to see what entries are made for database transactions.

Answer

There is an undocumented function called "fn_dblog" which enables you to read data from your transaction log which contains very informative data about things that are happening in your database.

Let us see this in practical

USE [master];

GO

CREATE DATABASE DataRead;

GO

-- Create tables.

USE DataRead;

GO

CREATE TABLE [Locality] (

    [Sr.No] INT IDENTITY,

    [Date] DATETIME DEFAULT GETDATE (),

    [City] CHAR (25) DEFAULT 'Bangalore');