sqlserver日誌提醒

標籤：

 
 

讀取的記錄檔來自安裝目錄下：MSSQL\Log\ERRORLOG--This will hold the rowsCREATE TABLE #ErrorLog (LogDate datetime, ProcessInfo VarChar(10), ErrorMessage VarChar(Max))-- Dump the errorlog into the tableINSERT INTO #ErrorLogEXEC master.dbo.xp_readerrorlog-- Delete everything older than 5 minutes-- ideally you will 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 #ErrorLogWHERE ErrorMessage LIKE‘BACKUP failed%‘-- Why does it take so looong to grow a file, maybe rethink your settingsSELECT * FROM #ErrorLogWHERE ErrorMessage LIKE‘Autogrow of file%‘-- What is going on any backups or statistic updates running at this time?SELECT * FROM #ErrorLogWHERE ErrorMessage LIKE‘SQL Server has encountered %occurrence(s) of I/O requests taking longer than%‘-- My mirror might not be up to dateSELECT * FROM #ErrorLogWHERE 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‘GOparameters:  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

 

sqlserver日誌提醒