October 2, 2006 at 11:41 am
Hi,
I have had issues with sleeping transactions in Ms SQL Server 2000 with my hosting company.
That is true that I saw about 10 sleeping transactions when querrying the database. I have done some investigation about this topic. I went thru a lot of my coding and I about 99.99% sure that I am closing every and each connection to the db and I am puzzled that I am still having such transaction (all very recent) much less but not down to zero. I am surprised that event Ms Entreprise Manager and Query Analyzer do generate such transaction too.
I wrote this:
set conMD = Server.CreateObject("ADODB.Connection")
conMD.ConnectionTimeout = 15
conMD.CommandTimeout = 30
conMD.CursorLocation =1
conMD.Open Application("conMD_ConnectionString")
sSQL= "SELECT Cd_Numero, Cd_Texto"
sSQL= sSQL & " FROM tblCodigos"
sSQL = sSQL & " WHERE Cd_Tipo=10"
sSQL = sSQL & " AND Cd_Lenguage=5"
Set rst = Server.CreateObject("ADODB.Recordset")
rst.open sSQL,conMD,adOpenForwardOnly,adLockReadOnly,adCmdText
do until rst.eof
if cint(rst("Cd_Numero")) = 1 then
Cd_Texto=rst("Cd_Texto")
else
Cd_Texto2=rst("Cd_Texto")
end if
rst.MoveNext
loop
rst.Close
set rst=nothing
conMD.close
set conMD=nothing
I run this code at 5AM in the morning when hardly anybody was online and it was showing as sleeping transaction. Why ?
Jean-Luc
www.corobori.com
October 3, 2006 at 7:10 am
It's not the transaction sleeping. From client app point of view, the connection is terminiated. But ado and sql server use connection pool technology. The real connection is still there and sleeping. If your/other app uses the same parameters to connect to the DB again, the connection will be resused, which has is less expensive than open a new one.
October 3, 2006 at 7:47 am
You're right I should have said "sleeping connections". If I understand well what you're saying there isn't much I can do about it and it doesn't sound too bad.
Jean-Luc
www.corobori.com
October 4, 2006 at 7:08 am
In most cases, connection pool is very helpful to your app's performance, especally when you have lots of clients. So it's not wise to disable it.
If you really do not want this behaviour, you can set the connection not using pooling. In ado.net, the property is Pooling=false.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply