sqldmo_ (number) - lots of connections
-
can anyone tell me why over the course of the last 3 days I suddenly have over 200 open db connections for the sqldmo_(number) user?
sample:
RTRIM(msp.hostname) AS HostName - MAIN-DB
RTRIM(mdb.name) AS DB - master
RTRIM(msp.loginame) AS iName - NT AUTHORITY\SYSTEM
RTRIM(msp.cmd) AS Cmd - AWAITING COMMAND
msp.blocked - 0
msp.cpu - 15
msp.physical_io - 0
msp.memusage - 0
msp.login_time - 2005-07-31 21:55:00.633
msp.last_batch - 2005-07-31 21:55:00.743
RTRIM(msp.status) AS Status - sleeping
RTRIM(msp.program_name) AS Program_Name - SQLDMO_3390
RTRIM(msp.nt_username) AS nt_UserName - SYSTEM
msp.waittime - 0
RTRIM(msp.lastwaittype) AS LastWaitType - PAGELATCH_UP
RTRIM(msp.waitresource) AS WaitResource - 2:1:140
I've got over 200 of these guys, just sitting there, and they appear to be going up and down by 2's, but mostly up.
I've talked to my developers and they say no major changes in vb code. I haven't made any db changes.
anyhelp at all, any ideas, or opinions about where to even start looking?
-
what additional info can I provide.
the connections are starting to mount up. They were just over 200 last night now over 260. All with basically the same spid record set data.
-
Run profiler and see where the connections are coming from, what IP, hostname, etc. is being sent to the server. Are they all in the same db? Perhaps something in the VB code isn't releasing or closing connections. Also,if the code hasn't changed, perhaps the "use" of the code is changing, a new function that hasn't been run before?
-
Why so many connections? Probably because the VB code was not properly written! VB developers sometimes forget to close connections that they open.
Rather than constantly fight with developers (OK. You can call me a coward. I think that sometimes one must pick his battles carefully), I'v created a job which runs this sproc several times a day, killing all stale connections.
Plan of action: check sysprocesses to see all open processes.
Create Proc KillSleepers
as
set nocount on
declare @counter int
declare @killid int
declare @temp table (ID int)
insert @temp
select spid from master.dbo.sysprocesses(nolock)
where status='sleeping' and loginame not in ('sa','NT AUTHORITY\SYSTEM')
and last_batch <= DATEADD(hh,1, getdate())
and blocked = 0
order by spid
select @counter = count(ID) from @temp
while @counter > 0
begin
select @killid = min(ID) from @temp
exec ('kill ' + @killid)
delete from @temp where ID = @killid
select @counter = @counter-1
end
Probably shoulda used a cursor instead, now that I am reviewing it.
-
found something.
MOM - Microsoft operations manager
Haven't made any changes to sql server or MOM monitoring around the time this started, but noticed a time pattern. Every 30 minutes @ :25 and :55 past the hour. A spid would go into waitResource mode and sit.
We disabled the sql server monitoring packages and all the sqldmo_% spids dropped. No useful info in trace, no nothing, just NULL's...
am trying to duplicate with different filter
-
Hi, I have this same problem also and have tried so many things to find out what is happeneing but all with no joy, including running profiler etc.!
It is the MOM action account (Momaction) that is causing the problem. Every hour at :25 and :55 past the hour I get a failed login attempt for Momaction on the SQL Server and one of these SQLDM_xx (where xx is an arbitrary number) appears and goes into a wait state.
Eventually the database dies with too many connections. I have found that if I stop and restart the MOM service on the server (SQL Server and MOM 2005 Worgroup Edition SP1 installed on same Windows 2003 SP1 server) then all the SQLDMO_xx processes disappear and I can get the database functioning properly again. I have to do this every 3 or 4 days to avoid having to reboot my server!
I have scoured through MOM configs of MP's, scripts and the like but cannot find what is causing this. There is no mention of this problem on MS MOM site and MS are no help at all.
It seems really strange as everything else in MOM appears to be working OK. The Momaction account is a domain admin account and has admin access rights to every database within SQL Server. I can log in with this account and access and query any database within SQL. Because of this I don't understand why I am getting 17055 18456 login errors aginst the Momaction account within SQL server!
Help is needed here!!
-
I had the same problem. MOM uses the following query to check if the databases are online.
select name, DATABASEPROPERTY(name, N'IsDetached'), (case when DATABASEPROPERTY(name, N'IsShutdown') is null then -1 else DATABASEPROPERTY(name, N'IsShutdown') end), DATABASEPROPERTY(name, N'IsSuspect'), DATABASEPROPERTY(name, N'IsOffline'), DATABASEPROPERTY(name, N'IsInLoad'), (case when DATABASEPROPERTY(name, N'IsInRecovery') is null then -1 else DATABASEPROPERTY(name, N'IsInRecovery') end), (case when DATABASEPROPERTY(name, N'IsNotRecovered') is null then -1 else DATABASEPROPERTY(name, N'IsNotRecovered') end), DATABASEPROPERTY(name, N'IsEmergencyMode'), DATABASEPROPERTY(name, N'IsInStandBy'), has_dbaccess(name), status, category, status2 from master.dbo.sysdatabases
According to the query one of my databases was off-line, but is wasn't. It was running well.
I solved the problem by doing a detach/attach.
After doing this and running the query again, it saw my database was online.
-
Same problem here. SQLDMO_ running "dbcc checkcatalog WITH NO_INFOMSGS" on every single database and making backups to virtual devices that we don't use. Source is the database server itself.
Randy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply