So randomly every 1 to 6 days queries start timing out and I'm almost positive it's from an improperly terminated transaction
Is there a way to snoop this out the next time it happens? Like when a table's locked I can look and see yea this is the transaction it's in the middle of?
You can either do this pro-actively or reactively.
Proactive : Set up a job that runs perhaps every minute, queries sysprocesses and spits the result into a table. So when a timeout happens you can query this table and see who/what/when.
Reactive: As soon as you see a timeout, query sysprocesses, get the spid, do dbcc inputbuffer(@.spid) get the T-SQL and fix it.
|||If you already know the proc, fix it.
|||You can use the Activity Monitor in SQl Server 2005 Management studio. Located under Management -> Activity Monitor.
|||
jperry4:
You can use the Activity Monitor in SQl Server 2005 Management studio. Located under Management -> Activity Monitor.
SWEET that's exactly what I've always been looking for :)
one more questions
There's like 100 processes in there that are "Sleeping" "Awaiting COmmand"
Are these like connections that haven't been closed?
So in CODE if conn.Close() or conn.Dispose()
NEver got called it's just left dangling in a "Sleeping" "Awaiting Command" state?
Shane
|||No, those are likely connections that are in the connection pool.
Don't get confused by a database physical connection and a logical connection. Conn.Close closes the logical connection, which (probably) is tied to a physical one. The physical connection is then returned to the connection pool waiting for someone to request a connection again.
|||Cool thanks for the info :)
Yea I ran a bunch of tests with basic programs just opening and closing connections and then read a bunch of stuff on pooling...
The Activity Monitor tool worked out great though for sniffing around the application and finding all the leaks though
Finding those places where the logical was never shut down so it kept opening 100s of new ones
No comments:
Post a Comment