Automatic log decresing space used size in AdventureWorks

Hello all, I was making test with AdventureWorks about monitoring the log size and I run script:
-
/*
** This script is used to fill the Northwind transaction
*/

USE AdventureWorks

WHILE 1 = 1
BEGIN
UPDATE Production.Product
SET Name = Name
WAITFOR DELAY '000:00:00:999' --simulates a somewhat realistic OLTP environment
END-

I used a NEW AdventureWorks, just that I changed the recovery model to FULL RECOVERY MODEL.

By default Adventure Works use a log initial file size of 2 MB with automatic increment of 16MB.

The problem is that the log starts to increase his size 0.7 MB, 1.4 MB ... 1.8 MB and later automatically the log used size decrease to 0.5MB... and the process repeat again. Why is the log decreasing his space used size?, it should increase becouse is configurated whit automatic increment.

I support my test with performance monitor, my results are (please see picture):
http://www.compuaulas.com/performance2.gif

1. There is not log file growths, witch is strange becouse the log should growth!! and the log size didn't change his size.

2. The LOG SPACE USED changed. It's starts to growth but suddenly the space used decrease automatically (See Fuchsia line in the picture).

3. After a while, the log started to growth but instead still growing, TRUNCATIONS started to occur, and it's shouldn't be happening.

4. There is not file shrinking.

please help!!, thank you a lot.

Hi !

I found the same problem in SQL 2000. I used log truncations and log used space (kb) counters to see what was happening. I also used DBCC loginfo to see VLFs, but I still have no solution for that...

[]'s

Dennes

|||

Hi!

I found the answer.

The recovery model full is useless until we made a full backup. SQL Server knows that and keep using recovery model simple until that.

[]'s

Dennes