Error on creating index on table in sql server, how to fix it?

Question

On creating clustered index on table in SQL SERVER 2008 (LIVE DB) got above error. Total records in table is 1046789.

I have created same index on test db with same number of records and it successfully created.

Thanks.

Answer 1

I think your question is "what can I do to fix this?"

Making that assumption:

Perform the following tasks to alleviate the problem:

Locate the transaction that is holding the lock on the required resource, if possible. Use sys.dm_os_waiting_tasks and sys.dm_tran_locks dynamic management views.

If the transaction is still holding the lock, terminate that transaction if appropriate.

Execute the query again.

If this error occurs frequently change the lock time-out period or modify the offending transactions so that they hold the lock for less time.

And if that doesn't work:

In this scenario following changes must be done in the offending transaction: 1) Modify the Transaction use query hints (use RECOMPILE,MAXDOPhints) 2) Run big Transaction in smaller transactions. 3) Upgrade Hardware if possible.

From here and here

Answer 2

Use following commands to find out exactly what processes are holding the exclusive locks on your table. FYI, creation of any type index must put exclusive locks on a table due to the complexity of the task. In clustered indexes the data are re-arranged physically on the disk. No access will be available on the table unless you specify the option ONLINE = OFF at index creation.

Before running, REPLACE DATABASE_NAME, DB_ID, OBJ_ID and SPID with real integer values.

CREATE TABLE tempdb..#temp_lock
(spid int, dbid int, ObjId int, IndId int, Type nvarchar(30), Resource nvarchar(100), Mode nvarchar(10), Status nvarchar(30))

Use DATABASE_NAME

GO

insert into tempdb..#temp_lock exec master..sp_lock

select spid, ObjId, mode, name from tempdb..#temp_lock as a inner join [test2]..sysobjects as b on a.ObjId= b.id where a.dbid = DB_ID

Identify The Spid which is holding "X" mode locks on your table. Note down ObjId and SPID values from previous query.

Find out what the locking Spid is executing.

dbcc inputbuffer (SPID)

or more detailed:

DECLARE @Handle binary(252)

SELECT @Handle = sql_handle FROM master..sysprocesses WHERE spid = SPID

SELECT * FROM ::fn_get_sql(@Handle) Go

Decide whether to terminate the process or commit the transaction if you know who (user) is executing.

KILL SPID

Create the index again.