SQL server error log contain all information about how a SQL server is running, what is happening and occurring for your databases, it is normally the first place you look at when you have any issue of the database. Keeping it small and as useful as it can be helps a lot when it comes to troubleshooting, since you don’t have to scan through hundreds of lines of useless information and locate the issue you might be having. Keeping it recycle on a regular interval is a good practice and should be set as default for easier maintenance.
If you have many databases within a single instance and/or have frequent backup mean that you will have all the successful backup entries written to SQL error log and makes the file grow large (and fast). If you do not have any scripts or monitoring that requires the successful backup entry in error log, it would be recommended to turn on trace flag 3226 to suppress all successful backups entries. It will, however, still write out the error entry if backup is unsuccessful, and all successful entry are still logged in msdb database.
How to implement:
To manually turn on this trace flag globally, you can use the below code:
DBCC TRACEON (3226, -1)
To turn this off, you can simply execute:
DBCC TRACEOFF (3226)
As this is a manual setup, it will not retain after service restart, to keep this setting effective after service restart, you should turn it on via adding startup parameters “-T 3226” under SQL server configuration manager:
 |
| Right click on the property of SQL server process |
 |
| Add "-T 3226" in "Startup Parameters" tab |
Putting it to the test:
Let try to run this and see what happens, the way we test will be using the manual setup method mentioned above.
BACKUP DATABASE DBTest TO DISK = 'G:\MSSQL\Backup\DBTest.bak'
GO
DBCC TRACEON (3226, -1)
BACKUP DATABASE DBTest TO DISK = 'G:\MSSQL\Backup\DBTest.bak'
GO
DBCC TRACEOFF (3226, -1)
BACKUP DATABASE DBTest TO DISK = 'G:\MSSQL\Backup\DBTest.bak'
GO
--Purposely make the backup fail
DBCC TRACEON (3226, -1)
BACKUP DATABASE DBTest TO DISK = 'R:\MSSQL\Backup\DBTest.bak'
GO
DBCC TRACEOFF (3226, -1)
From the code above, we will firstly backup the test database, enable the trace flag and perform the backup again, turn the trace flag off and perform the backup the third time. After its done, we will turn on the trace flag and purposely perform a failure backup. What we are expecting is that we should see the backup entry from the first try, nothing from the second and an entry for the third. The reason for the last part is to ensure that even if we have the trace flag on, we will still get failure backup entry in error log. Let check the result:
The result does seem to match what we are expecting. Which can conclude is that if you are not depending on the successful backup entry in SQL error log, you can enable this trace flag to minimize the number of entries in it.
