March 8, 2011 at 2:31 am
Hello all,
I currently run log shipping between two databases.
On the production box, I've set the backup and restore time at 1 hour.
The log shipping on it's own can generally run uninterrupted, and is fairly reliable.
However...
I also run some reindexing jobs, and split them over 9 nights, as there are a lot of tables and data, and it's not feasible to carry out a full db reindex in 1 night.
If I leave the recovery mode set to full, then the log file will grow to a point where it may fill up the disk.
However if I start the reindexing job by setting the recovery mode to simple and then returning to full after it has finished, the log shipping goes out of sync, as the LSN is no longer in series;
323899500000028450000132390970000004434000012011-03-07 20:00:17.000LI:\Standby\MyDB_tlog_201103072000.TRN
323918200000016530000132393030000002274000012011-03-07 20:30:00.000DI:\SQLServer\MSSQL\Backup\MyDB.BAK
323947700000022800000132394770000002282000012011-03-08 06:00:16.000LI:\Standby\MyDB_tlog_201103080600.TRN
323947700000022820000132394780000001065000012011-03-08 07:00:15.000LI:\Standby\MyDB_tlog_201103080700.TRN
323947800000010650000132395050000005010000012011-03-08 08:00:17.000LI:\Standby\MyDB_tlog_201103080800.TRN
323950500000050100000132395850000005097000012011-03-08 09:00:15.000LI:\Standby\MyDB_tlog_201103080900.TRN
I was able to manually restore the first file in the list here, but when I try to restore any subsequent logs, I get the following error;
"The log in this backup set begins at LSN 3239477000000228000001, which is too late to apply to the database. An earlier log backup that includes LSN 3239097000000443400001 can be restored. [SQLSTATE 42000] (Error 4305) RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed."
I'm sure this must be a fairly common occurrence for reindexing strategies, so can anyone offer up a good method or suggestions to manage both?
Many thanks in advance! 🙂
March 14, 2011 at 9:58 am
If this is a production box I wouldn't reccomend switching anything to simple mode...just throwing that out there (as I'm sure you're already aware)
Without knowing the details (i.e. how large ore the indexes you're trying to rebuild? What's the fragmentation threshold you use to determine the rebuild? How much space are on the HD's, what's the size of your log file? Etc) it's going to be difficult to help however, here's a few things to try:
- Change the frequency of your log shipping backup and restores to be about 15 mins - this should help minimize the growth of your log files while performing the rebuilds
- Do the rebuilds in smaller batches, you run more risk of bloating your log files if you're running a large number of rebuilds at the same time (plus smaller batches will most likely allow for your log backups to assist in clearing out space)
Just a couple thoughts, hope it helps
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 14, 2011 at 10:19 am
Re-indexing is a bulk logged operation.
If you will never need to restore to a point in time while re-indexing is happening try:
1. Switch to bulk logged.
2. Reindex
3. Switch to Full.
The bulk logged recover mode will not break log shipping and should produce a smaller log file than full recovery with bulk logged operations.
I would also be inclined to have another job to set the database to full recovery early in the morning just in case anything went wrong with the re-index job.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply