Transaction Log size

Question

Post reply

Transaction Log size

ruikong

SSC Enthusiast

Points: 197
More actions
August 5, 2010 at 6:36 pm

#225133

Hii everyone....below is my observation of transaction log file and data file.....please answer my questions.....Btn I am a newbie
I created a table in my database and inserted 1000000 rows into the table and monitored the data and log file increase in size.
-->in FULL recovery mode the behaviour is as follows
[both data file and log file increased in size,no change in log file size after i issue CHECKPOINT,
i perform a transaction log backup and issue CHECKPOINT, again there is no change in log file size.
This is unexpected, as according to
http://technet.microsoft.com/en-us/library/ms189085.aspx
transaction log should be truncated.
So I am assuming that Truncation and Freeing space are two different issues and in this case truncation already occured but space is not freed.
And if i need to free my space
I need to issue SHRINKFILE with log file name. Is this a correct assumption?
Is there any way to automatically free the space once Log file is truncated? (TRUNCATE_ONLY is deprecated right)
]
-->in SIMPLE recovery mode again I created a table and inserted rows, again there is a minor hiccup
Thanks everyone.....

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

balu_newbie SSC Enthusiast Points: 109 More actions · Answer 1

Veterans correct me if i'm wrong

For full Recovery mode:

Truncation and freeing space are not two different issues as such, as you assumed. Truncations reduces the size of logical log file. DBCC SHRINKFILE reduces the size of physical log file.

Say, for example after the huge insert, your log file increased in size to 2GB. When you truncate the Log file (by backing up and issuing CHECKPOINT), the size remains 2 GB as the space is already allocated to the Transaction log, but since you truncated the log file, the 2GB is free (keep in mind the whole 2 GB is free just after you perform Truncation) and if you perform further transactions, this 2 GB is utilized. Consider if you did not have performed truncation, and if you perform further transactions, these further transactions utilize additional space and so the size of Transaction Log keeps increasing until you Truncate.

Now consider you did truncate your Transaction log, but you feel that further transactions are small and allocating 2GB to Transaction log is unfair, and now you want to this free space, you use DBCC SHRINKFILE to free the unallocated free pages.

Jeffrey Williams SSC Guru Points: 90314 More actions · Answer 2

Review the article I link to in my signature - it explains all of this very well.

Truncation makes space in the log file available to be reused. Truncation happens differently based upon the recovery model. In full or bulk_logged recovery model, truncation is performed by backing up the log. In simple, truncation occurs after a checkpoint or when the file is 70% full (I think this is the value - it is documented in books online).

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

VastSQL SSC-Dedicated Points: 36144 More actions · Answer 3

When u issue a check point uncommitted transaction from buffer cache are written to disk making in commited..

Truncate reclaims the space but wont release to OS for use..

When u issue dbcc shrinkfile the space is released and you can see that in ur drive free space.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Ratheesh.K.Nair (8/5/2010)
When u issue a check point uncommitted transaction from buffer cache are written to disk making in commited..

No.

A transaction is committed when and only when the COMMIT TRANSACTION statement is reached for the outer transaction. Checkpoint writes all dirty data pages to disk, regardless of whether they belong to a committed or uncommitted transaction. In Simple recovery it also marks the inactive portions of the log (portions not containing any uncommitted transactions and not needed for replication) as reusable.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

ruikong (8/5/2010)
I created a table in my database and inserted 1000000 rows into the table and monitored the data and log file increase in size.
-->in FULL recovery mode the behaviour is as follows
[both data file and log file increased in size,no change in log file size after i issue CHECKPOINT,
i perform a transaction log backup and issue CHECKPOINT, again there is no change in log file size.
This is unexpected, as according to
http://technet.microsoft.com/en-us/library/ms189085.aspx
transaction log should be truncated.

It was. Truncated = space within the log marked as reusable. So the next transaction will be able to use the same space in the log and won't force the log to grow.

So I am assuming that Truncation and Freeing space are two different issues and in this case truncation already occured but space is not freed.
And if i need to free my space
I need to issue SHRINKFILE with log file name. Is this a correct assumption?

Yes. But don't.

Is there any way to automatically free the space once Log file is truncated? (TRUNCATE_ONLY is deprecated right)

No. It's a VERY BAD idea. Don't shrink the log. It'll just force the log to grow again, cause slow performance, log fragmentation, etc.

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/07/27/oh-the-horror-please-stop-telling-people-they-should-shrink-their-log-files.aspx

-->in SIMPLE recovery mode again I created a table and inserted rows, again there is a minor hiccup
[I thought there would be increase in size in log file first and then once i issue CHECKPOINT then there would be increase in data file size.
But only data file grew in size where as there isnt any change in the size of log file.

Probably because there was enough free space in the log that it didn't need to grow.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ruikong SSC Enthusiast Points: 197 More actions · Answer 6

Thanks everyone for prompt response....

-->in SIMPLE recovery mode again I created a table and inserted rows, again there is a minor hiccup
[I thought there would be increase in size in log file first and then once i issue CHECKPOINT then there would be increase in data file size.
But only data file grew in size where as there isnt any change in the size of log file.

btn, this is because i was using select into statement creating table on run.....the statement is minimally logged, so there wasnt any increase in transaction log size (my mistake). i ceated a table instead and inserted rows and there was Transaction log growth as expected.