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)
Advertisements

One thought on “Stop successfull backup loging messages in SQL Server Error Log?

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s