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 !!
Santhoshkumar KB
  • 581
  • 2
  • 9
  • 22

1 Answers1

2

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 SQL Server does not have idea about "state of transaction" when the backup finished before the transaction committed/completed. 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).

As stated above because it does not have any idea about the state of transaction, like whether it will commit or rollback and since transactions in SQL Server are first written in transaction log before changes are made to pages the backup 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 the 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 log file to rollback. Rollback is mostly single threaded and undoes what the transaction has done.

Rollback: What happens when you kill a session

Shanky
  • 19,148
  • 4
  • 37
  • 58