Tuesday, 11 November 2008

Maintenance task fails on reorganizing indexes

The most common problem with in house or bespoke databases is developers forgetting to turn page locks on, now this doesn’t sound like the largest problem, and can easily be fix with a simple query like this.

ALTER INDEX [name_of_index] ON [dbo].[name_of_table] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )

However the error output from job may not clearly tell you what database it came from if you have a large number of databases with similar or same table names in them or just to many to check each one by one then you'll be happy to know there is a quicker way.

So here is another the way around the problem, I have created a query that will list all the databases, tables and indexes that have no page locks making your task of correcting the problem much easier.


SET NOCOUNT ON

DECLARE @CurrentDB NVARCHAR(128)

IF OBJECT_ID('tempdb..#tblServerDatabases', 'U') IS NOT NULL
DROP TABLE #tblServerDatabases
-- create temp table
CREATE TABLE #tblServerDatabases (DBName NVARCHAR(1024))

IF OBJECT_ID('tempdb..#tblDBindexes', 'U') IS NOT NULL
DROP TABLE #tblDBindexes
-- create temp table
CREATE TABLE #tblDBindexes (
DBName NVARCHAR(1024),
[index_name] NVARCHAR(1024),
[index_id] NVARCHAR(1024))

IF OBJECT_ID('tempdb..#tblDBtables', 'U') IS NOT NULL
DROP TABLE #tblDBtables

-- create temp table for finding object id in tables
CREATE TABLE #tblDBtables (
DBName1 NVARCHAR(1024),
[table_name] NVARCHAR(1024),
[table_id] NVARCHAR(1024))


-- insert into temp table and names of databses you don't want to list
INSERT INTO #tblServerDatabases (DBName)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE [name] NOT IN ('model', 'tempdb')

SELECT @CurrentDB = min(DBName)
FROM #tblServerDatabases

WHILE @CurrentDB IS NOT NULL
BEGIN
INSERT INTO #tblDBindexes(
index_name, index_id)
EXEC ('USE ' + @CurrentDB + ' select [name] as index_name, [object_id] as index_id from sys.indexes where allow_page_locks=0')

UPDATE #tblDBindexes
SET DBName = @CurrentDB
WHERE DBName IS NULL

SELECT @CurrentDB = min(DBName)
FROM #tblServerDatabases WITH (NOLOCK)
WHERE DBName > @CurrentDB
END

-- fill temp table with results of query for table object id
SELECT @CurrentDB = min(DBName)
FROM #tblServerDatabases

WHILE @CurrentDB IS NOT NULL
BEGIN
INSERT INTO #tblDBtables(
table_name, table_id)
EXEC ('USE ' + @CurrentDB + ' select [name] as table_name, [object_id] as table_id from sys.tables')

UPDATE #tblDBtables
SET DBName1 = @CurrentDB
WHERE DBName1 IS NULL

SELECT @CurrentDB = min(DBName)
FROM #tblServerDatabases WITH (NOLOCK)
WHERE DBName > @CurrentDB
END

SET NOCOUNT OFF

-- join temp tables and show results
select [dbname],index_name,table_name
FROM #tblDBindexes
join #tblDBtables
ON #tblDBtables.[table_id]=#tblDBindexes.[index_id]

-- Cleanup
IF OBJECT_ID('tempdb..#tblServerDatabases', 'U') IS NOT NULL
DROP TABLE #tblServerDatabases

IF OBJECT_ID('tempdb..#tblDBindexes', 'U') IS NOT NULL
DROP TABLE #tblDBindexes

IF OBJECT_ID('tempdb..#tblDBtables', 'U') IS NOT NULL
DROP TABLE #tblDBtables

This script has already filtered out the system indexes leaving you only the ones created by the designer from this list you should be able to find the database where the index creating the error in the reorganizing exists.

No comments: