Difference between Active part,Inactive part and Free space

Question

What is the difference between Active part,Inactive part and Free space in a transaction log file?

Answer 1

Hi Vinay,

 

Click the below link to understand the complete log Architecture

http://thiruna.blog.com/2010/11/23/understanding-logging-and-recovery-in-sql-server/

 

 

---

Regards, RM Thirunavukkarasu MCP, MCITP, ITIL V3 | "http://thiruna.blog.com " |Click "Propose As Answer" if solution is helpful to you.

Answer 2

Hello Ramasamy,

Thanks a lot for your reply. I still have some questions.

Active part means-Part of the transactional log file which hasn't been backed up yet

In active part means-Which has been backed up and not yet turncated.

I am still confusing about Free space---To My under standing, free space means used space will be truncated and released it for further activities after completion of log backup(If DB will be in FULL recover) or a check point and truncates the file(If DB is in SIMPLE recovery)

This is what I understand. Is this Correct? Please let me know am I going in right path and corrects me if not.

Thanks again for your time and explanation!!

 

Regards,

Vinay Vutukuru.

e-mail:[e-mail]

Answer 3

Hi Vinay,

I believe that there may be condition when few transaction is still running and others may have been completed, so once any log backup is performed it actually marks inactive to those transaction which have been completed and those transaction which are still running are marked with active, at the moment when log backup completed you can raise the shrikfile statement, it will release the inactive space to OS.

Hope it will help you.

Raj

 

 

 

---

Thanks & Regards Rajnish Awasthi, MCTS-SQL Server 2008

Answer 4

Active Virtual Logs are Open Transactions, they will be still active even if you back it up or not, they not truncated when you backup your Transaction Log file. When you backup your Transaction Log file, Passive Virtual Logs in the Transaction Log file are truncated.

You can find more information about Transaction Log's logical and physical structure at Books Online:

http://msdn.microsoft.com/en-us/library/ms189085.aspx

---

Ekrem Önsoy - MCDBA, MCITP:DBA & DBD, MCSD.Net, SQL Server MVP | [e-mail]

Answer 5

Please refer to my blogs:

http://blogs.msdn.com/b/suhde/archive/2009/03/16/shrinking-the-transaction-log-files-in-sql-server.aspx
http://blogs.msdn.com/b/suhde/archive/2009/07/18/revealing-the-secrets-truncating-a-transaction-log-file.aspx

Let me know if you ever have any questions about Log Truncation and Shrinking of TLogs after reading these blogs.

My email id is [e-mail]

---

Suhas De
--------------------------------------------------------------------------------
Please mark answered if your question is completely answered; vote it as helpful to help others find a solution quicker.
This posting is provided "AS IS"; with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog: http://blogs.msdn.com/b/suhde

Answer 6

Active Porttion: All the transactions are still going on, as Ekrem said, whether you backup or not, the active portion will still remain until the transactions are completed.

Inactive Portion: All the committed transactions are present in this portion of the transaction log, where the transactions are already written to the data files. However, it will be still there in the t-log consuming some amount of space until a transaction log backup is initiated. Point to be remember here is that when you initiate a t-log backup, the inactive portion of the t-log is removed, which create space for the active transactions; however the t-log size remains unchanged (unless your database is in Simple Recovery Model).

So, how to release the space in transaction-log? You need to shrink the file , remember, it is a bad advice. Should never do that, unless thats the only option you have.

The other way is to initiate more frequent t-log backup, which will, in some extent, reduce the t-log size, however, if there will be many active transactions / bulk-load operations, it can still grows.

If the database is in Simple Recovery model, the t-log size is maintained by the SQL Server. Whenever the database backup is initiated, a checkpoint is issued, and the inactive portion of the t-log is truncated, so the t-log size is reduced. However, the active portion of the  log still remains same.

Hope, this may help.

---

SKG: Please Marked as Answered, if it resolves your issue.

Answer 7

Thanks to all for spending your time on answerign my question.Now, it is clear for me!

Regards-Vinay Vutukuru.