As a DBA you regularly schedule backups you might have regular full backups, incremental backups and hopefully regular backups. Over time you might realize that your error log is not longer filled with errors, but instead a majority of your error log is filled with backup messages. “Backup Database successfully … “, “Database backed up…”, “Log was backed up”. This makes it a bit difficult to find the actual errors in this flood of backup messages. Your error log is no longer an error log, but it looks more like a backup log.
Transcription:
Steve Stedman 0:09
Welcome to today’s training video. I’m Steve Stedman. I’m the founder of Stedman Solutions. Today we’re going to talk about one of the common items reported by the sequel daily checkup. The warning message that you get from the SQL daily monitoring that we’re going to talk about today is the logs being flooded with backup messages. By default, the way SQL Server set up, every time a backup is run, whether it’s a log backup, or differential backup, or a full backup, it writes an entry into the SQL Server log telling that there is was a backup that occurred. This is kind of one of my personal Peeves, because what it does, if you have a system that’s being backed up regularly, or lots of databases and that you have regular or frequent log backups, is it fills up this log file with nothing but warnings, and even warnings, nothing, it just fills it up with statements that say your database has been backed up. And that makes it so that if if you’ve got a log file that has 150,000 rows in there that says everything is groovy, and that your database was backed up, it makes it really hard to find that one or two rows in that 150,000 rows that actually say that there’s some kind of problem that occurred. So what we recommend doing is turning on trace flag 3226. And what SQL Server trace flags are is these are kind of startup parameters are a way of saying that run SQL Server in a different mode from the default. And what trace flag 3226 does is it says just don’t write these things to the log file. So the way that we’re going to do this is we’re going to go to the SQL server configuration manager, look at the configuration for the currently running SQL Server instance, this happens to be running SQL Server 2019. We’re gonna look at the startup parameters. And we’re going to put in a parameter here. And it’s going to be dash t 3226. And you’re going to add that parameter. And what that does is it says next time SQL Server restarts that it will run and not write those all those backup messages to the error log, and you will only see errors, or you’ll see errors and less messages in there. The thing you want to do is it says this here, any changes made will be saved. However, they won’t take place until the cert service has been stopped and restarted. Now what some people may do is you may say, Okay, I’m not going to restart the SQL Server at this point, the next time it restarts that effect, that’ll go into effect. But the problem in doing that is if you have a typo on that, and you then go and attempt to restart the SQL Server, that typo may prevent the SQL Server from starting up correctly. So what we recommend doing is that when you make the change, that you restart SQL Server, and that may mean that you have to make the change after hours sometime during a maintenance window. But you restart the SQL Server right away so that if it fails to start, you know, it’s probably because of a typo and something you did in there, rather than if the next restart is in three, three or four weeks, and you completely forget that you made this change. So once that’s been done, your you will no longer be getting these database backed up and backup databases, messages written into the SQL Server log. Well, one of the things that comes to question is well, how do we know about backups? If that’s the case? Well, the way we can take a look at that. And I have a sample script here that will make available on the blog. If we go and actually run a backup of one of the databases here, we can go and look using a query like this that will make available on my blog, and it will go and instead of looking at the log, it goes and looks at the tables and msdb that track backup history, like backup media, family and backup set. And we can look at that and see that here is a history of all of those backups that occurred without having to go to the log to get that information. I’ve recommend trace flag 3226 on every SQL Server I think that I’ve ever worked on. Here’s some more information that may help you out my blog where to get more information. SteveStedman.com or StedmanSolutions.com And the last link will be the Stedman Solutions sequel, daily checkup monthly subscription. We have lots of people using the sequel daily checkup today. If it’s something that you’d like to use, let me know and we’ll get it set up for you. Have a great day.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!