Creation of spatial index locks the table for hours

Question

I used this code to create a spatial index in SQL Server 2008 R2:

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE SPATIAL INDEX SPATIAL_COMPANIES_GEO ON dbo.ITEMS(GEO) USING GEOGRAPHY_GRID
 WITH( 
          GRIDS  = ( LEVEL_1  = HIGH, LEVEL_2  = HIGH, LEVEL_3  = HIGH, LEVEL_4  = HIGH)
        , CELLS_PER_OBJECT  = 100
        , STATISTICS_NORECOMPUTE = OFF
        , ALLOW_ROW_LOCKS = ON
        , ALLOW_PAGE_LOCKS = ON
     )
GO
ALTER TABLE dbo.COMPANIES SET (LOCK_ESCALATION = TABLE)
GO

This query executes in 5 seconds, but then the table ITEMS seems to be locked for hours. In Management Studio I cannot expand the Indexes folder for ITEMS and any query to that table times out. Furthermore, if I restart the SQL Server service the spatial index I just created disappears.

I've tried this on my production database with 3.000.000 records and also on my test database with 300.000 records with the same result.

Is this normal? What I should do to create the index without locking the database?

Answer 1

You start a transaction but don't commit the 2nd one, so the table will remain locked.
The SQL Server restart will rollback the transaction containing the CREATE INDEX

Remove both BEGIN TRANSACTION calls and theCOMMIT
(or add a final COMMIT TRAN)