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.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Automatic log decresing space used size in Adventu

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 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.

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.
Go to Top of Page
   

- Advertisement -