Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jcavaliere
Starting Member
4 Posts |
 Posted - 2007-05-20 : 09:50:21
|
| Here's the deal - we have an old application ( written for SQL 7 in classic ASP) that our client decided to update the SQL server to 2005 with a server switch. For some unknown reason the SQL connections are not closing out when the connection.close method is being called, leaving anywhere from 500-1000 open connections after a couple of hours.I wrote the script below and have been manually running it for a couple of days now, but I want to put it in some kind of automated task and not have to worry about it any more. I'm a developer not a DBA, so I just hacled my way through the sp_who2 to get the info I needed and tried to piece somehhting together. I was going to put one other filter if necessary that said only connections older then 30 minutes. Should this work without any problems? Thanks for your time, -JasonDECLARE csr Cursor FOR select spid from master.dbo.sysprocesses with (nolock)where cmd='AWAITING COMMAND'AND STATUS='SLEEPING'open csrDECLARE @SPID smallintFetch NEXT FROM csr INTO @SPIDWhile (@@FETCH_STATUS <> -1)BEGIN exec('kill ' + @SPID) FETCH NEXT FROM csr INTO @SPIDENDCLOSE csrDEALLOCATE csrGO |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-05-20 : 09:59:53
|
| well... sure you can execute this... i think about safe though...try updateing your MDAC driver then see why the connection don't close.this is a pure asp problem and not a sql server. connection are opened from the client and are also closed on the client, so check if you're using anything other than forward only read only cursors in your asp code._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
jcavaliere
Starting Member
4 Posts |
Posted - 2007-05-20 : 10:23:04
|
| Sorry, I should have prefaced with this - we can't change the source code, it's been an abandoned application. I need to find a sollution outside of the application. I can look into the MDAC version, but it's a 2003 machine with the latest updates, so I would imagine that the newest version is on there, no? Thanks for your help :)-Jason |
|
|
|
rudesyle
Posting Yak Master
110 Posts |
Posted - 2007-05-20 : 10:23:09
|
| Also, in the asp code, see how the connection is being opened, mainly, is it using one dedicated connection string, rather than a separate one for each login. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-20 : 16:56:35
|
It shouldn't really matter that the connection is not being formerly closed in ASP.if the web server also the SQL Server (now SQL 2005)? if so that will have put the latest MDAC on there I reckon  If not then it won't have got updated since it was installed ... bound to be newer MDAC drivers for SQL 2005 I reckon.The issue might be to do with Pooling of connections, which you can probably tinker with without having to mess with the ASP code.What I'm struggling with is why this would have changed with the installation of SQL 2005. There must be some difference in "communication" that came about with the 2005 install ... but I don't know what it is, hopefully someone else will!Kristen |
|
|
|
|
|
|
|
|
Can I run this safely every few minutes?