SQL Server Trace Flag 3213

Hi Friends,

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

This is one of the trace flag which can provide you the internal information about backup and restore operations. In simple words we can say that, by using this trace flag we can check the total amount of memory or buffers used for backup or restore operation. Let me show you practically for backup operation:

use master
go
DBCC TRACEON(3213,3605,-1)
go
exec sp_cycle_errorlog
go
--I already have the statsDemo2014 DB on my Instance
--You can replace the name of DB and Drive Letter as per your environment.
Backup database StatsDemo2014 to disk='E:\StatsDemo2014_full.bak'
go
exec xp_readerrorlog
go

Where

BUFFERCOUNT: Specifies the total number of I/O buffers to be used for the backup operation. You can specify any positive integer. however, large numbers of buffers might cause out of memory errors because of inadequate virtual address space in the Sqlservr.exe process.The total space used by the buffers is determined by buffercount * maxtransfersize.

MAXTRANSFERSIZE: Specifies the largest unit of transfer in bytes to be used between SQL Server and the backup media. The possible values are multiples of 65536 bytes (64 KB) ranging up to 4194304 bytes (4 MB).

Below is the figure which can show you the complete overview of backup operation.

Finally do not forget to turn off the trace flags.

use master
go
DBCC TRACEOFF(3213,3605,-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