1

I have a situation right now.
There is a database in SIMPLE recovery model.

Note:
1. Compression is not enabled which will be enabled pretty soon. 2. SQL Server 2012 SP3

BackupSize: 8GB (Usually)<br/>
Last Saturday BackupSize: 73GB (yes, 73 !!)

This is because of an application that left 2 transactions open.
Right now the LOG file is 154GB.

Transaction: So far it inserted 90M records to a not so large table)

Question:

  1. In a FULL backup, how much LOG information is recorded if there is an open transaction?
  2. When the FULL backup is restored, does it ROLLBACK the actions by the OPEN transaction?
  3. If so, why does FULL backup even bother to backup the LOG for an OPEN transaction when it is still open after the backup is finished (on a SIMPLE recovery model though).
  4. If I kill the spid from SQL Server, does it need more LOG space to ROLLBACK? We are already out of space here !!
1

In a FULL backup, how much LOG information is recorded if there is an open transaction?

Answer to your question is in This Blog by Paul Randal

When the FULL backup is restored, does it ROLLBACK the actions by the OPEN transaction?

Yep if the transaction has not committed when the full backup completes it will be rolled back when the backup is restored, simply because it does not have idea about state of transaction as backup finished before the transaction committed. Please read below for more details

Understanding Backups in SQL Server

If so, why does FULL backup even bother to backup the LOG for an OPEN transaction when it is still open after the backup is finished (on a SIMPLE recovery model though).

Simply because it does not have any idea about the status of transaction, like whether it will commit or rollback so it does its job of backing up enough transaction logs so that if transaction commits it can bring database to consistent state after backup is restored and if transaction does not commits before backup is finished the backups takes it as uncommitted and when it will be restored their would be no information about changed made by that transaction in restored database.

Read The First Myth pointed out by Paul Randal

If I kill the spid from SQL Server, does it need more LOG space to ROLLBACK? We are already out of space here !!

Yes it does needs space in transaction lo file to rollback. Rollback is mostly single threaded and undoes what the transaction has done.

Rollback: What happens when you kill a session

  • Thank you for the explanations and links...I have been searching around on how to kill the process...The log file size is 134GB and growing, looking at other results, I am afraid to KILL this process which could take more than 4-18hrs to "ROLLBACK" !! – Santhoshkumar KB Apr 24 '17 at 11:21
  • There is no ideal way to kill a process you have to give a kill command. So what this process is actually doing ? – Shanky Apr 24 '17 at 11:28
  • 1
    Someone had the transaction open on a Friday noon and it kept generating records because of bad join and filters. When the application was stopped (with a button on it - luckily app was active), the transaction was released and it was rolled back(?) in no time !! – Santhoshkumar KB Apr 24 '17 at 13:39

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.