Thursday, November 21, 2013

Stop successful backup logging info in SQL Server Error Log

The SQL Server error log contains systems events and user defined events have information, user will use this log to troubleshoot problems related to SQLServer.

SQL Server error log will have information about
  • Backup and restore operations
  • batch command info
  • processes info
  • automatic recovery message
  • SQLServer stop and start info
  • kernel messages
  • Server –level error messages ...etc
But I see that SQL Server Error log is mainly full with “successful backup logging” due to frequent backups. In production environments, I have seen frequent log backups due to RPO (Recovery point objective) and Successful backup information will be logged into SQLServer Error log.

2013-11-18 18:15:08.08 Backup Database backed up. Database: EXP, creation date(time): 2008/07/09(16:46:27), pages dumped: 3732, first LSN: 10550:280:300, last LSN: 10550:408:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\\SQLServer_Backup\Full_Backup\EXP\EXP_backup_2013_11_18_181504_1347670.bak'}). This is an informational message only. No user action is required.
2013-11-18 18:45:06.40 Backup Log was backed up. Database: EXP, creation date(time): 2010/12/14(14:30:59), first LSN: 73542:11608:1, last LSN: 73542:11650:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'\\SQLServer_Backup\Log_Backup\EXP\EXP_backup_2013_11_18_184503_3906746.trn'}). This is an informational message only. No user action is required.

You can recycle the SQLServer error log to decrease the ERRORLOG size using below link, but it will not solve your problem completely.

Create New Error Log or Recycle Error log in SQLServer
http://www.sqlserver-expert.com/2012/06/create-new-error-log-or-recycle-error.html

It is really difficult to read the ERRORLOG due to this backup information, and we can stop logging successful backup information by using one of the below solution

1. DBCC TRACEON

Run below command to turn off/stop the successful backup entries.

DBCC TRACEON (3226,-1)


If you restart the SQL Server the changes will be gone. If you want to make these changes permanent then you need to specify the flag at start-up parameter.

2. SQL Server startup parameters

You can add trace flag “-T3226” to SQLServer startup parameters to suppress successful backup information
  • SQL Server Configuration Manager, Select SQL Server Services
  • Right click on SQL Server Instance service go to properties and select "startup parameters" tab
  • Enter “-T3226” value in “Specify a startup parameter” and click Add
  • Click on Apply.
 

You need to restart SQL Server in order to affect the changes.


Regards,
Satishbabu Gunukula
http://www.sqlserver-expert.com

No comments:

Post a Comment