November 26, 2008 at 4:12 pm
Hello,
Iam Getting an error while doing reindexing as below.
Msg 2552, Level 16, State 2, Line 1
The index "CIX_ServiceInstances" (partition 1) on table "dta_ServiceInstances" cannot be reorganized because page level locking is disabled.
could you please tell me what to do to enable this page locking?
Iam using the below script for reindexing?
--* Originally created by Microsoft */
--/* Error corrected by Pinal Dave (http://www.SQLAuthority.com) */
-- Specify your Database Name
USE Your DB
GO
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename VARCHAR(128);
DECLARE @execstr VARCHAR(255);
DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @frag decimal;
DECLARE @maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT CAST(TABLE_SCHEMA AS VARCHAR(100))
+'.'+CAST(TABLE_NAME AS VARCHAR(100))
AS Table_Name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table.
CREATE TABLE #fraglist (
ObjectName CHAR(255),
ObjectId INT,
IndexName CHAR(255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity decimal,
BestCount INT,
ActualCount INT,
LogicalFrag decimal,
ExtentFrag decimal);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @tablename;
WHILE @@FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @tablename;
END;
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(VARCHAR(15),@frag)) + '%';
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')';
EXEC (@execstr);
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag;
END;
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
GO
November 26, 2008 at 4:19 pm
ALTER INDEX < Index name > ON < Table Name >
SET (
ALLOW_PAGE_LOCKS = ON
)
GO
Also you may want to revise that script. Both showconting and indexdefrag are deprecated in SQL 2005. The replacements are sys.dm_db_index_physical_stats and ALTER INDEX .... REORGANIZE
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 26, 2008 at 4:36 pm
Thanks Gail,
is that must and should to enable page level locking for all the indexes?
what the purpose of enabling and disabling page level locking on indexes?
November 27, 2008 at 12:43 am
madhu.arda (11/26/2008)
Thanks Gail,is that must and should to enable page level locking for all the indexes?
No. It will enable page locks for the index specified in the alter index statement
what the purpose of enabling and disabling page level locking on indexes?
It disables some lock escalation. With page locks disabled, SQL can only lock at the row or table level for that index
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 27, 2008 at 2:58 am
Be careful with changing the index. Some applications need this option diabled. I remember with a SAP database that it contains a couple of tables where page locking must be disabled and even if you change it, SAP will change it back. I would say ask your software supplier first before making any changes.
[font="Verdana"]Markus Bohse[/font]
November 27, 2008 at 5:51 am
There was initially a 'bug' in SQL 2005 where the default of PAGE_LOCK was OFF. With SP2 this setting was corrected and set to default ON. So be sure that your server and clients-tools are on SP2 or you always have to check this setting manually.
Similar to what MarkusB said, we also have an application that requires this setting to OFF for a few indexes. When it is ON a lot of locking will occur and the performance falls down. We added a scheduled job to change the setting to ON just before the REORGANIZE of indexes and a job to set it back to OFF afterwards.
November 27, 2008 at 6:06 am
HanShi (11/27/2008)
When it is ON a lot of locking will occur and the performance falls down. We added a scheduled job to change the setting to ON just before the REORGANIZE of indexes and a job to set it back to OFF afterwards.
Actually, with it off you can get far more locking as SQL has to either lock at the row level (meaning lots more locks) or at the table level (less locks, but far more restrictive)
Personally I prefer letting SQL pick the best locking granularity itself and tune my queries and indexes of the locking's getting excessive.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 27, 2008 at 6:13 am
GilaMonster (11/27/2008)
HanShi (11/27/2008)
When it is ON a lot of locking will occur and the performance falls down. We added a scheduled job to change the setting to ON just before the REORGANIZE of indexes and a job to set it back to OFF afterwards.Actually, with it off you can get far more locking as SQL has to either lock at the row level (meaning lots more locks) or at the table level (less locks, but far more restrictive)
Personally I prefer letting SQL pick the best locking granularity itself and tune my queries and indexes of the locking's getting excessive.
Hi Gail,
I agree to let SQL determine the best approach, but unfortunately I am not able to change the application myself. The application provider told me they are busy with a redesign, so untill then I have to leave this setting... :crying:
November 28, 2008 at 9:15 am
1) since this is SQL 2005, the best practice is to use ALTER INDEX ... to perform index maintenance instead of DBCC INDEXDEFRAG...
2) I don't know why you need to involve application developers to change existing index options such as allow page locks. Note that you still need to be concerned about possible third-party app issues such as the one someone mentioned with SAP. I will say that they have bad design if their app performs improperly with page locking enabled. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 2, 2009 at 3:40 pm
Hi hanshi,
as you said We added a scheduled job to change the setting to ON just before the REORGANIZE of indexes and a job to set it back to OFF afterwards.
could you plz send the script that you use for enabling the pagelevel locking just before reorganize and set it back to OFF afterwards
thanks
January 3, 2009 at 7:57 am
madhu.arda (1/2/2009)
Hi hanshi,as you said We added a scheduled job to change the setting to ON just before the REORGANIZE of indexes and a job to set it back to OFF afterwards.
could you plz send the script that you use for enabling the pagelevel locking just before reorganize and set it back to OFF afterwards
thanks
Hi madhu.arda,
When you have only a few indexes that need to have the setting changed, you can hard-code them in your script.
Use {database}
GO
begin transaction
ALTER INDEX [index_name] ON [table_name] SET (ALLOW_PAGE_LOCKS = ON)
commit transaction
If you have many indexes that need to be changed, you can change it using dynamic SQL. First select all the indexes with the setting OFF and store the names in a (new created) table. Loop this table and execute dynamic SQL to change the setting of each index. After the reorganization is finished, loop the table again and change the setting back to OFF. Finally delete the table with index names.
January 3, 2009 at 10:20 am
Or you could just rebuild them 🙂
Rebuilding is fine with page level locking disabled... it's just reorg that fails.
Rebuild online if possible.
~BOT
Craig Outcalt
January 28, 2010 at 1:44 pm
GilaMonster (11/27/2008)
madhu.arda (11/26/2008)
Thanks Gail,is that must and should to enable page level locking for all the indexes?
No. It will enable page locks for the index specified in the alter index statement
Hey Gail, Does SQL server always set the Page level lock for the indexes and not for the table? Is there a way we can set allow page locking or row locking for the table and not for the indexes?
March 7, 2012 at 1:47 am
i'm hitting this error during an index rebuild/reorg job:
Msg 2552, Level 16, State 1, Line 1
The index "VBDATA^0" (partition 1) on table "VBDATA" cannot be reorganized because page level locking is disabled.
i would like to detect for this in the system tables and force it to be ALTER INDEX REBUILD in my script to create the appropriate ALTER INDEX statements.
where can i find the column which sets PAGE LEVEL LOCKING in the system tables?
March 7, 2012 at 4:25 am
In sys.indexes you will find the column "allow_page_locks".
But why do you post this question in a 3 year old thread instead of creating a new one?
[font="Verdana"]Markus Bohse[/font]
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply