Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

BLOCKING CAUSED BY REORGANIZE OF INDEX JOB IN SQL SERVER 2005 Expand / Collapse
Author
Message
Posted Monday, September 30, 2013 6:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 40,609, Visits: 37,070
John Mitchell-245523 (9/30/2013)
Have you tried to use sys.dm_os_waiting_tasks to see which session is doing the blocking?


?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1499970
Posted Monday, September 30, 2013 6:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 20, 2014 8:01 AM
Points: 123, Visits: 462
john
yes i have automatic blocking alert also with the details which is causing the blocking with query and time and etc .

but here my question is when i reorganize the index why the blocking occurs which should not come as per the reorganize index criteria reorganize of index is online and lock does not occurs at that point of time as the index are available for use.
Post #1499972
Posted Monday, September 30, 2013 6:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 20, 2014 8:01 AM
Points: 123, Visits: 462
well gila i have the details of which all others query was running @ that point of time but alter index reorganize is the head of the blocking chain.
Post #1499978
Posted Monday, September 30, 2013 6:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:28 PM
Points: 5,485, Visits: 10,323
Yes, but how do you know it's the ALTER INDEX...REORGANIZE statement that's doing the blocking? The code you posted does more than that.

How long does the blocking have to last before the alert is sent?

John
Post #1499981
Posted Monday, September 30, 2013 6:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:28 PM
Points: 5,485, Visits: 10,323
Ivan Mohapatra (9/30/2013)
well gila i have the details of which all others query was running @ that point of time but alter index reorganize is the head of the blocking chain.

Ah, that answers one of my questions. What was the query that was being blocked?

John
Post #1499982
Posted Monday, September 30, 2013 7:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 20, 2014 8:01 AM
Points: 123, Visits: 462
IF the blocking occurs more then 1 i get the email alert with a attachment containing all the session query with each and every details what blocked which query blocked and etc.
Post #1499984
Posted Monday, September 30, 2013 7:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:28 PM
Points: 5,485, Visits: 10,323
More than 1 what? According to the e-mail, what was the blocking query, and what was the blocked query?

John
Post #1499985
Posted Monday, September 30, 2013 7:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 20, 2014 8:01 AM
Points: 123, Visits: 462
john every time the query is different and i know u making a point of another task or session running @ that point of time every day same time then no such thing it is usually different query running no particular same query found still now.
Post #1499986
Posted Monday, September 30, 2013 7:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 20, 2014 8:01 AM
Points: 123, Visits: 462
IS BLOCKING POSSIBLE If I run INDEX REORGANIZE job ??????
Post #1499988
Posted Monday, September 30, 2013 7:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:28 PM
Points: 5,485, Visits: 10,323
According to Books Online, with ALTER INDEX...REORGANIZE, "long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction". You still haven't said what the blocking threshold is for your e-mail, nor have you provided an example of what is being blocked. Without those two things, it's very difficult to help.

John
Post #1499990
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse