El día de hoy estábamos tratando en hacer el plan de mantenimiento de la base de datos de uno de nuestros clientes con el Maintenance Plan Wizard de SQL Server 2008. Crear el plan a través de esta herramienta es bastante sencillo, pero las pocas ocasiones que lo he utilizado siempre me encuentro con algo en el momento de ejecutar el plan de mantenimiento. Dentro de las opciones del plan de mantenimiento seleccionamos la opción Reorganize Index, el cual cuando se iba a ejecutar nos presentaba un error que decía:

The index “INDEX_NAME” (partition 1) on table “TABLE_NAME” cannot be reorganized because page level locking is disabled.

El cual luego de revisar un poco en internet y la ayuda de SQL Server encontramos que se puede solucionar con la siguiente instrucción SQL:

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

Y bueno es una excelente solución, hasta el momento en que seguimos ejecutando el plan de mantenimiento y el error vuelve a aparecer una y otra vez para diferentes índices, así que decidimos buscar una nueva solución, en la cual gracias al poder de T-SQL y de internet construimos el siguiente query que nos permite realizar un SQL dinámico para cada uno de los índices con el mismo problema:

SET NOCOUNT ON
DECLARE @DBName nvarchar(50), @INName nvarchar(50)
DECLARE @ODBName nvarchar(50), @OINName nvarchar(50)
Declare @execstr nvarchar(200)
--PRINT '-------- Index with page level locking disabled --------'
DECLARE Index_cursor CURSOR FOR
SELECT idx.Name AS InName, sch.Name + '.' + obj.Name AS DBName
FROM sys.indexes idx left outer join sys.objects obj ON
obj.object_id = idx.Object_id left outer join sys.schemas sch ON
obj.schema_id = sch.schema_id
WHERE allow_page_locks=0 and obj.type='U'
-- Select only allow_page_locks 0 and User Tables
OPEN Index_cursor
FETCH NEXT FROM Index_cursor
INTO @INName, @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @DBName +' ' + @INName
--PRINT @INName
SET @ODBName = ltrim(rtrim(@DBName))
SET @OINName = ltrim(rtrim(@INName))
SELECT @execstr = 'ALTER INDEX ' + @OINName + ' ON ' +
@ODBName + ' SET (ALLOW_PAGE_LOCKS = ON)';
EXEC (@execstr);
FETCH NEXT FROM Index_cursor
INTO @INName, @DBName
END
CLOSE Index_cursor
DEALLOCATE Index_cursor

Luego de haber solucionado el problema de los índices nos encontramos con que el tamaño del log de eventos de la base de datos era demasiado grande, y excedía el tamaño disponible en la unidad de disco donde se encontraba, así que luego de buscar un poco en internet nos encontramos con este post muy útil para solucionar este problema.