snippetsqlMinor
What happens when CREATE INDEX gets interrupted (manually or accidentally)?
Viewed 0 times
createwhataccidentallymanuallywhenhappensindexinterruptedgets
Problem
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?
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?
Solution
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
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
Context
StackExchange Database Administrators Q#62588, answer score: 5
Revisions (0)
No revisions yet.