August 21, 2008 at 5:20 pm
Hi,
Suppose the following scenario
1- User using an application
2- User begins a transaction which supposed to insert ten inter dependent records in different insert statements. (After complete 10 insertion user will commit the data)
3- Five records were inserted when application crashed. User was not been able to commit or roll back the transaction.
So my assumption is, the five inserted records are still present in the Transaction Log.
So if my assumption is correct when will these records will be flushed out of the Transaction Log?
August 21, 2008 at 6:20 pm
...is the five inserted records are still present in the Transaction Log.
Yes. See this document on when log records are created in the transaction log.
... when will these records will be flushed out of the Transaction Log?
Space occupied by the log records of the 5 insertions will be marked available for reuse immediately if there are no other transactions occupying the same virtual log file, if your database is running the simple recovery model.
If your database is running in full or [bulk-logged[/b] recovery models, the space is released only after a transaction log backup is made.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
August 21, 2008 at 8:37 pm
"If your database is running in full or [bulk-logged[/b] recovery models, the space is released only after a transaction log backup is made."
Ok this comment is confusing me.
Suppose a user is in the middle of a 1000 inserts transacion suppose that it will take 30 minutes.........suppose after 15 minutes of the transaction start (500 inserts are made and transactaion is not yet commited) someone start taking the TLog backup (which is completed say in 3 minutes)..........so now according to your comment sql server will free space of these 500 non commited inserts from the Log file???.......
I mean while taking the TLog backup .... how will it differentiate that whether some uncommited transactions present in the TLog will never commit (due to application crash in the middle of transaction) and some uncommited transactions present in the TLog will commit after some time (because they are part of a current transaction that started before TLog backup and will complete after the completion of backup).??
am i making some sense or acting stupid??
August 21, 2008 at 11:30 pm
In the scenario,which you have mentioned that the application crashes in the middle of the transaction.500 records are inserted and it is in th non committal state.The transaction log will have the log of these 500 records and at the same somebody is performing the log backup.The 500 record log space will be free only after the backup is done.The backup process does not need to differentiate between records that are not comitted and the ones which are going to get comitted.When we take trnsaction backup there is always the tail portion of the transaction log which is remaining to be backed up.If the application crashes in between,restore process takes place.Before restoring,we nee dto perform the backup of the tail portion of transaction log.During the restore process,it checks whether the transaction is commited or not.If it is comitted then it is rolled forward and if not then rolled back.That is in your case,as teh transaction was not comitted it will be rolled back.I hope i am making sense
August 22, 2008 at 2:08 am
Sorry for the confusion.
The active part of a transaction log is the portion that cannot be truncated. If you have the following transactions:
A B C D E F G H
and A is not committed/rolled-back, while B to H have been committed, the space occupied by transactions A to H cannot be truncated, regardless of the database recovery model, regardless of whether the transaction log has been backed up. This is the active portion of the log.
There is then a transaction log backup, which doesn't care about the state of the transactions. If you back up the above transaction log, it will contain transactions A to H. But because this is the active part of the log, it will not be truncated i.e. the space won't be marked available for new transactions.
So that's 2 different things. There's also another point if you're running transactional replication, but let's keep that out for now.
Thus, my earlier statement was wrong. Partially. The moment the application crashed after the 5 records were inserted but not committed (since the commit takes place only after 10 inserts), the space occupied by the 5 records are not available for use immediately. SQL Server will eventually detect that the connection to which the transaction belongs to no longer exist, and automatically roll back the transaction. Only then will the space be available for reuse.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
August 22, 2008 at 7:25 am
There you go....now you are successful in satisfying me:)
Now the next logical question is ....ASSUMING THAT SQL SERVER ALSO STORES THE CONNECTION INFO OF EACH TRANSACION IN THE LOG FILE...........when will sql server perform that activity (that connection is lost & roll back transactions)??when checkpoint occurs?when we take backup?after regular interval? when?
August 22, 2008 at 8:03 am
ASSUMING THAT SQL SERVER ALSO STORES THE CONNECTION INFO OF EACH TRANSACION IN THE LOG FILE...........
Sort of, see here.
when will sql server perform that activity (that connection is lost & roll back transactions)?
See the 'Errors During Transaction Processing' section in the 'Controlling Transactions' topic in books online (look for 'rolling back transactions' in the index).
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
August 22, 2008 at 8:12 am
Thanks alot Ray, you have been a great help and btw the solution that you gave me for getting space available after shrinking worked for me, i tried it last night and it was awesome......and your website of backup & restore is just TOO GUDDDD.........YOU ROCK
Thanks,
Usman
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply