Mysteriously max out on user connections

[Darin Drewrey]

Recently, my SQL Server has started getting:
"Unable to connect. The maxinum of '100' configured user connections are already connected. System Administrator can configure to a higher value with sp_configure."
The server has to be rebooted to recover.

What's odd is that this occurs during the weekend when only about 5-8 connections are used by various system processes. I can't tell yet what is sucking up the connections.

We use SQL 6.5 sp4 on NT4.0 sp3 for DEC Alpha. We are also using SQL Mail, Seagate Backup (with SQL drivers) and some cgis that talk to the database.

Any thoughts would be appreciated.

Darin Drewrey
DBA
Extensis Corp

[Greg]

using SQL Perform monitor, watch for User connections?

I'd also setup an alert to fire in case user connections max out, that will
run simple task, like this (so at least u'll know that's running...)

i hope that 'll help.....

create procedure usp_save_sysprocesses_file

as

set nocount on

declare @spid smallint
declare @status char(10)
declare @hostname char(30)
declare @program_name char(30)
declare @cmd char(16)
declare @cpu int
declare @physical_io int
declare @memusage int
declare @blocked smallint
declare @dbid smallint
declare @login_time datetime
declare @last_batch datetime

declare @printout varchar(255)
declare @filename varchar(255)

select @filename = 'c:mssqllogsysprocesses'+ '_' +
convert(varchar(8),getdate(),112) + '_'+
convert(varchar(2),datepart(hh,getdate())) +
convert(varchar(2),datepart(mi,getdate())) +
convert(varchar(2),datepart(ss,getdate())) +
'.log'

declare sysprocess_cursor INSENSITIVE cursor
for
select
spid
,status
,hostname
,program_name
,cmd
,cpu
,physical_io
,memusage
,blocked
,dbid
,login_time
,last_batch
from
master..sysprocesses
FOR READ ONLY

open sysprocess_cursor


fetch next from sysprocess_cursor into
@spid
,@status
,@hostname
,@program_name
,@cmd
,@cpu
,@physical_io
,@memusage
,@blocked
,@dbid
,@login_time
,@last_batch


select @printout= 'echo '+ 'sysprocesses: ' + convert(varchar,getdate()) + ' >'+ @filename
exec master..xp_cmdshell @printout


select @printout= 'echo '+ replicate('*',200) + ' >>'+ @filename
exec master..xp_cmdshell @printout

select @printout = 'spid' + char(9) +
'status ' + char(9) +
'host' + space(27) + char(9) +
'program' + space(24) + char(9) +

'cmd ' + char(9) +
'cpu ' + char(9) +
'io ' + char(9) +
'memusage ' + char(9) +
'blocked ' + char(9) +
'dbid ' + char(9) +
'login ' + char(9) +
'last batch '
select @printout= 'echo '+ @printout + '>>'+ @filename
exec master..xp_cmdshell @printout

select @printout= 'echo '+ replicate('*',200)+ ' >>'+ @filename
exec master..xp_cmdshell @printout


while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)

begin
select @printout = convert(char(4),@spid) + char(9) +
@status + char(9) +
@hostname + char(9) +
@program_name + char(9) +
@cmd + char(9) +
convert(char(10),@cpu) + char(9) +
convert(char(10),@physical_io) + char(9)+
convert(char(10),@memusage) + char(9) +
convert(char(10),@blocked) + char(9) +
convert(char(10),@dbid) + char(9) +
convert(varchar,@login_time) + char(9) +
convert(varchar,@last_batch)
select @printout= &#39;echo &#39;+ @printout + &#39;>>&#39;+ @filename
exec master..xp_cmdshell @printout

end
fetch next from sysprocess_cursor into
@spid
,@status
,@hostname
,@program_name
,@cmd
,@cpu
,@physical_io
,@memusage
,@blocked
,@dbid
,@login_time
,@last_batch
end


close sysprocess_cursor

deallocate sysprocess_cursor

set nocount off
GO



On 3/1/99 3:55:46 PM, Darin Drewrey wrote:
> Recently, my SQL Server has started getting:
&#34;Unable to connect. The
> maxinum of &#39;100&#39; configured user connections are already connected.
> System Administrator can configure to a higher value with
> sp_configure.&#34;
The server has to be rebooted to recover.

What&#39;s
> odd is that this occurs during the weekend when only about 5-8 connections
> are used by various system processes. I can&#39;t tell yet what is sucking
> up the connections.

We use SQL 6.5 sp4 on NT4.0 sp3 for DEC Alpha. We
> are also using SQL Mail, Seagate Backup (with SQL drivers) and some cgis
> that talk to the database.

Any thoughts would be appreciated.

Darin
> Drewrey
DBA
Extensis Corp

[Geoff Oliphant]

This is a documented SQL Server 6.5 bug introduced in sp3. It is NOT fixed in sp5 and MS are unsure whether it is fixed in 7.0.

The best workaround they could give me is set the user connections attribute artificially high and then monitor it with Performance Monitor.

Good luck



On 3/4/99 10:27:24 AM, Darin Drewrey wrote:
> Thanks for the code Greg. This should help a lot.
-Darin.


On 3/2/99
> 11:34:15 AM, Greg wrote:
> using SQL Perform monitor, watch for User
> connections?

I&#39;d also
> setup an alert to fire in case user
> connections max out, that will
run
> simple task, like this (so at least
> u&#39;ll know that&#39;s
> running...)

i hope that &#39;ll help.....

[Darin Drewrey]

Thanks for the code Greg. This should help a lot.
-Darin.


On 3/2/99 11:34:15 AM, Greg wrote:
> using SQL Perform monitor, watch for User connections?

I&#39;d also
> setup an alert to fire in case user connections max out, that will
run
> simple task, like this (so at least u&#39;ll know that&#39;s
> running...)

i hope that &#39;ll help.....