sql server trace flag 3226

Hi Friends,

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

In my working experience I have seen many servers where we have more than 20 databases on a single SQL instance. For all databases recovery model is FULL. SQL Agent job are there to take full, differential and transaction log backups of all those databases. We all know that whenever we have taken backup for a database then a backup completion message will logged in sql server error log. That means message will be logged for each full, Differential and log backups.

Consider a scenario: we have an instance that is having 50 databases and log backup job will run after every 15 minutes. That means 50 log backup messages will be logged after every 15 minutes in the error log. We can say 200 messages in an hour and 4800 messages in a day. Now we can say error log will be flooded by such kind of messages.

Now the question is: Can we get rid of such kind of messages to simplify our error log? Of course yes, I have written this blog only due to ‘Yes’. We can use trace flag 3226 to get rid of those messages.

Let me show you that practically:

exec sp_cycle_errorlog
go
backup database [CheckpointTest3]
to disk='E:\CheckpointTest3_Native_Full_29092014.bak'
go
backup database [CheckpointTest3]
to disk='E:\CheckpointTest3_Native_Diff_29092014.dif'
with differential
go
backup Log [CheckpointTest3]
to disk='E:\CheckpointTest3_Native_Log_29092014.trn'
go
exec sp_readerrorlog
go

Now I will show you the same by enabling the trace flag 3226.

exec sp_cycle_errorlog
go
DBCC TRACEON(3226,-1)
go
backup database [CheckpointTest3]
to disk='E:\CheckpointTest3_Native_Full_29092014.bak'
go
backup database [CheckpointTest3]
to disk='E:\CheckpointTest3_Native_Diff_29092014.dif'
with differential
go
backup Log [CheckpointTest3]
to disk='E:\CheckpointTest3_Native_Log_29092014.trn'
go
exec sp_readerrorlog
go
DBCC TRACEOFF(3226,-1)
go

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