Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Dec 16 '11 at 16:36

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 4 down vote accepted

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)

share|improve this answer
    
There is a COMMIT before the second BEGIN TRAN. I don't think setting changes can be rolled back anyways, but he has it encapsulated! –  JNK Dec 16 '11 at 16:45
    
@JNK: I was scanning for COMMIT TRANSACTION to match BEGIN TRANSACTION. This is definitely SSMS generated code –  gbn Dec 16 '11 at 16:51
    
Yes it is a SSMS generated code. anyway i removed the ALTER TABLE statement as really i can't understand why SSMS added it... THANK YOU GUYS! –  Alex Dec 16 '11 at 17:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.