[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Index Rebuild causing blocking issue

Posted on 2011-09-14
10
Medium Priority
?
364 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1000 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 21

Expert Comment

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

Greg

0
 
LVL 25

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
 
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:Ramesh Babu Vavilla
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

649 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