| Author |
Topic |
|
anoop_mig25
Starting Member
20 Posts |
 Posted - 2015-02-04 : 00:12:40
|
Hi friends
At one of your client sides we have configured Always on with synchronous mode.Also we have schedule rebuild index and update statistics job which runs in night every alternate day. the issue is there are more then 100 sleeping queries which is blocking update statistics job.
I have to stop update statistics job manually once i come to office manually.
once i have killed blocking sleeping query but then other sleeping query blocked it and so on.
what is the issue and please suggest some solutions
Regards
Anoop |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2015-02-04 : 19:32:47
|
Is there any possibility that one of those sleeping queries has an open transaction? Is the sleeping query "Awaiting Command"?
Those who will not reason, are bigots, those who cannot, are fools, and those who dare not, are slaves. -Lord Byron, poet (1788-1824) |
 |
|
|
anoop_mig25
Starting Member
20 Posts |
Posted - 2015-02-05 : 00:14:58
|
quote:
Originally posted by Bustaz Kool
Is there any possibility that one of those sleeping queries has an open transaction? Is the sleeping query "Awaiting Command"?
Those who will not reason, are bigots, those who cannot, are fools, and those who dare not, are slaves. -Lord Byron, poet (1788-1824)
How to check whether sleeping queries has an open transaction and most of them are "awaiting command" |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2015-02-05 : 18:37:40
|
1) @@trancount will yield the number of transactions within the current connection
2) exec sp_who2 or sp_who will show a Status of "Sleeping" and a Command of "Awaiting Command".
Those who will not reason, are bigots, those who cannot, are fools, and those who dare not, are slaves. -Lord Byron, poet (1788-1824) |
|
|
|
anoop_mig25
Starting Member
20 Posts |
Posted - 2015-02-06 : 05:33:01
|
quote:
Originally posted by Bustaz Kool
1) @@trancount will yield the number of transactions within the current connection
2) exec sp_who2 or sp_who will show a Status of "Sleeping" and a Command of "Awaiting Command".
Those who will not reason, are bigots, those who cannot, are fools, and those who dare not, are slaves. -Lord Byron, poet (1788-1824)
Hi i know about sp_who/2 showing sleeping or awaiting coammnd queries.What i want to know is whether any one of them is waiting for transactuons to complete is there any query to determine the same
Please suggest solution my initial problem
|
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2015-02-06 : 16:26:04
|
Go to the session that is sleeping/awaiting command and run "SELECT @@trancount"
Those who will not reason, are bigots, those who cannot, are fools, and those who dare not, are slaves. -Lord Byron, poet (1788-1824) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-02-06 : 17:17:58
|
I just use the open_tran column in sysprocesses for the respective spid.
Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
Sleeping queries blocking rebuild index and update