SQL Server Performance Forum – Threads Archive

Automatic log decresing-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——————- The file configuration for transaction log is 2 MB whit automatic increment whitout limit. Full Recovery Model is used. The problem is that the log starts to increase his size 1 MB, 9MB … 12 MB and later automatically the log size decrease to 1.5MB… and the process repeat again. Why is the log decreasing his size??, it should increase becouse is configurated whit automatic increment. <img src=’http://www.compuaulas.com/performance.gif’ border=’0′ /> http://www.compuaulas.com/performance.gif</a> In the picture you can see that there are two counters: log truncations and log file used size. You can also see that there isn’t any truncation of the log, but the log used size decrease automatically from 16 MB to 2 MB approximately. WHY IS THIS HAPPENING? please help, thanks!!

What you are seeing is not the Log Size but the Log size used. And if you look closely at the data on the perf picture you posted, there are actually a number of log truncations (average 1186). Remember that log truncations are not the same as log shrinks in performance monitoring. You should repeat the test and add the following counters: -Log Files(s) Size
-Log Growths
-Log Shrinks Nathan H. Omukwenyi
MVP [SQL Server]

Hello Nathan. I repeat the test. This time 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. My results are (please see picture): <img src=’http://www.compuaulas.com/performance2.gif’ border=’0′ /> http://www.compuaulas.com/performance2.gif</a> 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.

Just one more tweak to your tests. Do a Full database backup of the NEW adventureworks database and then run the same test exactly. You will see what is expected. The behavour of maintaining and growing the log only kicks in when you tell SQL server that you actually intend to make use of the FULL recovery model by doing at least one full database backup. Nathan H. Omukwenyi
MVP [SQL Server]

Yes!! you are right!!. Thank you a lot!!. But how did you discover that?? It’s was a tricky question, you are the only person who answer correctly. In BOL I found this: "Switching from the simple recovery to full or bulk-logged recovery is possible, though unusual. A database can be switched to another recovery model at any time. If a switch occurs during a bulk operation. the logging of the bulk operation changes appropriately. To maximize data protection after switching from the simple recovery model to the full or bulk-logged recovery model, take a database or differential backup immediately and add periodic log backups to your backup schedule." But AFTER your answer!!. Thanks again!.

]]>