Stop Successful Backup Messages in Error Log File using Trace 3226.

The SQL Server error log captures information about what is occurring on our database server. It logs all the failures/errors that have occurred on our server. This error log file store information since the last start of SQL Server or since the last time we ran sp_cycle_errorlog(Closes the current error log file and cycles the error log extension numbers just like a server restart).

When we take backup of database in Sql Server, By default the successful Backup messages are get logged into SQL server error log file which can also be useful but if we have a lot of databases on our SQL server instance with frequent backups then we'll surely want to suppress all successful backup messages.

From SQL Server 2008, Microsoft converted an undocumented command (DBCC Trace(3226)) to documented category through which we can turn off the successful backup message.

Below is new trace flag is 3226 with its description which I have taken from Microsoft BOL:

Now lets create a sample database and then we will see how to stop successful backup messages from Error Log file:

Use Master
Go
IF NOT EXISTS ( SELECT name FROM sys.databases WHERE name = 'NewTraceDB' )
Create Database NewTraceDB
Go

Now lets take the backup of database:

Use NewTraceDB
Go
Backup database NewTraceDB to disk ='C:\vimal\NewTraceDB1.bak'
Go

Now open error log file. We can see that the backup messaged has been logged into this file.

Now lets turn on this New trace flag 3226. We are going to turn ON this trace only for this session. 
If we want to turn ON globally then we can use "-1" as DBCC TraceOn(3226,-1);

Now execute:

DBCC TRACEON (3226)
Go

Let take the backup again:

Use NewTraceDB
Go
Backup database NewTraceDB to disk ='C:\vimal\NewTraceDB2.bak'
Go

Now open open error log file once again and check for backup messages. No successful backup message are there after enabling trace 3226.

So, by using Trace Flag 3226 we can stop Stop Successful Backup Messages in Error Log File. Now lets off the trace flag.

DBCC TraceOff(3226)
Go

Now lets Clean the test environment

Use master
Go
Drop database NewTraceDB
Go

Trace flag 3226 works fine with SQL 2005/2008/2012/2014.

Thanks!!!!