Solved

Index Rebuild causing blocking issue

Posted on 2011-09-14
10
329 Views
Last Modified: 2012-05-12
Hi,

The attached script is causing blocking issue on our prod servers. Please let me know how to avoid the blocking issue i.e., is there any modifications required in that attached script.

Thanks in advance.
Index-Rebild.txt
0
Comment
Question by:sg05121983
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36536588
ALTER INDEX IndexName ON TableName REBUILD WITH (ONLINE = ON)

Greg

0
 
LVL 21

Accepted Solution

by:
JestersGrind earned 250 total points
ID: 36536602
Sorry, didn't see that you had a script file.  Change this line

 SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REBUILD '' ;

to

 SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REBUILD  WITH (ONLINE = ON)'' ;

Greg

0
 

Author Comment

by:sg05121983
ID: 36536774
Thnaks Mr. JestersGrind.

I will try your suggestion and let your know the results.

Any other suggestions on my script?
0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36536891
The rest of the script looks fine.

Greg

0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 36537030
Just remember in your scripts you use the dm_db_index_physical_stats and that is a function that will go out and take some locks.  Not always will there be extreme blocking, but it is not a lightweight operation.  That will block things the way you are doing it, but it will always be the case and should be done at a time that there is less activity on the database.
0
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
LVL 22

Expert Comment

by:8080_Diver
ID: 36537244
You might also want to try to do that during "off hours" . . . doing it during "business hours" is not a Best Practice.
0
 
LVL 10

Expert Comment

by:sqlservr
ID: 36541926
JestersGrind:

ALTER INDEX IndexName ON TableName REBUILD WITH (ONLINE = ON)
online index rebuild runs only in the enterprise editon ?
0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36542643
@sqlservr

That's a good point.  Online index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions in SQL 2008.  

Greg

0
 

Author Closing Comment

by:sg05121983
ID: 36547172
--
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 36552207
I take it the author has the Enterprise version of SQL Server. ;-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
CONVERT date time to a different time zone. 2 45
Problem with SqlConnection 4 160
Filtered index 5 56
SQL Server (2008) Declare Variable (Date) and Set value 6 16
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now