SQL Server Trace Flag 2537

Hi Friends,
This is my 24th blog on SQL Server Trace Flag 2537 in the series of One Trace Flag a Day. You can go to all blogs under that series by clicking here.

Function fn_dblog() is provided by microsoft to read the transaction log. Keep in mind that this trace flag can only read active portion of transaction log not In-active portion of transaction log. We can use this function to read the transaction log to find out some specific transaction. Now what happen if we are trying to read transaction information that is not the part of active transaction log? Simple, we will not be able to read that information. In such kind of situation you can use trace flag 2537. By using this trace flag, function fn_dblog() will read both active and inactive portions. Let e show you this practically:

Run the below TSQL code and note down the output to compare with same TSQL code using Trace Flag.

--This code will read only active portion of transaction logs
USE [master]
GO
if DB_ID('Trace2537') > 0
   DROP DATABASE Trace2537
GO
CREATE DATABASE Trace2537
 ON  PRIMARY 
( NAME = N'Trace2537', FILENAME = N'E:\Trace2537.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB )
 LOG ON 
( NAME = N'Trace2537_log', FILENAME = N'E:\Trace2537_log.ldf' , SIZE = 512KB , MAXSIZE = 2048GB , FILEGROWTH = 512KB)
GO
use master
go
ALTER DATABASE Trace2537 SET RECOVERY SIMPLE
GO
use Trace2537
go
create table xtTrace2537
(
TestData char(8000)
)
go
checkpoint
go
Select count(*) from sys.fn_dblog(null,null)
GO
insert into xtTrace2537 values('Welcome to SQLServerGeeks.com')
go 10
Select count(*) from sys.fn_dblog(null,null)
Go

The output is mention below. These numbers of rows are showing total number of rows in active portion of transaction log first for a checkpoint operation only and for second including inserted 10 rows:

Now you can run the below TSQL code to check the use of trace flag 2537.

--This code will read active + inactive portion of transaction logs
USE [master]
GO
if DB_ID('Trace2537') > 0
   DROP DATABASE Trace2537
GO
CREATE DATABASE Trace2537
 ON  PRIMARY 
( NAME = N'Trace2537', FILENAME = N'E:\Trace2537.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512KB )
 LOG ON 
( NAME = N'Trace2537_log', FILENAME = N'E:\Trace2537_log.ldf' , SIZE = 512KB , MAXSIZE = 2048GB , FILEGROWTH = 512KB)
GO
use master
go
ALTER DATABASE Trace2537 SET RECOVERY SIMPLE
GO
DBCC TRACEON(2537,-1)
go
use Trace2537
go
create table xtTrace2537
(
TestData char(8000)
)
go
checkpoint
go
Select count(*) from sys.fn_dblog(null,null)
GO
insert into xtTrace2537 values('Welcome to SQLServerGeeks.com')
go 10
Select count(*) from sys.fn_dblog(null,null)
go
DBCC TRACEOFF(2537,-1)
Go

The output is mention below. Now you can check the number of records returned by fn_dblog() function with use of trace flag 2537. These numbers of records are higher than the previous numbers because these are counts including active and inactive records from transaction log.

PS: Do not use trace flags in production environment without testing it on non production environments and without consulting because everything comes at a cost.

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook