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

trace_flag_3213_1

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.

Full_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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

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 →

Leave a Reply

Your email address will not be published. Required fields are marked *