It has happened again and this time I could start checking what was going on 🙂

The first problem was connecting to database: I couldn't log into because I got error 701. So, I logged in successfully using the DAC connection.

I started running the following, althought I had not a lot of expectation because we use quite a few of stored procedures:

DBCC FREESYSTEMCACHE ('ALL');

DBCC FREESESSIONCACHE

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

Nothing changed.

I tried then with sp_who2 and got this:

Mens 701, Nivel 17, Estado 123, Procedimiento sp_who2, Línea 49

Error: 701, Severity: 17, State: 123. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

I executed the following query to see all connections opened:

SELECT

spid,status,sid,hostname,program_name,cmd,cpu,physical_io,blocked,dbid,

convert(sysname, rtrim(loginame)) as loginname,spid as 'spid_sort',

substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' ' + substring( convert(varchar,last_batch,113) ,13 ,8 ) as 'last_batch_char',last_batch, DB_NAME(dbid)

from master.dbo.sysprocesses (nolock)

order by loginname

and here I saw something unexpected: there were 30.036 connections where the normal average is 40. All were done with the same login and against the same database: it was a 3rd party provider that every time they wanted to execute a stored procedure they were creating a connection and leaving it opened.

The problem is different than the original post in this forum thread, but I think it's also interesting...

Josep