Recently I took the database offline with the option Drop all active connections checked.
After that I couldn't bring it back online due to error Could not redo log record for transaction id on page database. I was able to recover the database with CHECKDB but I want to avoid such a scenario in future
As far as I understand an uncommitted transaction was interrupted which corrupted databases's integrity.
I wonder how can I disable new upcoming connections while letting the active transactions to finish?
Best Answer
When you take a database OFFLINE, SQL Server is no longer protecting the database files from modification by other programs. SQL Server tries to set file ACLs to prevent other users/programs from modifying the files, but having the database ONLINE provides an important additional protection.
Whenever possible perform actions in a way that doesn't take databases OFFLINE or stop the SQL Server service. EG take the database to SINGLE USER mode instead of OFFLINE, and use Backup/Restore instead of moving database and databse files.
And if you do take the database OFFLINE, ensure that you have a backup first.