Thursday, March 1, 2007

SQL Server: How to turn off event logs for successful backups

I recently noticed that I get an event log message on my SQL Server every time a successful backup is done:


Event ID: 17055
Information
18265 :
Log backed up: Database: MyDatabase, creation date(time): 2006/01/11(10:50:49),
first LSN: 1212:97:1, last LSN: 1212:97:1, number of dump devices: 1,
device information: (FILE=103, TYPE=DISK: {'MyDatabase02'}).

This is annoying, because I run transaction log backups about every ten minutes. This generates so many messages that my event logs recycle and I get no important messages.

The solution for this is to set the TRACEFLAG 3226. TRACEFLAGs are a mechanism in SQL Server 2000 that allow you to turn on and off behavior. Setting TRACEFLAG 3226 turns off logging for backup events. You can still set up event logging in your backup jobs, but have it only write to the event log if the backup fails.

There are two ways to set this traceflag. The following works when you can't restart the SQL Server service:

 DBCC TRACEON (3226, -1) 

This turns on traceflag 3226 globally until the next time the SQL Server restarts.

If you have the option of restarting the SQL Server service, you can set this as a startup parameter. In Enterprise Manager, right-click and choose "Properties", and on the "General" tab, click the "Startup Parameters" button. Type "/T3226" in the "Parameter" box, then click "Add". Click OK. Windows will ask if you want to restart your service, and you can either choose yes, or choose "no" and restart the service yourself later. This will keep the TRACEFLAG 3226 set permanently, and you won't get Event Log messages about successful backups anymore.

No comments:

Post a Comment

I moderate comments blog posts over 14 days old. This keeps a lot of spam away. I generally am all right about moderating. Thanks for understanding.