Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
hernan93
Starting Member
3 Posts |
Posted - 2006-08-25 : 09:39:18
|
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 AdventureWorksWHILE 1 = 1BEGINUPDATE Production.ProductSET Name = NameWAITFOR DELAY '000:00:00:999' --simulates a somewhat realistic OLTP environmentEND-------------------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.gif1. 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. |
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-08-26 : 17:16:44
|
The log will continue to truncate on checkpoint until a full backup is taken.Until there is a full backup the log backups can't be applied to anything so I guess that the system assumes there's no point keeping the data.It happens to quite a few systems - people think things are ok after implementation. Leave things running then after a while want a dev copy so take a backup. A little while later the system crashes due to running out of log space.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|