Log files read from the installation directory: Mssql\log\errorlog--this'll hold the rowscreate TABLE #ErrorLog (logdate datetime, ProcessInfo VarChar ( ErrorMessage VarChar (Max))--Dump the errorlog into the Tableinsert into #ErrorLogEXEC master.dbo.xp_readerrorlog--D Elete everything older than 5 minutes--ideally you'll store the max date when it ran Lastdelete #ErrorLogWHERE logdate < DATEADD (Mi,-5,getdate ())--Some stuff you want to check for--Failed backups...you want to know thisselect * from #E Rrorlogwhere errormessage like ' BACKUP failed% '--what does it take so looong to grow a file, maybe rethink your Settingssel ECT * from #ErrorLogWHERE errormessage "autogrow of file%"--what's going on any backups or statistic updates running At this time? SELECT * from #ErrorLogWHERE errormessage "SQL Server has encountered%occurrence (s) of I/O requests taking longer tha n% '--My mirror might not being up to Dateselect * from #ErrorLogWHERE errormessage like ' The alert for ' unsent log ' have bee N raised% ' DROP TABLE #ErrorLog清除日志: Exec Sp_cycle_errorlog, performing the current errorlog rename to Errorlog.1
Exec xp_readerrorlog 2,1,null,null, ' 20130415 08:10 ', ' 20130415 12:30pm ', ' Asc ' GOparameters: 1.Value of error log File want to read:0 = current, 1 = Archive #1, 2 = Archive #2, etc ... 2.Log file type:1 or NULL = Error Log, 2 = SQL Agent log 3.Search string 1:string one want to Search for 4.Searc H string 2:string want to search for further refine the results 5.Search the start time 6.Search the end Tim e 7.Sort order for results:n ' asc ' = ascending, N ' desc ' = Descending
SQL Server Log Reminders