Enabling page level locking

Sometimes index rebuild task will be failed due to the following issue:

Description: Executing the query "ALTER INDEX [IDX_XXX_OF_DATE] ON [dbo].[DI..." failed with the following error: "The index "IDX_XXX_OF_DATE" (partition 1) on table "XXXX_OF_CF" cannot be reorganized because page level locking is disabled.".

Solution

Enable page level locking…

To find the disabled page level locking indexes, please execute the given query:

SELECT * FROM sys.indexes WHERE allow_page_locks = 0

Copy the index names from the above output and enable locking by executing the below query:

Alter Index <index name> On <table name> Set (ALLOW_PAGE_LOCKS = ON)

If we want to do the same for multiple indexes in single database at a time, please execute the following:

Use <dbname>

GO

select 'alter index ['+s.[name]+'] on ['+t.[name]+'] Set (ALLOW_PAGE_LOCKS = ON);' from sys.indexes as s inner join sysobjects as t on s.[object_id] = t.[id] where s.[allow_page_locks] = 0 and is_unique = 0

Copy the given OUTPUT and paste it into new NEW QUERY WINDOW and execute it.

Check for disabled indexes and re-run the job.

TSQL - Code to find used columns in database objects in MS SQL Server

Sometimes, you find that you have columns that are not being used. The data no longer is collected or relevant. In this case, you need to remove some columns from your database. Please use the following code to find used columns in database objects.

SELECT TOP 100 PERCENT
so1.name AS ObjectName,
CASE so1.type
WHEN 'P' THEN 'Procedure'
WHEN 'V' THEN 'View'
WHEN 'FN' THEN 'Function'
WHEN 'U' THEN 'Table'
WHEN 'TR' THEN 'Trigger'
WHEN 'IF' THEN 'Inline Function'
WHEN 'TF' THEN 'Table-Valed Function'
ELSE so1.type
END AS ObjectType,
DB_NAME() AS DatabaseName,
USER_NAME(so.uid) AS OwnerName,
so.name AS TableName,
c.name AS FieldName
FROM
sysobjects so,
sysobjects so1,
sysdepends d,
syscolumns c
WHERE
so1.xtype IN ('P','FN','IF','TF','V','TR') --('P', 'V', 'FN', 'TR')
AND d.id = so1.id
AND d.depid = so.id
AND so.id = c.id
AND d.depnumber = c.colid
ORDER BY
so.name
-----------------------------
Your comments are always accpeted.

Thanks,
Satish Kumar
+91 9966 710 244
Hyderabad.