Trace Flag 3014– This is one of the undocumented Trace flags in SQL Server, which basically gives a detailed information(Well, This might not be useful in most of the cases) regarding File Creation, Padding and much more related Info while you are taking a Backup of your Database.
Do you really need this Info? IMHO, 95% of the cases, i would say NO. I consider this as Just an FYI kind of Information. When i try to troubleshoot Backup/Restore Issues, I rather enable Trace Flags 3004 and 3014 along with 3605(to enable logging of all the entries in our Error Log) instead of Just enabling Single Flag(Make sure you disable these Flags once you are done with diagnostics).
Let’s See what exactly get’s logged when i Enable these Trace Flags and Take a Full Database Backup.
Tadaaaaaaaaaa!
2011-09-15 22:00:40.470 spid54 BackupStream(0): Starting MSDA of size 384 extents
2011-09-15 22:00:40.470 spid54 Started file C:\Program Files\Microsoft SQL Server\MSSQL10_50.PROD\MSSQL\DATA\3014_demo.mdf
2011-09-15 22:00:40.780 spid54 Completed file C:\Program Files\Microsoft SQL Server\MSSQL10_50.PROD\MSSQL\DATA\3014_demo.mdf
2011-09-15 22:00:40.790 spid54 Padding MSDA with 327680 bytes
2011-09-15 22:00:40.790 spid54 BackupStream(0): Total MSDA: 384 extents
2011-09-15 22:00:40.860 Backup Database backed up. Database: 3014_demo, creation date(time): 2011/09/15(21:46:19), pages dumped: 3100, first LSN: 163:1808:37, last LSN: 163:1824:1, number of dump devices: 1, device information: (FILE=2, TYPE=DISK: {‘D:\Backs\3014_full.bak’}).
So..Basically you can see exact times while starting backup stream , how many Extents being affected, what file(s) etc…Again This is Just an FYI information in most of the cases while you are dealing with Backups, where as the Trace Flag 3004(see here) is Extremely Useful while investigating DB Restore Issues. YMMV!
MSSQLTREK 