Hey, @sod
Here you go 
I think it has something to do with the date, the markdown file name is also 2022-05-09-trace-flag-3226.md
---
title: SQL Server Trace Flag 3226
date: 2022-05-09T09:00:00.000+01:00
author: Rich
layout: post
categories:
- sqlserver
draft: false
tags:
- SQLServer
- DatabaseAdministration
- Advanced
---
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.
| Version | Path |
|--------------|-----------|
| SQL Server 2019 | C:\Windows\SysWOW64\SQLServerManager15.msc |
| SQL Server 2017 | C:\Windows\SysWOW64\SQLServerManager14.msc |
| SQL Server 2016 | C:\Windows\SysWOW64\SQLServerManager13.msc |
| SQL Server 2014 (12.x) | C:\Windows\SysWOW64\SQLServerManager12.msc |
| SQL Server 2012 (11.x) | C:\Windows\SysWOW64\SQLServerManager11.msc |
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.