February 10, 2009 at 3:57 am
Hi all,
I have a log shipping in place for all of my databases. They are not so big, the biggest 30 Gb. I have the log shipping process happening at every 15 minutes.
This includes backing up the log file on the main server, copy the backed up log to a shared location on the second server, and restore the backup up log file in the second server to the database on the secondary server.
Obviously the database in the second server is a restore from the same database in the main server.
I have a job that run weekly that does the reindexing of the database, leaving 10% free space.
It is scheduled to run 05:00 am Monday Morning.
While it happens the log file becomes very big, lets say 3GB, then there is no enough time to back, copy and restore in 15 min.
This causes the log shipping process to fall out of sync.
It all comes back to sync by 9:00 am.
However, I dont want my log shipping out of sync.
Here I would like your opinion and comments.
I am planning to, before the reindex job starts, change the database to bulk mode recovery mode, because in my wisdom, I think the reindex job will not cause the log to grow so big if I do so, but I am not sure, what do you think?
It then arised another question: if I am doing all this reindex in the main database, is it automatically doing the same in the log shipped version?
(the same database that is on restoring mode on the secondary server)
What alternatives do we have in order to prevent the log to grow so big during the reindex process, and so not causing the log shipping to go out of sync?
thanks in advance,
marcello miorelli
February 12, 2009 at 5:32 am
The problem is your reindexing job. It causes the log file to become larger as it reindexes. This is normal. That huge log is what gets the reindexing over to the log-shipped database, so it will look like the source DB.
You need to review your reindexing process to see if it is really needed. I have seen some DBAs reindex everything as a matter of course instead of evaluating whether the reindexing actually needs to happen or not. I do not know if this is your case, but I am putting it out there.
I suggest a job that evaluates the index fragmentation and only does a rebuild if the fragmentation level exceeds a certain percentage, that you will decide on.
If have already evaluated your indexing strategy and find that it is required, your choices become more limited. The first is to live with it, since it does eventually synch back up. The second is to set up jobs to do the indexing in smaller chunks, let's say one or two tables at a time instead of the entire database, then you can stagger the jobs over a bigger time period and keep the log for each time period a more reasonable size.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
February 13, 2009 at 7:18 am
or, option #3, use the bulk mode strategy.
Works for me.
Tim White
February 13, 2009 at 7:37 am
Tim,
Are you saying that reindexing takes up less or no space in the log when you have the DB set to the "Bulked logged" recovery model?
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
February 13, 2009 at 7:50 am
A rebuild index is minimally logged in Bulk-Logged mode;
http://msdn.microsoft.com/en-us/library/ms191484(SQL.90).aspx
Tim White
February 13, 2009 at 8:11 am
Exactly, that is another thing I was planning to do but I am not sure if the reindexing work works in bulks, if it does, then fine.
If not, then I better do as Tim suggested and spread the indexing work among different days.
Regarding the question that "is the database is in bulk mode recovery mode, the reindexing is not written to the transaction log in full", if someone has already tested it, please let we know.
thanks in advance,
Marcello
February 13, 2009 at 8:18 am
Actually it was Chris that suggested a couple of alternatives for you.
I have used the Bulk Mode strategy in a production environment and it works just fine.
One caveat, if your reindex job ever fails for any reason, make sure your databse gets back to the full model.
One other thing you asked..... yes, it wil work fine in a log shipping environment. (not a mirrored, which we haven't discussed anyway)
As always, test, test, test
good luck
Tim White
February 13, 2009 at 8:56 am
Wow, this is new to me. I had not thought of that before, and obviously did not know it was possible, switching back and forth between Bulk and Full. I've just never had a problem with index rebuilds because I do it selectively.
http://technet.microsoft.com/en-us/library/ms190203.aspx
Learn something new every day. Thanks Tim.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply