0

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?

Improve this question
1

Recently I took the database offline with the option Drop all active connections checked.

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.

Improve this answer
0

To stop upcoming new connections while letting the active transactions finish, you can PAUSE the SQL Server service in "SQL Server Configuration Manager."

pause sql server

Please see this article for more details.

To deal with the possible corruption issue you have with your database, you could either restore it from a previous backup or attempt to fix it by doing a

DBCC CHECKDB (DB_NAME, REPAIR_REBUILD);
Improve this answer
  • As far as I see this is possible for the whole server. Is there an option to do this on single database. – Rico W Nov 27 '20 at 9:43
  • How did you came to conlcusion that repair_rebuild will solve the issue? There was no checkdb output indicating that would be required. – Shanky Nov 27 '20 at 9:46
0

Dropping the active connection should not be what causes your database to get corrupted.

When you bring a database online, SQL will replay the log (redo/undo phase) and in a context like the one you discribed, I guess SQL would redo the part of the transaction that was done before the session was killed, but it will also roll it back as the transaction was not commited.

Have you done something else after putting the DB offline ?

Improve this answer
  • I haven't done anything between clicking on Take offline and Bring online in MSSM. Afaik no user was active on this db. Is there a way to investigate what caused the issue? – Rico W Nov 27 '20 at 13:30
  • It can in some cases specially if your kill the connection which forces a rollback and you immediately shutdown the system. Your disk was not able to save information and database is left with orphaned transaction – Shanky Nov 27 '20 at 13:43
0

I would read the errorlog file from that time fram. Over and over again. With a magnifier. It isn't normal what you see. You want to determine why that happened. Without any clues in the form of error messages, you (and we) are only guessing.

Perhaps the disk was full during recovery so the ldf file couldn't grow to acommodate the needed recovery work?

Or, as Shanky suggested, perhaps you had double-unluck so a critical write operation was lost at the physical level?

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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