• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

Index Rebuild causing blocking issue

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
sg05121983
Asked:
sg05121983
  • 4
  • 2
  • 2
  • +2
1 Solution
 
JestersGrindCommented:
ALTER INDEX IndexName ON TableName REBUILD WITH (ONLINE = ON)

Greg

0
 
JestersGrindCommented:
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
 
sg05121983Author Commented:
Thnaks Mr. JestersGrind.

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

Any other suggestions on my script?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
JestersGrindCommented:
The rest of the script looks fine.

Greg

0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
 
8080_DiverCommented:
You might also want to try to do that during "off hours" . . . doing it during "business hours" is not a Best Practice.
0
 
Ramesh Babu VavillaCommented:
JestersGrind:

ALTER INDEX IndexName ON TableName REBUILD WITH (ONLINE = ON)
online index rebuild runs only in the enterprise editon ?
0
 
JestersGrindCommented:
@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
 
sg05121983Author Commented:
--
0
 
8080_DiverCommented:
I take it the author has the Enterprise version of SQL Server. ;-)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now