Have you ever opened the SQL Server Error Log and wondered, “where are all the errors?” Digging through the Error Log to find what you need can be a pain. Sure, you could use several methods to parse through the Error Log to find what you are looking for, but sometimes you want to take a glance at the Error Log using SSMS.
Whether you are new to SQL Server or a seasoned veteran, you will notice odd behavior in the SQL Server Error Log. When a database backup is performed, an entry is put into the SQL Error Log. The SQL Server team decided to log successful backup messages to the Error Log. If you ask most technology professionals, you will find that logging successful events are not really a common occurrence. This behavior causes a bloated Error Log that can make it hard to find what you need quickly.
Luckily, that same SQL Server team built in a solution to this situation. To correct this behavior, Trace Flag 3226 can be enabled.
What is a Trace Flag, you say? Trace Flags are configuration settings that can enable administrators to change the behavior of the database engine. There are 100’s of Trace Flags within SQL Server, some documented very well, while others are undocumented.
There are a couple of ways to enable Trace Flags. Below will outline how to identify what Trace Flags are enabled in SQL Server, then how to enable a Trace Flag as a startup parameter for SQL Server.
Trace Flag Status
First, we will run the command to identify if any Trace Flags are currently enabled.
DBCC TRACESTATUS;
The results of the query show that Trace Flag 1118 is enabled globally on this SQL Server. A globally scoped Trace Flag means that all sessions within SQL Server can take advantage of the functionality the Trace Flag provides. A session-scoped Trace Flag will only work with the user’s current session. Each Trace Flag has a scope identified for proper use.
Enable a Trace Flag
The next step would be to enable Trace Flag 3226 globally to show how it impacts the SQL Server Error Log. This method of enabling the Trace Flag will take effect immediately. However, note that the Trace Flag will no longer be enabled on the next SQL Server Engine restart.
DBCC TRACEON (3226, -1);
After enabling Trace Flag 3226, the SQL Server Error Log will no longer display successful database backup messages.
Note how after enabling Trace Flag 3226, you can see errors and informational messages in the SQL Server Error Log. Another thing to notice is how Trace On and Off commands are logged, so you know when Trace Flags are used, this can help in troubleshooting efforts.
By default, if the SQL Server engine is restarted at this point, the Trace Flag will not be enabled unless we run the command above. To fix this, Trace Flag 3226 can be added as a SQL Server Engine startup parameter. Once this is complete, every time the SQL Server Engine is started, the Trace Flag will be enabled.
Enable Startup Parameter Configuration Manager
Open SQL Server Configuration Manager and click on the SQL Server Services option. Now double-click the SQL Server Instance that you want to enable the Trace Flag on. The first thing you want to do is put -T3226 in the startup parameter box. Then click Add, and that will put the new Trace Flag into the existing parameters section. When you click Apply, you will be prompted to restart the SQL Server Service for the changes to take effect.
Now Trace Flag 3226 will be enabled automatically each time the SQL Server Service is started.
Enable Trace Flag with PowerShell and dbatools
The methods shown above are good if you have one or two SQL Servers to worry about. However, let’s say you have 100 SQL Servers that you are not sure if Trace Flag 3226 is enabled or not in the Startup Parameters. That is where the powerful command
Set-DbaStartupParameter function comes into play.
By running this command against one or one hundred SQL Servers, you will be sure that you have Trace Flag 3226 enabled and silencing those database backup success messages.
Additional resources
The post Where are my SQL Server Errors? appeared first on GarryBargsley.com.