We are running database maintenace with indexing the system, indexing fails due to locking?

We have been trying to do re-indexing in our system, there are users still using the database during the indexing. While doing index for certain tables there is failure of indexing this seems to be due to deadlocks

the indexing script we use is:

```
-------- REBUILDING THE INDEXES----------------------

CREATE TABLE #TableIDs (Tablename nvarchar(200),IndexName nvarchar(1000))
--Database to be indexed--
INSERT INTO #TableIDs (Tablename, IndexName)
SELECT a.object_id,name
FROM sys.dm_db_index_physical_stats (DB_ID(N'RAL'), NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
Where avg_fragmentation_in_percent > 20 and name is not null and OBJECT_SCHEMA_NAME(a.object_id) = 'dbo'
And object_name(a.object_id) not in (SELECT
OBJECT_NAME(p.object_id) BlockedObjectName
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
Where db.name = 'RAL')

declare c cursor for select object_name(Tablename) as Tablename ,IndexN

If the requirement is to rebuild the indexes while letting users continue to work, you will find this extremely challenging, if not impossible, to meet if you can't do online index rebuilds, which are only available in Enterprise Edition.

There are really only two types of solutions:

• Use online index rebuilds.

• Do index REORGANIZE instead of REBUILD -- reorgs are always online operations, but may not do as good a job as a rebuild, and may run more slowly in comparison.

Regardless, instead of rolling your own scripts, I would highly recommend using an already-built-and-tested solution such as Ola Hallengren's maintenance scripts, which will intelligently maintain your indexes based on their current state of fragmentation. The indexes may not actually need to be rebuilt all the time.

StackExchange Database Administrators Q#29616, answer score: 7