June 25, 2014 at 8:22 am
Hello,
We have a requirement to rebuild a load of very heavily fragmented indexes. We do not have Enterprise so has to be done offline. We are able to turn off the application pointing to this database. The database is currently in FULL recovery mode and we log ship a copy to out office from PRODUCTION. There is a 56GB table with a PK that needs to be rebuilt/dropped-created so the amount of log growth will be very high....too high for our bandwidth without special scheduling. I estimate around 100GB of changes whereas the compressed backup is only 20GB.
We do not want the log growth associated with the index maintenance being transferred so wanted to check my thoughts are correct on the method:
- turn off the application and ensure all connections closed
- take a LOG backup
- disable the LOG backup schedule
- switch to SIMPLE
- perform index maintenance
- take a DIFFERENTIAL backup
- switch to FULL
- take a LOG backup
- re-enable the LOG schedule
- turn on the application
This should avoid breaking the restore chain and transferring all the changes to the log-shipped copy in real-time. We can then reinitialize the logship copy from the compressed FULL backup we can transfer slowly.
Is this overkill?? Am i missing something??
Thanks
Samuel
June 25, 2014 at 10:04 am
lilywhites (6/25/2014)
Hello,We have a requirement to rebuild a load of very heavily fragmented indexes. We do not have Enterprise so has to be done offline. We are able to turn off the application pointing to this database. The database is currently in FULL recovery mode and we log ship a copy to out office from PRODUCTION. There is a 56GB table with a PK that needs to be rebuilt/dropped-created so the amount of log growth will be very high....too high for our bandwidth without special scheduling. I estimate around 100GB of changes whereas the compressed backup is only 20GB.
We do not want the log growth associated with the index maintenance being transferred so wanted to check my thoughts are correct on the method:
- turn off the application and ensure all connections closed
- take a LOG backup
- disable the LOG backup schedule
- switch to SIMPLE
- perform index maintenance
- take a DIFFERENTIAL backup
- switch to FULL
- take a LOG backup
- re-enable the LOG schedule
- turn on the application
This should avoid breaking the restore chain and transferring all the changes to the log-shipped copy in real-time. We can then reinitialize the logship copy from the compressed FULL backup we can transfer slowly.
Is this overkill?? Am i missing something??
Thanks
Samuel
- take a LOG backup
- disable the LOG backup schedule
will break log shipping.
I believe you can copy log files and make log shipping work, however, I could not make it happen without a lot of practice. I just recreate my log shipping.
good luck.
Oh, by the way, just because you switch to simple recovery does not mean the index rebuild will not be logged. It just means the transition log will be flushed later (this may be the wrong explanation but that is how I see it).
June 25, 2014 at 10:07 am
hmm....yeah i jumped ahead of myself....will be logged but not stored
and i have resigned myself to restarting the logshipping for this database if i want to get the maintenance done!!
joined 2 months ago and the database has been maintenance-free for 2 years....facing 70mil rows with 98% fragmentation on PK's using GUID!!
lots of fun to be had :p
gotta do something.....
June 26, 2014 at 9:03 am
lilywhites (6/25/2014)
There is a 56GB table with a PK that needs to be rebuilt/dropped-created
I'm assuming you're referring to rebuilding the clustered index that was created on the PK column?
You don't need to drop the PK just run an ALTER INDEX .... REBUILD on the clustered index.
Instead of switching to simple recovery, switch to Bulk Logged for the index rebuild, this is minimally logged in both simple and bulk logged recovery models. Switching to Bulk logged will not break the log chain.
lilywhites (6/25/2014)
- turn off the application and ensure all connections closed- take a LOG backup
- disable the LOG backup schedule
- switch to SIMPLE
- perform index maintenance
- take a DIFFERENTIAL backup
- switch to FULL
- take a LOG backup
- re-enable the LOG schedule
- turn on the application
This should avoid breaking the restore chain
No it won't, the log chain will be broken using the steps above. To restore the log chain after the switch back to full you need the steps in this order
- take a LOG backup
- disable the LOG backup schedule
- switch to SIMPLE
- perform index maintenance
- switch to FULL
- take a DIFFERENTIAL backup
- re-enable the LOG schedule
- turn on the application
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 27, 2014 at 3:50 am
oohhh.....thanks perry!!
hadnt seen that distinction made anywhere else yet....nice catch 🙂
June 27, 2014 at 3:59 am
No problem, as i said switch to bulk logged for the rebuild and you wont break the log chain, but the rebuild will still be minimally logged
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 30, 2014 at 3:28 am
If you switch to Simple recovery mode then you break the log chain. The only way forward for your log shipping is to switch back to Full and send a new full backup to reinitialise log shipping.
However, as Perry has said, if you switch to Bulk Logged you will minimise the logging, and you will not break the log chain. After you switch back to Full you should do a differential, send this to your subscribers, then continue with normal log shipping.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 30, 2014 at 3:40 am
thanks guys for all your responses
please mark this post as closed/answered and give any points to perry 🙂
June 30, 2014 at 5:19 am
EdVassie (6/30/2014)
The only way forward for your log shipping is to switch back to Full and send a new full backup to reinitialise log shipping.
This is not entirely accurate, if all log shipping restores were in synch and no full backup was taken at the Primary after the switch to simple, a differential backup would suffice. See my article at this link[/url].
To check if a differential is possible just query the Primary and Secondary instances using
select name, differential_base_lsn
from sys.master_files
where database_id = DB_ID('yourdb')
and type_desc = 'ROWS'
If the base LSNs match you will be able to use a differential to restore the log chain 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 30, 2014 at 5:24 am
Thanks Perry, I stand corrected and have learned something new.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply