Open Connections

[wbodger]

So, I'm trying to clean-up this production hairball that I have inherited. It is SQL 2000 sp4 running on Windows Server 2003 sp1. I *think* that there are some connections that are not getting closed, which I am currently searching for. In the meantime, however, I would like to figure out if SQL 2000 has a setting for automatically killing idle connections or if that is even a good idea? When I run sp_who3, the event info is frequently either sp_cursorclose;1 or null and they aren't doing anything but sitting there and have been for a while. Thoughts, suggestions? Thanks for any help or direction you can give me.

Willie

 

[mrdenny]

SQL doesn't kill idle connections, and there is no setting to do so.

You can see if a connection is idle by checking the sysprocesses (or sys.sysprocesses if SQL 2005) and looking at the status and last_batch column. If the status is sleeping and the last_batch was a while ago then the connection is idle.

However once you have identified the idle connections I would recommend leaving them alone. The client application may not respond when to having it's connection closed and will probably through an error.

What is the issue that you are having which is making you look at the idle connections?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[wbodger]

Hmm... My database just came to a crawl yesterday afternoon. I could see some intermittent blocking issues, but they were scattered around so I began wondering if it was due to the number of open conenction (in chorus with other issues, like having only 2 GB of RAM). I killed a few of the processes that were blocking and things cleared up, bit was odd. Even when there weren't any blocking issues it was painfully slow.

I have been running some defrag and reindexing scripts to clena up teh database (but none recently, like this week) and I have log shipping running every hour. This server is ONLY running SQL 2000, nothing else. I don't see anything odd in the error logs.

Willie

 

[SQLBill]

Willie,

To answer your question about if SQL Server can automatically close connections. I had this discussion with Microsoft myself due to issues at my last job.

SQL Server is not designed to recognize and drop un-used/idle/etc connections. It depends upon the OS or the front-end application to drop connections. Microsoft OS has a default to check for dead connections every 2 hours. This can be changed (I forget where), but isn't always recommended.

-SQLBill

Posting advice: FAQ481-4875