How to Eliminate all successful backups messages in SQL server error log

We have got a one request in my organization To Eliminate the all successful backup's messages only from SQL server error log,use below trace flag.
-T3226

Normally Sql server error log contain all the information about how the sql server is running, what is happening and occurring on your database, its 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.

Manually How to implement:

To manually turn on this trace flag globally, you can use the below code:

DBCC TRACEON (3226, -1)

To turn this trace off, you can just below query execute:

DBCC TRACEOFF (3226)

To see the what traces are enable globally use Trace status command:

DBCC TRACESTATUS(-1)

As above setup is a manual setup, it will not effect after service restart, to keep this setting effective after restart please use through startup parameter option.

Globally how to enable trace flag:

Step-1: Open sql server configuration manager through run command or startup programs

Step-2: After sql server configuration manager open select the sql server service in left pane

Step-3: In right pane select and right on MSSQLSERVER service

Step-4: click on ok properties sql server process

Step-5: Go to the advanced startup parameter tab

Step-6: Enter and add the below trace flag

-T 3226

Step-7: Apply and Click on OK, ok

Step-8: Restart the sql services.

Step-9: After restart connect to instance and open new query analyzer run below command to verify the trace flag whether enable or not.

DBCC TRACESTATUS(-1)

Or

use master

go

exec xp_readerrorlog 0, 1, 'lock'

Screen shots for above steps:

Lab test:

Let try to run this and see what happens, we are testing manual setup method mentioned above.

BACKUP DATABASE DBA_test TO DISK = 'D:\DBA_test.bak'

GO

DBCC TRACEON (3226, -1)

BACKUP DATABASE DBA_test TO DISK = 'D:\DBA_test.bak'

GO

DBCC TRACEOFF (3226, -1)

BACKUP DATABASE DBA_test TO DISK = 'D:\DBA_test.bak'

GO

--wanted make the backup fail

DBCC TRACEON (3226, -1)

BACKUP DATABASE DBA_test TO DISK = 'D:\MSSQL\DBA_test.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 wanted 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 above result in screen shot match's what we are expecting. we 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.