Friday, December 31, 2010

Stop successfull backup loging messages in SQL Server Error Log?

Whenever backup peformed on SQL Server, it records the backup entry in the SQL Server. Because of that error log file grows and sometimes we are missing important information from there.

For example,








use master
backup database jshah to disk = 'c:\jshah.bak'



Above command will log the below message in the SQL Server error log.
Backup Message:
Database backed up. Database: jshah, creation date(time): 2010/12/31(09:55:22), pages dumped: 178, first LSN: 28:60:170, last LSN: 28:130:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'c:\jshah.bak'}). This is an informational message only. No user action is required.

Solution
As a solution we can turn on the trace flag 3226 to stop loging of sucessfull backup message.

You can turn it on either using SQL Server Service Starup Parameter (-T 3226) or using DBCC TRACEON command.








-- To turn on the trace flag at global level
DBCC TRACEON (3226,-1)
-- To turn off the trace flag at global level
DBCC TRACEOFF (3226,-1)


1 comment: