January 24, 2014 at 5:23 am
Ok, I am trying to get my head around the best course of action for the following scenario
I have a database which is over 200GB in size and has log shipping configured on it. All is well, apart from I need to add a new index to the biggest table of the database - 50GB size table, 300 million rows.
This obviously produces a huge amount of log information which grows the transaction log from a manageable 10GB to well over the available disk space on the server. So, what to do!?
I initially thought I could do the following:
Stop the log shipping backup job.
Set the database to simple recovery.
Perform the index creation.
Set the database back to full recovery.
Take a differential backup of the database and restore without recovery to the secondary database.
Restart the log shipping backup job.
On my test server, this process works fine and all transactions and the new index is brought over fine. However, on the live server, I have suddenly thought, the last full backup of the database was taken back when log shipping was first created - September 2012!! If I take a differential backup, would this mean the differential backup size will be huge as the database has changed so much in that time?
What would be a better way to allow me to create the index without large amounts of transaction logging, huge database backup files and not have to reinitialise log shipping again?
Thanks.
January 24, 2014 at 5:37 am
If you take a differential backup, you'll need the original backup as well. But yes, your differential is likely to be almost as large as a full backup. Are you using SQL Server 2008 R2? If so, you can compress your backup, and it might fit into 40GB or less, depending on what type of data you have in your database. Alternatively, some third-party utilities will produce compressed backups.
John
January 24, 2014 at 5:55 am
Thanks for the reply, that is what I thought would happen. I think I need to do a bit of experimenting then. I am running R2 so can compress the backups. I'll see what size I can get it to.
January 24, 2014 at 6:20 am
Maddave (1/24/2014)
However, on the live server, I have suddenly thought, the last full backup of the database was taken back when log shipping was first created - September 2012!!
A year and a half from the last full backup... What's going to happen if you need to restore that DB somewhere? Let's say someone drops a table and doesn't tell you until the log shipping's restored the log with the drop table.
Can you restore from those backups you have?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 24, 2014 at 6:30 am
GilaMonster (1/24/2014)
Maddave (1/24/2014)
However, on the live server, I have suddenly thought, the last full backup of the database was taken back when log shipping was first created - September 2012!!A year and a half from the last full backup... What's going to happen if you need to restore that DB somewhere? Let's say someone drops a table and doesn't tell you until the log shipping's restored the log with the drop table.
Can you restore from those backups you have?
I completely agree! It's not a good situation and something which has been overlooked. I have already put in plans to rectify this asap.
January 24, 2014 at 7:44 am
Maddave (1/24/2014)
I initially thought I could do the following:Stop the log shipping backup job.
Set the database to simple recovery.
Perform the index creation.
Set the database back to full recovery.
Take a differential backup of the database and restore without recovery to the secondary database.
Restart the log shipping backup job.
Why switch to simple recovery and break the log chain, you could set to bulk logged and perform the index creation then switch back to full afterwards. Ensure you take a compressed log or diff backup and restore to the secondary. If you don't have sql compression you could install the trial version of litespeed to accomplish this on a onetime run.
Maddave (1/24/2014)
On my test server, this process works fine and all transactions and the new index is brought over fine. However, on the live server, I have suddenly thought, the last full backup of the database was taken back when log shipping was first created - September 2012!! If I take a differential backup, would this mean the differential backup size will be huge as the database has changed so much in that time?What would be a better way to allow me to create the index without large amounts of transaction logging, huge database backup files and not have to reinitialise log shipping again?
Thanks.
Follow these steps exactly
John Mitchell-245523 (1/24/2014)
If you take a differential backup, you'll need the original backup as well.
This is not entirely accurate. See here[/url]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply