365DBA Blog

Database Stuff

SQL Server Monitoring Scripts - 8 - General SQL Errors Check

To wrap up the SQL scripts for this segment, here is a script to comb the logs and look for some common errors. You can add and remove checks by modifying the calls to xp_readerrorlog so you can catch errors that you are most concerned about in your environment.

Set NoCount On
Set ANSI_Warnings Off
Select '---------------General SQL Errors Check---------------'
Declare @LastCheck DateTime
Select @LastCheck = DateAdd(dd, -365, GetDate())
If (@LastCheck Is Null)
Begin
Set @LastCheck = '1-1-2000'
End
If Object_ID('tempdb.dbo.#ErrLog1') Is Not Null
Drop Table #ErrLog1;
Create Table #ErrLog1 (LogDate DateTime, ProcessInfo VarChar(50), LogText VarChar(2000))
Insert Into #ErrLog1 (LogDate, ProcessInfo, LogText) 
Exec xp_readerrorlog 0, 1, N'STACK DUMP'
Insert Into #ErrLog1 (LogDate, ProcessInfo, LogText)
Exec xp_readerrorlog 0, 1, N'Severity';
Insert Into #ErrLog1 (LogDate, ProcessInfo, LogText)
Exec xp_readerrorlog 0, 1, N'Login', N'failed';
Insert Into #ErrLog1 (LogDate, ProcessInfo, LogText)
Exec xp_readerrorlog 0, 1, N'I/O requests';
Delete From #ErrLog1 Where LogDate <= @LastCheck
Delete From #ErrLog1 Where LogText Like 'Error: 18456%' --Login Failed (removing since we are capturing it explicitly above)
Delete From #ErrLog1 Where LogText Like 'Error: 18210%' --VDI Backup Failures (http://support.microsoft.com/kb/2497575)
Delete From #ErrLog1 Where LogText Like 'Error: 3041%' --Log Backup Failures (removing since we are capturing it explicitly in other scripts)

Select Cast(LogDate As VarChar(25)), Left(LogText, 500) From #ErrLog1
If Object_ID('tempdb.dbo.#ErrLog1') Is Not Null
Drop Table #ErrLog1;
Loading