April 19, 2010 at 4:22 am
The Gurus,
The log files for my DBs have remained the same for a very long time now (6 months) but the data files keep increasing. For instance one of the DBs have a log file size of 3,164,032 for over 6 months now even though the corresponding data file keeps growing. Why is this? Any idea of what I can do to get the log file to 0 or lower? Can I flush it (i know that my choice of word looks funny)?
Thanks for your response.
Regards,
Sahoong.
April 19, 2010 at 5:50 am
For starters read this very informative article by Gail Shaw http://www.sqlservercentral.com/articles/64582/
This could be for a number of reasons but isn't anything necessarily to be concerned with. Most people have problems where the transaction log keeps growing!!
The above url explains why you should'nt 'flush' the log file. I would also expect the data files (.mdf / .ndf's) to grow accordingly.
Some more reading material http://www.sqlservercentral.com/articles/Transaction+Log/65255/
Any idea of what I can do to get the log file to 0 or lower?
Why would you want to do this?
April 19, 2010 at 5:59 am
You must have a regular log backup in place that truncate inactive portions of the log.
You can use DBCC sqlperf('logspace') to find percent used for the log file
April 19, 2010 at 6:46 am
It sounds like your log file is of sufficient size. As was noted, you may have a log backup process running. Your system might also be in simple recovery mode, meaning that all completed transactions are cleaned out each checkpoint. Either way, the log should not grow with the data. The log, if it grows at all, grows with the number and size of the transactions you're supporting.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply