CODEWITHSUNDEEP
sql-serverdatabasetransactions
JohanSellberg
JohanSellberg

Reputation: 2503

What happens to connections when taking SQl Server Database Offline?

I have recently tried a big merge of 2 databases. We recreated the schema from Database 2 into Database 1 and created a script to transfer all data from database 2 into Database 1. This script takes about 35 min to run and have transaction handling with:

BEGIN TRANSACTION
...
IF(@@error<>0)
    COMMIT TRANSACTION
ELSE
    ROLLBACK TRANSACTION

The full script is a bit sensitive but here is some SQL that have the same structure: http://pastebin.com/GWJ3ZnkF

We ran the script and all data was transfered without errors. We tested the systems running with the new combined database (removed access rights to the old database).

But as a last task we wanted to take the old database offline to make sure no one used that database. To do this we used:

ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE

This was bad. After this line of SQL code all data in the combined database that we just copied was suddenly gone. I first asumed it wasn't really finished so the "Rollback immediate" sounds like it have performed a rollback on my transaction..

But why? Wasn't the transaction allready committed?

Also I tried running the same script again a few times but after every attempt no data was copied even if it said the script was successfull. I have no idea why... did it remember my offline rollback somehow?

What is really happening to my connections?

Upvotes: 0

Views: 1419

Answers (3)

Remus Rusanu
Remus Rusanu

Reputation: 294387

Sounds like you had a pending transaction uncommitted and you forced it to rollback, loosing some of the work. The rest is explained by how your scripts are structured. Is unlikely your script had a single transaction from start to bottom. Only the last transaction was rolled back, so the database was left now in a state in which it is 'half copied'. Probably your script does various checks and this intermediate state sends the script on the 'ELSE' branches where it does not do the proper work (ie. apparently does nothing).

W/o posting the exact script, is all speculation anyway.

Right now you need to restore the database to a consistent state, the one before your data copy. Use the backup you took before the data move (you did take a backup, right?). for extra credit, make sure your script is idempotent and works correctly on a half-updated database.

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300719

WITH ROLLBACK IMMEDIATE:

All incomplete transactions will be rolled back and any other connections to the database will be immediately disconnected.

Sounds like someone got the 2 databases mixed up or maybe there is an outstanding transaction?.... Can you post your entire script?

Ref: ALTER DATABASE.

Rather than only checking @@ERROR, inspect @@TRANCOUNT as well.

Upvotes: 0

Richard
Richard

Reputation: 30628

I'd double-check to make sure that there are no outstanding transactions. Either go through the file and count the number of BEGIN TRANSACTION vs COMMIT TRANSACTION lines, or add a statement to the end of it to SELECT @@TRANCOUNT to ensure that there are no open transactions remaining.

If your data has been committed, there should be no way it can be lost by disconnecting you.

Upvotes: 0

Related Questions