SQL Server Performance Forum – Threads Archive

sleeping process

Dear sir,
I have an issue with sql 2000.There exist a lot of sleeping processess and i tried to kill it ,but it is still exist.Can you pls guide me what may cause this. Due to this the applications running on the system are facing timeout errors software engg
Diya systems

Instead kill processes I suggest to run Profiler and find why you have timeout errors. May be you have to optimize some queries.
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirÃ¡ luego de tu muerte
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Timeout can occur in other cases as well. Refer these
http://vyaskn.tripod.com/watch_your_timeouts.htm
http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm
Madhivanan Failing to plan is Planning to fail

In my experience, there are times that you might have to kill the process outside the SQL (i.e. running xp_cmdshell) using the taskmanager. Check out the real source.

I don’t want to ignore the obvious — When you kill a process it will take a few seconds, and you’ll need to press Refresh if you are looking at the list in Enterprise Manager. Secondly sleeping processes aren’t active so they aren’t slowing anything down causing timeouts because they aren’t in the middle of doing anything, unless you have so many that your server can’t handle any more (memory resource). If that is the case resolving them by you manually killing them isn’t going to resolve the problem for more than 1 minute, you’ll need to address that in your application since it is obviously hanging on to the resource and shouldn’t be. If you have queries timing out you can run the following script that will show you exactly what is waiting in the system, and what process is blocking it.
select * from sysprocesses
where waittype > 0
order by waittime desc
Queries that time out are usually the result of poor indexes, or poorly written T-SQL code that eats up and locks tons of data. Once you find out what process(es) are blocking everyone else using the command above, you can then double click on the process in the Enterprise Manager to find out what command that process is running, or by using the following command in Query Analyzer
DBCC INPUTBUFFER(SPID#)
Hopefully this will help,
Dalton Hope it helps,
Dalton Blessings aren’t so much a matter of recieving them as they are a matter of recognizing what you have received.

.. why taking action on SQL Server side, make sure the connections are closed from the appication (client) side and I second Dalton’s reference above about sleeping processes. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks for the valuable suggestions software engg
Diya systems

One doubt in the following query
select * from sysprocesses where waittype > 0order by waittime desc What is the meaning of waittype.I tried in books online it says Reserved. Pls clarify ?
software engg
Diya systems

It is the process that indicates the name of the last or current wait type. For a complete information on this refer tohttp://www.sqldev.net/misc/waittypes.htm link. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks software engg
Diya systems

]]>