newbie - re: log truncate after bkup

[IT247]

Hello
I am not clear about when the log truncation happens.
If i do a backup log, does the log truncate automatically or, does it require a switch?
right now, i did a dbcc loginfo which shows some 230 vlf's
-only one of them is active.
I then did backup log and rechecked with dbcc loginfo - no change! I expected it to show less vlf's. Or maybe i am not understanding what happens when the log is backed up or how they work

can someone clarify this for me?

thx very much

 

[mrdenny]

When you backup the log the log will be truncated to the first active log entry. If you put the no_truncate flag in the backup log command then the commited transactions won't be removed from the log.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[IT247]

thx Mr Denny
I read up on the log architecture etc from BOL.
I understand it better now. However, I don't see why SQL would create so many vlf's and have just one of them active.

 

[mrdenny]

It all depends on how many concurrent transactions you have that are not checkpointed.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com