3

It takes hours or even days to create an index for a table of millions of rows.

What happens to the database / data when CREATE INDEX gets interrupted? Such as when I manually used Ctrl-C or when there's an accident like a power-failure.

Does it do harm to the database / data?

If so, how do we recover from such an interruption?

1 Answer 1

5

Index creation is done within a transaction. If you interrupt it, SQL Server will rollback the transaction. This might take a while, but it's harmless. Of course, there are locks held while the transaction is active so this can impact your server's performance.

If you lose power in the middle, SQL Server will automatically rollback the partially completed transaction when the server boots. Again, there's a performance hit but there's no harm done to your data.

http://technet.microsoft.com/en-us/library/ms188317(v=sql.105).aspx

http://technet.microsoft.com/en-us/library/ms190925.aspx

http://technet.microsoft.com/en-us/library/aa224747(v=sql.80).aspx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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