SQL Server Error logs

SQL Server Error logs

step1: Reading current log
sp_readerrorlog

step2: Reading archie1 log
sp_readerrorlog 1

step3: Reading agents current log
sp_readerrorlog 0,2  2 indicates agent log 1 for sql log

step4: To filter errorlog for error word
sp_readerrorlog 0,1,'error'

step5: To recycle errorlog
sp_cycle_errorlog

step6: Check current log after recycling
sp_readerrorlog

step7: To recycle sql agent log
use msdb
go
sp_cycle_agent_errorlog

step8: Backup of master database
backup database master to disk='master.bak'

step9: Check the errorlog for backup details
sp_readerrorlog 0,1,'master'

step10: Backup fails..
backup database master1 to disk='master1.bak'

step11: Check errorlog
sp_readerrorlog 0,1,'master1'

step12: To customize event logging we can use trace flags
dbcc traceon(3226,-1) 3226 avoid success entries into errorlog

step13: Take backup of any database
backup database msdb to disk='msdb.bak'

step14: Check msdb backup details. Not recorded
sp_readerrorlog 0,1,'msdb'

step15: Make trace flag off
dbcc tracestatusTo enabled trace flags
dbcc traceoff(3226,-1)