读取的日志文件来自安装目录下：MSSQL\Log\ERRORLOG

--This will hold the rows
CREATE TABLE #ErrorLog (LogDate datetime, ProcessInfo VarChar(10), ErrorMessage VarChar(Max))
 
-- Dump the errorlog into the table
INSERT INTO #ErrorLog
EXEC master.dbo.xp_readerrorlog
 
-- Delete everything older than 5 minutes
-- ideally you will store the max date when it ran last
DELETE #ErrorLog
WHERE LogDate <  DATEADD(mi,-5,GETDATE())
 
-- Some stuff you want to check for
-- Failed backups...you want to know this
SELECT * FROM #ErrorLog
WHERE ErrorMessage LIKE'BACKUP failed%'
 
-- Why does it take so looong to grow a file, maybe rethink your settings
SELECT * FROM #ErrorLog
WHERE ErrorMessage LIKE'Autogrow of file%'
 
-- What is going on any backups or statistic updates running at this time?
SELECT * FROM #ErrorLog
WHERE ErrorMessage LIKE'SQL Server has encountered %occurrence(s) of I/O requests taking longer than%'
 
-- My mirror might not be up to date
SELECT * FROM #ErrorLog
WHERE ErrorMessage LIKE'The alert for ''unsent log'' has been raised%'
 
 
DROP TABLE #ErrorLog

清除日志：Exec sp_cycle_errorlog，执行当前ERRORLOG 重命名为ERRORLOG.1

Exec xp_readerrorlog 2,1,Null,Null,'20130415 08:10','20130415 12:30','Asc'
GO
parameters:
  1.Value of error log file you 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 you want to search for 
  4.Search string 2: String two you want to search for to further refine the results
  5.Search the start time
  6.Search the end time
  7.Sort order for results: N'asc' = ascending, N'desc' = descending

参考：Proactive notifications 死锁提醒