June 20, 2011 at 2:30 pm
I have a Script for Re-Org Indexes on all user databases.
Its like this
USE [DB1]
GO
ALTER INDEX [Index_Name] ON [dbo].[EmpInfo] REORGANIZE WITH ( LOB_COMPACTION = ON )
GOThe script was autogenerated by SSIS maiantainance tasks --> ReOrganize Indexes.
So far so good. But some indexes are failing to be re organized coz the ALLOW_PAGE_LOCKS was disabled. So I have to add a explicit SET ALLOW_PAGE_LOCKS = ON for every index.
In other words the above script should look like this
USE [DB1]
GO
ALTER INDEX [Index_Name] ON [dbo].[EmpInfo] (SET ALLOW_PAGE_LOCKS = ON)
GO
USE [DB1]
GO
ALTER INDEX [Index_Name] ON [dbo].[EmpInfo] REORGANIZE WITH ( LOB_COMPACTION = ON )
GO
What the best way to acheieve this ?
There are several hundred DBs and I am on SS2008.
Ideas please.
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
June 20, 2011 at 11:33 pm
I have written a script for you. Select the Result to Text and run the query. Copy and paste the output to a new window and run it. This is one time work.
use master
go
set nocount on
go
sp_msforeachdb 'use [?]
select ''USE [?]
GO
ALTER INDEX ['' + i.name + ''] ON ['' + s.name + ''].['' + o.name + ''] SET (ALLOW_PAGE_LOCKS = ON);
GO''
from sys.indexes i
inner join sys.objects o
on o.object_id = i.object_id
inner join sys.schemas s
on s.schema_id = o.schema_id
where i.allow_page_locks = 0
and o.name not like ''queue_message%'''
go
June 21, 2011 at 6:06 am
Thanks for the reply. I was wondering why did you use the condition
WHERE ALLOW_PAGE_LOCKS = 0
in the script ?
Anyways I will run the script and let you know if this works.
Thanks again
🙂
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
June 21, 2011 at 10:33 pm
This query is used to set allow page lock on. So I used the condition to select index with allow page locks off currently.
This script is not for index rebuild/reorganize.
As I mentioned earlier. You have run this only once. No need to run every time you rebuild/reorganize the index.
Hope this clarified your question.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply