September 16, 2009 at 3:22 pm
SQLServer2008.
Supposed to be a Terabyte database. Testing now. I load 100Mb, 150Mb, 300Mb of data and
it gets replicated successfully. The db is now 4GB. But the log file size goes well
upto 24GB. The log file backup has been configured to run every 5 minutes. The log file is
getting backed up every five minutes successfully. When the load takes place the
log file gets backed up too. During the load the log file backup size reaches 3 or 4GB.
Once the job load / replication is completed the log file backup size comes down to 12Mb.
But the Log file still remains at 24GB. Recovery mode = FULL.
Is this a normal behaviour.
I have set the logsize size max to 512GB; Could that be the reason.
DBCC SQLPERF(logspace) shows Log Size (MB) = 24296 , Log Space Used (%) = 1.57 , Status = 0
BACKUP LOG WITH TRUNCATE_ONLY is deprecated in SQL Server 2008. Hence i tried
DBCC SHRINKFILE(2,1024) and it resized to 14GB from 24GB.
DBCC SQLPERF(logspace) shows Log Size (MB) = 14517 , Log Space Used (%) = 1.63 , Status = 0
I cannot set the Recovery mode to SIMPLE since Replication is taking place. I hope i am
correct in saying this.
To repeat my question.
Is this expected behaviour? Why the log file goes to 24GB when the data files being loaded are < 1GB?
September 17, 2009 at 7:04 am
gk-411903 (9/16/2009)
SQLServer2008.Supposed to be a Terabyte database. Testing now. I load 100Mb, 150Mb, 300Mb of data and
it gets replicated successfully. The db is now 4GB. But the log file size goes well
upto 24GB. The log file backup has been configured to run every 5 minutes. The log file is
getting backed up every five minutes successfully. When the load takes place the
log file gets backed up too. During the load the log file backup size reaches 3 or 4GB.
Once the job load / replication is completed the log file backup size comes down to 12Mb.
But the Log file still remains at 24GB. Recovery mode = FULL.
Is this a normal behaviour.
I have set the logsize size max to 512GB; Could that be the reason.
DBCC SQLPERF(logspace) shows Log Size (MB) = 24296 , Log Space Used (%) = 1.57 , Status = 0
BACKUP LOG WITH TRUNCATE_ONLY is deprecated in SQL Server 2008. Hence i tried
DBCC SHRINKFILE(2,1024) and it resized to 14GB from 24GB.
DBCC SQLPERF(logspace) shows Log Size (MB) = 14517 , Log Space Used (%) = 1.63 , Status = 0
I cannot set the Recovery mode to SIMPLE since Replication is taking place. I hope i am
correct in saying this.
To repeat my question.
Is this expected behaviour? Why the log file goes to 24GB when the data files being loaded are < 1GB?
yes it is expected behaviour, Other people have written very good articles on transation logs.
remember that that a backup log, just frees up space in the log file, it doesnt alter the physical size of the log file, and a shrinkfile will only reduce it to the last active virtual file in the log file.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 17, 2009 at 7:32 am
to add to what fox has said, whenever a log file grows to a certain size, the size is not reduced automatically unless you shrink the file (which is a bad option). when you backup the log, all the active entries are backed up in an external file and these entries are truncated leaving you with free space which you can reuse later.
September 17, 2009 at 7:48 am
Review the article I link to in my signature - Gail does a very good job of explaining how to manage transaction logs.
What you are seeing is normal and expected behavior. Personally, I would grow the log file out to 30GB (at least) and leave it alone.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply