Troubleshoot and Debug SQL Server backup using DBCC TRACEON 3213 Trace Flags!

How to view what is happening during backup (TRACE)

DBCC TRACEON(3213, 3605, -1)

WARNING: This trace flags should be used under the guidance of Microsoft SQL Server support. They are used in this post for discussion purposes only and may not be supported in future versions.

Enable Trace Flag 3213 to trace SQL Server activity during backup process so that we will come to know which part of backup process is taking more time. I enabled this Trace Flag now, This will log the backup process step-by-step in SQL Server Log because we used the Trace parameter 3605 also.

Now I ran backup for a database and below is the SQL Server error log messages
2008-11-22 07:51:42.11 spid55 BufferPoolLimit: 101 MB
2008-11-22 07:51:42.11 spid55 Backup/Restore buffer configuration parameters
2008-11-22 07:51:42.11 spid55 Buffer count: 5
2008-11-22 07:51:42.11 spid55 Max transfer size: 983040
2008-11-22 07:51:42.11 spid55 Total buffer space: 4 MB
2008-11-22 07:51:42.11 spid55 Buffers per read stream: 5
2008-11-22 07:51:42.11 spid55 Buffers per write stream: 5
2008-11-22 07:51:42.11 spid55 Memory Limit: 101 MB

So from the information logged, we can understand the values choosed by SQL Server for Max Transfer Size and Buffer Count. I'm not sure what else will be logged incase error occurs, Incase I find some time I will check it and post it here...

Links:
1. How does SQL Server Backup and Restore select transfer sizes

2. What is Restore/Backup Doing?