Have you seen this error in the SQL Server error log before?
The client was unable to reuse a session with SPID XXX, which had been reset for connection pooling. The failure ID is 5. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
Usually those logs are on a remote server, have accumulated hundreds of megs of data, and will blow the top off Notepad if you try to open and search through it.
I wrote a little procedure to go through and find the bits that happened around those error messages and just report them back.
If Object_Id('dbo.Show_Error_Log_Previous', 'P') Is Not Null Drop Procedure dbo.Show_Error_Log_Previous Go Create Procedure dbo.Show_Error_Log_Previous @FromDate Date = Null, -- Only look after this date @LogNumber Int = 0, -- In case you want to search something other than the most recent log @Debug Bit = 0 -- Internal information about the procedure As Begin If @LogNumber Is Null Begin Set @LogNumber = 0 End If Object_Id('tempdb..#InputLog') Is Not Null Drop Table #InputLog Create Table #InputLog ( [LogId] Int Identity(1, 1), LogDate DateTime, ProcessInfo nvarchar(max), [Text] nvarchar(max)) Select * Into #OutputLog From #InputLog Insert #InputLog Exec master.dbo.xp_readerrorlog @LogNumber, -- Log number, might want to set this back further 1, -- Main log N'immediately before this error message', -- Text to search for Null, @FromDate, Null, 'asc' If @Debug = 1 Begin Select 'Errors found' As Information, * From #InputLog Order By LogId End Declare C_Log Cursor For Select LogDate, ProcessInfo From #InputLog Order By LogId Open C_Log Declare @LogDate DateTime2, @ProcessInfo nvarchar(max) -- DateTime2 is important to make it work Fetch Next From C_Log Into @LogDate, @ProcessInfo While @@Fetch_Status = 0 Begin Declare @LogDateFrom Nvarchar(19) = DateAdd(second, -1, @LogDate) Declare @LogDateTo Nvarchar(19) = DateAdd(second, 1, @LogDate) If @Debug = 1 Begin Print 'Fetching between ' + @LogDateFrom + ' and ' + @LogDateTo End Insert #OutputLog Exec master.dbo.Xp_Readerrorlog 0, 1, Null, Null, @LogDateFrom, @LogDateTo, N'asc' If @@RowCount = 0 And @Debug = 1 Begin Print 'No records were returned for ' + @LogDateFrom + ' to ' + @LogDateTo End Fetch Next From C_Log Into @LogDate, @ProcessInfo End Close C_Log Deallocate C_Log Select * From #OutputLog Order by LogDate, LogId End Go Exec dbo.Show_Error_Log_Previous
The output might be something like this:
|1||2013-11-15 15:34:36.180||Logon||Error: 18456, Severity: 14, State: 5.|
|2||2013-11-15 15:34:36.180||Logon||Login failed for user ‘S-1-9-3-123467890-123467890-123467890-123467890.'. Reason: Could not find a login matching the name provided. [CLIENT: 192.168.0.1]|
|3||2013-11-15 15:34:36.180||spidXXX||Error: 18056, Severity: 20, State: 5.|
|4||2013-11-15 15:34:36.180||spidXXX||The client was unable to reuse a session with SPID XXX, which had been reset for connection pooling. The failure ID is 5. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.|