We have got a one request in my organization
To Eliminate the all successful backup's messages only from SQL server error log,use below trace flag.
-T
3226
Normally Sql server error log
contain all the information about how the sql server is running, what is
happening and occurring on your database, its normally the first place you look at
when you have any issue of the database. Keeping it small and as useful
as it can be helps a lot when it comes to troubleshooting, since you don’t have
to scan through hundreds of lines of useless information and locate the issue
you might be having. Keeping it recycle on a regular interval is a good
practice and should be set as default for easier maintenance.
If you have many databases within
a single instance and/or have frequent backup mean that you will have all the
successful backup entries written to SQL error log and makes the file grow large
(and fast). If you do not have any scripts or monitoring that requires the
successful backup entry in error log, it would be recommended to turn on trace
flag 3226 to suppress all successful backups entries. It will, however, still
write out the error entry if backup is unsuccessful, and all successful entry
are still logged in msdb database.
Manually How to implement:
To manually turn on this trace
flag globally, you can use the below code:
DBCC TRACEON (3226, -1)
To turn this trace off, you can just below query execute:
DBCC TRACEOFF (3226)
To see the what traces are enable
globally use Trace status command:
DBCC TRACESTATUS(-1)
As above setup is a manual setup, it will
not effect after service restart, to keep this setting effective after
restart please use through startup parameter option.
Globally how to enable trace flag:
Step-1: Open sql server
configuration manager through run command or startup programs
Step-2: After sql server configuration
manager open select the sql server service in left pane
Step-3: In right pane select and
right on MSSQLSERVER service
Step-4: click on ok properties sql
server process
Step-5: Go to the advanced startup
parameter tab
Step-6: Enter and add the below
trace flag
-T 3226
Step-7: Apply and Click on OK, ok
Step-8: Restart the sql services.
Step-9: After restart connect to instance and open new
query analyzer run below command to verify the trace flag whether enable or
not.
DBCC TRACESTATUS(-1)
Or
use master
go
exec xp_readerrorlog 0, 1, 'lock'
Screen shots for above steps:
Lab test:
Let try
to run this and see what happens, we are testing manual setup method mentioned
above.
BACKUP DATABASE DBA_test TO DISK = 'D:\DBA_test.bak'
GO
DBCC TRACEON (3226, -1)
BACKUP DATABASE DBA_test TO DISK = 'D:\DBA_test.bak'
GO
DBCC TRACEOFF (3226, -1)
BACKUP DATABASE DBA_test TO DISK = 'D:\DBA_test.bak'
GO
--wanted make the backup fail
DBCC TRACEON (3226, -1)
BACKUP DATABASE DBA_test TO DISK = 'D:\MSSQL\DBA_test.bak'
GO
DBCC TRACEOFF (3226, -1)
From the code above, we will firstly backup the test
database, enable the trace flag and perform the backup again, turn the trace
flag off and perform the backup the third time. After its done, we will turn on
the trace flag and wanted perform a failure backup. What we are expecting is
that we should see the backup entry from the first try, nothing from the second
and an entry for the third. The reason for the last part is to ensure that even
if we have the trace flag on, we will still get failure backup entry in error
log. Let check the result:
The above result in screen shot match's what we are expecting.
we can conclude is that if you are not depending on the successful backup
entry in SQL error log, you can enable this trace flag to minimize the number
of entries in it.
