January 25, 2009 at 2:57 pm
Hello,
My Server is Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
On a production DB, Log-shipping is working fine 24/7 after every 15 min
A scheduled daily over-nightly maintenance plan is taking FULL back of the production database (Total DB size is 1.5GB) and other related stuff e.g. check database integrity, reogranize/rebild indexes and some occasional clean-up stuff.
On average, transactional log file size(.trn) is 2MB
During this backup process log-shipping and backup works smoothly and i have successfully restored that full-backup number of times and after restore, DB size remains same as of original backup.
NOW Question is:
After the FULL backup completes, Log-shipping transaction log file .trn size increases to double (3.5GB) and then gradually reduces to normal .trn file size to 2MB but after 2 Hours.
I wonder whats the reason of this size increase ?
Is this due to Log-shipping and FULL backup in parallel ? OR Full-back also creates log-entries during backup? OR somehow Log-shipping wraps the total backup file into it ?
Whats the solution to avoid this increase of transaction log file size ? because its then effects overall log-shipping process and also sometimes creates performance issues ? I would preffer NOT stopping log-shipping at anytime.
Kindly try to explain the reason, before sending some referenec material PLEASE.
Many Thanks
January 26, 2009 at 7:21 am
Yes !!!
I have studied some other articles and white-papers.
Its definitely index rebuild which i am doing daily. The steps are
1. Database backup
2. database Integrity
3. Reorganize ALL index
4. Rebuild ALL index
Because Index REBUILD option drops and then re-create all indexes. SO it is quite obvious that it is creating LOG entries of all those indexes during built. SO the log file size is increasing in parallel
NOW
1. Daily, there is heavy loading of data and later we use the database for quering and amendments
so we are doing INDEX reorganize and rebuild daily. Any problem with this ?
2. Physical RAM is 6GB which is shared among other databases on the same server. The size of this database 1.5GB but how to confirm if it is completely residing in the RAM ?
3.NOW question is that what are the options ? spcially if you do not want to STOP log shipping JOBS (if possible)
4.Do you have other recommendation to handle such situation ?
Thanks
January 26, 2009 at 8:02 am
I'm not sure why you are reorg'ing ALL indexes and then rebuilding ALL indexes. The reorg will move pages around, but then the rebuild totally wipes out your indexes and rebuilds them from scratch making the reorg superfluous.
My personal recommendastion is to remove the reorg step, and add a step to alter your database to "bulk Insert" mode before the index rebuild and then alter it back to "full recovery" mode after it's done. The bulk insert mode will not log the index rebuild processes but continue to log normal transaction use.
This should keep your log from growing beyond normal use.
Tim White
January 26, 2009 at 10:30 am
We encounter the same problems and Tim's suggestion should provide a solution
However because we use database mirroring it's kind of a challenge for us to change the recovery model to Bulk-Logged.
I'm wondering why you need to rebuild indexes every day because it could mean your indexes probably need more space. You could do some tuning with the FILLFACTOR of your indexes.
HTH
__________________
MS-SQL / SSIS / SSRS junkie
Visit my blog at dba60k.net
January 29, 2009 at 2:46 am
well,
Fill-factor is 90% for selected objects otherwise default is 0.
NOW, I have disabled REBUILD ALL task, however reorganize and update statistics are still there. Database was remain in FULL recovery model all the time. The transaction log file size after the Reorganize step is still double (3GB) than the DB size.
Another thing i have identified is that Log file growth is unlimited with 10% increment. Log file current size is 3GB as well.
So if rebuild index is not there now then what else is effecting this whole thing now ?
Thanks
January 29, 2009 at 6:40 am
Have you shrunk your tran log file down to a normal opertaing size?
Tim White
January 29, 2009 at 8:03 am
Does SQL server allow to 1. Truncate log AND 2. Shrink Log While the Log-Shipping is ON ?
OR
Do we need to stop the log-shipping and then "backup log with truncate_only" and then shrink log file ? If we go with this option then would there be any issue on backup server database, where i put the Log-Shipping back on ?
thanks
January 29, 2009 at 8:09 am
do not use truncate log. That will break your log chain and require you to do an immediate full backup. I've never tried to shrink a log file in a log shipping environment. The shrink is simply removing pages that ar no longer in use, so I see no reason why a shrink wouldn't work just fine.
Maybe someone else with more experience in log shipping can answer better.
hope you have a system to test it out on.
good luck!
Tim White
January 31, 2009 at 6:45 pm
Yes, it is possible to shrink the log file in a log shipping scenario. But the root cause of the problem is that you are reorganizing all your indexes nightly ... as this is a fully logged operation (even in simple recovery mode) the log files will grow.
Take a look at one of the scripts floating around (on this site and elsewhere) that selectively rebuild/reorg indexes based on fragmentation levels.
http://www.sqlservercentral.com/Scripts/Index+Management/
http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/62380/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply