An issue with SQL Server that you might not be aware of is that every time a backup is sucessfully completed on an instance a message is recorded to the SQL Server Event Log

This happens for all backup types and is default behaviour. If for example you take frequent log backups this can become problomatic becuase the log will be full of messages that are not really all that useful for the management or diagnosis of the instance itself. With Trace Flag 3226 you can change this behaviour.

This trace flag has been in production since SQL Server version 2000, so it has a long standing in the code base.

Microsoft have this in the documentation for this flag;

By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If you create very frequent log backups, these success messages accumulate quickly, resulting in huge error logs in which finding other messages is problematic.

With this trace flag, you can suppress these log entries. This is useful if you are running frequent log backups and if none of your scripts depend on those entries.

Enable The Change Immediately

You can enable the change immediately without forcing an engine restart by running the following command in SQL Server Management Studio

DBCC TRACEON (3226, -1)

This will change the behaviour with immediate effect but if the instance restarts the default behaviour will be enforced.

Enfore the change

Load up SQL Server Configuration Manager - if this is not in your start menu, below is the locations of where you can find it.

Once there, right click on the SQL Server Service, Select Properties then select Startup Parameters

Enter the following trace flag into the statrup parameter box

-T3226

once you have typed it into the box provided press add

Now press apply, you will get a warning saying the change won’t take affect until the server is restarted but if you ran the DBCC command from above this can wait until a more conveniant time.

Check The Change

Now that the change is implemented, lets take a backup and see what happens

As you can see, SQL Server is no longer creating entries in the event log for sucessful backups.

Watch out!!!

If you have scripts or monitoring tools that rely on them entries being present in the log, you might not want to do this but if your scripts or monitoring soloutions make no use of them and you have high backup frequency or just don’t want to see sucessful backup messages in your logs this is definatley a recommended change.

Make It Standard

It is good practice to enforce this startup parameter on all new instances of SQL Server - if you have a checklist, add this to it it makes diagnosing your instance all that much easier.