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?