Solved

question about rebuild index with smo

Posted on 2014-03-13
7
358 Views
Last Modified: 2014-03-24
Hello,

The following error is returned when I execute the script if page level locking is disabled

ERROR - Microsoft.SqlServer.Management.Smo.FailedOperationException: Reorganize failed for Index 'idx_tms_login_log_date'.  ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: The index "idx_log_date" on table "login" cannot be reorganized because page level locking is disabled.

I have modified the script like :

if ($Average_Fragmentation -gt 40.00 -and $Index.IsDisabled -eq $false -and $Index.IsPageLockDisallowed -eq $false)

            {
                  
                  Try {  
                  $index.Rebuild();
                  $A = "REBUILD INDEX"
                  LogIt $SQLREBUILD_LOG " $A of $db $table $index completed" $true  
            
            }
      
                  Catch [exception] {                
                  LogIt  $SQLREBUILD_LOG ("ERROR - " + $_.Exception) $true                
                  LogIt  $SQLREBUILD_LOG "" $false                
                  Continue                
            }  
                  
      
      }

But no index is rebuild now even if page level locking is not disabled

Why ?

Thanks
0
Comment
Question by:bibi92
  • 3
  • 3
7 Comments
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Maybe this helps:

http://mytechmantra.com/LearnSQLServer/Index-cannot-be-reorganized-because-page-level-locking-is-disabled/

You can also use an alter statement for the index:
ALTER INDEX index_name ON table_name SET (ALLOW_PAGE_LOCKS = ON)

Open in new window

0
 
LVL 29

Expert Comment

by:Rich Weissler
Comment Utility
I've had developers use automated tools which build some indexes, or modify indexes for them... and they don't realize the tool is turning off pagelocks or know what it is or care.  Zberteoc's quick alter statement should take care of it for you.  Alternately, you can drill down in Management Studio to the database > Table > Indexes ... then find the specific index you are receiving the error on.  Open it's properties, and select the 'options' page.  You can turn the page locks on for the index there.
Should look something like this.
0
 

Author Comment

by:bibi92
Comment Utility
Hello,

Ok thanks but I have to use this script.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 29

Expert Comment

by:Rich Weissler
Comment Utility
I assume you 'have to use this script' to perform the maintenance.   Can you confirm that allow page locking his been disallowed for the one index?  I guess I should back up one level... are you receiving the error message for all indexes, or just for the one index that you specified after 'ERROR'?
0
 

Author Comment

by:bibi92
Comment Utility
I receive the message only for this index, it's the reason for that I search to exclude index with page locking disallowed.
Thanks
0
 
LVL 29

Accepted Solution

by:
Rich Weissler earned 500 total points
Comment Utility
Oh, I see.   Check the values being returned to your script and ensure you aren't getting a NULL for Index.IsPageLockDisallowed.
0
 

Author Closing Comment

by:bibi92
Comment Utility
thanks
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

772 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

11 Experts available now in Live!

Get 1:1 Help Now