July 21, 2008 at 4:18 am
Hello SQL Master DBA
anyone can help me to find the script for this issue.
(There are lots of sleeping connections from the problematical host (hmcapp04-pdc.nhg.local/10.50.137.60). The oldest connection has been there for more than 17 minutes, and some of the connections had been idle for more than 10 minutes.)
Thank you
Ayie
July 21, 2008 at 4:28 am
Why do you think idle connections are a problem? All sleeping means is that there's currently nothing running.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2008 at 8:48 am
The Database cause of blocking, can you give me an script to get the idle time,log and who's blocking of the sql. Its the idle time cause of cpu or sql?
Please help me to find a solution for this issue.
Thank you
July 21, 2008 at 9:02 am
Idle connections should not be causing blocking as they are doing nothing. Using sp_who or sp_who2 shows any blocked processes and the SPID which is causing the blocking. You can then use sp_lock [spid] to determine what resources are being blocked. Check out this article,
Return Query Text Along With sp_who2 Using Dynamic Management Views[/url], to see how you can work on "ferreting" out the offending queries.
You can also use SQL Profiler to find deadlocks. Check out this article, How to Track Down Deadlocks Using SQL Server 2005 Profiler[/url] , for how to do that.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 21, 2008 at 9:12 am
In returning to the issue, I want something to create an script, to see which is idle and lock, and log, can anyone give me the sample scripts, Im using sql 2000.
Thank you
July 21, 2008 at 9:30 am
There was a follow up to the first article in my earlier post, Return Query Text Along With sp_who2 Using SQL 2000[/url], that may help. You can still use sp_lock and Profiler as well.
You posted your issue in a 2005 forum which is why you received a 2005 answer.
Idle connections can be caused by a couple of different things:
Improperly closed connections from an application.
Applications that open and close connections regularly that are not using connection pooling, so that a connection is not re-used.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply