February 28, 2014 at 6:24 am
I have database in Simple Mode on SQL 2008 R2, every time I index or carry maintenance on the database the log size keep growing,
Please Assist,
Regards,
Bubby
February 28, 2014 at 7:01 am
Hi. Index rebuilding etc. is a fully-logged process so it will cause some growth in your log file.
Is it causing you particular problems?
February 28, 2014 at 7:01 am
(So sorry about the duplicates.)
February 28, 2014 at 7:05 am
Database is 2 gb and the log is 4 gb, every time I shrink log the size of log grows, as mentioned database is simple
February 28, 2014 at 7:11 am
What is size of the biggest table in the database? My safe estimate for index rebuilds is 2.5 times the size of the original for the log file.
Shrinking the file each time can be counterproductive since SQL will need to grow the file the next time you perform a task of this size. please see some of the other posts about virtual log files ( VLF ).
February 28, 2014 at 7:12 am
Why are you shrinking the log so much? If it keeps growing to a particular size, it needs to be that size. Sometimes it is necessary to shrink the log, but quite rarely. If you shrink it too small it will just have to grow again, and that can be resource-intensive.
Also, shrinking your log file causes fragmentation, which can lead to slowness starting up, backing up, restoring, etc. It's not a great idea!
February 28, 2014 at 7:41 am
Agreed, but I would I get my size of log low as it is simple database,
February 28, 2014 at 7:48 am
bubby (2/28/2014)
Agreed, but I would I get my size of log low as it is simple database,
data = 2
log = 4
total of 6GB
how short on space is your system? Yes, I like to keep things trimmed, but this seems like overkill for the size of hard drives these days.
February 28, 2014 at 8:01 am
Disk is not a problem, but I had this maintenance tasks running before too, size was growing in few mbs now suddenly it has grown crazy, I need to see my log to minimum, any way to do that
February 28, 2014 at 8:31 am
Well, what has changed recently?
February 28, 2014 at 9:05 am
Nothing was changed, I need help if something can be done, looking for resolution here
February 28, 2014 at 9:09 am
Potentially you have some sort of open transaction, close mirroring/ replication, that prevents the log from being cleared completely.
In terms of the size, I guess it's possible you need that space for maintenance, though it does seem high. If you run an index rebuild for each table separately, does it still grow very large? Maybe check your largest table?
February 28, 2014 at 9:09 am
If it's in Simple Recovery, the only thing you can do to keep the size smaller is reduce the transaction size and count. If you're seeing large logs after maintenance, it's due to index rebuilds more than likely. Instead of using the maintenance script, use your own so that you can control how many tables get done all at once.
"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
February 28, 2014 at 9:29 am
bubby (2/28/2014)
Nothing was changed, I need help if something can be done, looking for resolution here
This is a little rude. Several people have been trying to help you.
February 28, 2014 at 11:21 am
My Apologies if anyone felt that way, I have mentioned that there was regular maintenance running, and the log would not grow.
I even stopped the maintenance job, but whenever I try to shrink log files the size grows in case it decreases,
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply