After a long gap Im writing a article. This article is all about suppressing successful backup information into errorlog. Let me explain my scenario, In my working environment we have a production SQL Server with 60+ databases, all the databases are set to full recovery and data loss is accepted only for 15 min. Yeah you got me, we are taking Full backup daily, differential every 4 hours and transaction log backup every 15 min, there is no problem with the backup. We have one thing that is messing our error log, whenever a backup is taken by default SQL Server will write the detail to the errorlog as shown below.
In our case SQL Server is writing the successful backup information for 60+ databases every 15 min to error log. So what happens, error log grows 30~50 MB daily which makes us hard to scan the error log. We have a maintenance job to scan the errorlog for errors, if it grows 30 MB our errorlog monitoring jobs runs for long time. To overcome this we can suppress this backup information writing in error log because we have this information in backupset table on MSDB database. We can achieve this by using the trace flag 3226. Follow the steps below to add this trace flag to sql server startup.
-
Open SQL Server Configuration Manager
-
Go to SQL Server properties and click on Advanced Tab
-
Add Trace flag 3226 as shown below
-
Restart SQL Server service
Thats it you are good to go. From now no backup information will be written to errorlog however you can get the backup details from backupset table on MSDB database. We have done this and saved lot of space and time scanning errorlog.
Applies to :
- SQL Server 2000
- SQL Server 2005
- SQL Server 2008 &
- SQL Server 2008 R2
Leave a Reply