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:

SQL_Server_Trace_Flag_2537

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.

SQL_Server_Trace_Flag_2537_2

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

   

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

6 Comments on “SQL Server Trace Flag 2537”

  1. Hi Sachin,

    DB set to simple recovery, for just showing that the log truncation is happening on checkpoint rather than log backups. After checkpoint log records are going to be inactive and trace flag is still able to read the inactive log records.

  2. Thanks for the reply.
    The database recovery mode should not matter as long as you are issuing manual checkpoints.Your example is kind of confusing and implies that TF 2537 would only work with simple recovery databases.

Leave a Reply

Your email address will not be published.