Trace Flags in SQL Server related to Backup and Restore


Trace Flags in SQL Server are used to turn ON or OFF particular behavior. TraceFlags are used for variety of purposes like Logging extended information in to SQL Server errorlog, Troubleshooting or improving SQL Server Performance, Enabling certain features or functionality, etc.

Below are the SQL Server Trace Flags related to Backup or Restore operations in SQL Server.

3001 – Stops sending backup entries into MSDB.

3004 – Write extended information about backup/restore to Errorlog

3014 – Write extended information about backup/restore to Errorlog

3023 – Automatically enable CHECKSUM for BACKUP command

3042 – Bypasses the default backup compression pre-allocation algorithm to allow the backup file to grow only as needed to reach its final size

3111 – Cause LogMgr::ValidateBackedupBlock to be skipped during backup and restore operations.

3205 – If a tape drive supports hardware compression, either the DUMP or BACKUP statement uses it. With this trace flag, you can disable hardware compression for tape drivers. This is useful when you want to exchange tapes with other sites or tape drives that do not support compression. Scope: global / session

3213 – Display backup/restore configuration parameters. Trace SQL Server activity during backup process so that we will come to know which part of backup process is taking more time.

3226 – Suppress successful backup messages in the logs

3117 – SQL Server 2005 tries to restore the log files and the data files in a single step which some third-party snapshot backup utilities do not support.
Turing on 3117 does things the SQL 8 way multiple-step restore process. KB 915385
Restore the file or the file group from the full database backup. The database remains in a restoring state.
Restore the transaction log or logs from the log backup chain.

3231 – In SQL Server 2005 TF 3231 Will turn the NO_LOG and TRUNCATE_ONLY options into no-ops in FULL/BULK_LOGGED recovery mode, and will clear the log in SIMPLE recovery mode. When set, BACKUP LOG with TRUNCATE_ONLY and BACKUP LOG with NO_LOG do not allow a log backup to run if the database’s recovery model is FULL or BULK_LOGGED.

3505 – Disables automatic checkpoints. May increase recovery time and can prevent log space reuse until the next checkpoint is issued. Make sure to issue manual checkpoints on all read/write databases at appropriate time intervals. Note does not prevent the internal checkpoints that are issued by certain commands, such as BACKUP.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.


Leave a Reply

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