September 30, 2013 at 7:01 am
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.
September 30, 2013 at 7:03 am
More than 1 what? According to the e-mail, what was the blocking query, and what was the blocked query?
John
September 30, 2013 at 7:05 am
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.
September 30, 2013 at 7:10 am
IS BLOCKING POSSIBLE If I run INDEX REORGANIZE job ??????
September 30, 2013 at 7:17 am
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
September 30, 2013 at 7:17 am
according to the blocking email it sometime select statement or update statament
September 30, 2013 at 7:19 am
Stop shouting at us please.
Yes, blocking is possible. It should be short-lived. It's very hard to help you as you're not giving us much in the way of details. No mention of lock types, wait durations, what exact statements were involved, etc.
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
September 30, 2013 at 7:28 am
Gila,
the wait time is 1 minute minimum for a blocking email(alert) to be triggered and i get like more then 5 blocking email.:-)
thanks
Ivan
September 30, 2013 at 7:33 am
Ok, so something (unknown) is causing something else (unknown) to be blocked for (unknown) duration with (unknown) lock types requested and (unknown) lock types held on (unknown) objects.
Good luck fixing this, because currently there's no way that anyone else can help.
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
September 30, 2013 at 7:55 am
GilaMonster (9/30/2013)
Ok, so something (unknown) is causing something else (unknown) to be blocked for (unknown) duration with (unknown) lock types requested and (unknown) lock types held on (unknown) objects.Good luck fixing this, because currently there's no way that anyone else can help.
so something (Select statement) is causing something else (alter index reorganize ) to be blocked for (5 to 10 min) duration with (unknown) lock types requested and (unknown) lock types held on (SAME) objects.
September 30, 2013 at 8:14 am
Not enough information to help. Exact statement that is being blocked. Exact statement (not batch) that is causing the blocking. The Wait time. The exact lock types. The object names. At an absolute minimum. The full blocking chain (with all wait types) would help.
Otherwise it's like calling a mechanic and telling him your car won't start but not giving any other information.
Oh, and if it's the select that's blocking the reorganise (which is the opposite of what you've been saying), then that's completely expected. Optimise the select to run in less time.
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
September 30, 2013 at 11:21 am
GilaMonster (9/30/2013)
Not enough information to help. Exact statement that is being blocked. Exact statement (not batch) that is causing the blocking. The Wait time. The exact lock types. The object names. At an absolute minimum. The full blocking chain (with all wait types) would help.Otherwise it's like calling a mechanic and telling him your car won't start but not giving any other information.
Oh, and if it's the select that's blocking the reorganise (which is the opposite of what you've been saying), then that's completely expected. Optimise the select to run in less time.
i already got my answer from you thanks a lot
March 19, 2014 at 2:18 am
I've run into this problem too. The only thing that comes to mind is that tables with LOB data types could be the problem. I know that for index REBUILD you cannot specify ONLINE = ON if the index contains LOB columns, but I haven't seen anything stating the same for REORGANIZE.
Anyone know anything about this?
In SQL there are no absolutes, it always depends...
March 27, 2018 at 1:26 pm
Thomas Mucha - Wednesday, March 19, 2014 2:18 AMI've run into this problem too. The only thing that comes to mind is that tables with LOB data types could be the problem. I know that for index REBUILD you cannot specify ONLINE = ON if the index contains LOB columns, but I haven't seen anything stating the same for REORGANIZE.Anyone know anything about this?
Seeing it too, and that googling led me here.
As per sp_whoisactive
Thread blocking:
ALTER INDEX ALL ON mytablename REORGANIZE PARTITION= @switch_partition
and then the thread being blocked (which doesn't show either in sql_text or sql_command) has wait type:
(2652058ms)LCK_M_SCH_M.
My only thought is that it's something to do with ALTER INDEX ALL that's grabbing that schema lock.
The partitioned table we're REORGing, FWIW, has both an XML field and a varchar(max) field.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply