October 11, 2010 at 8:27 am
There are three databases located on three separate servers around the country (Miami, Los Angeles and New York). These three databases are roughly 20-30 Gb. The current practice is to use transaction log shipping (actually a modified process that compresses/decompresses the logs for shipping and application) to update backup (data repository) copies of each of the three databases on a central SQL server in a different New York location.
The three production databases are backed up nightly, as are the data repositories. The growth of the amount of data has created performance issues with the production databases.
What I would like to do is a) turn off log shipping, b) delete the excess data from the production tables, and c) turn log shipping back on.
The questions I have are:
Can I apply transaction logs to the database if the number of records in the tables are different?
The data to be deleted (all records over six months old) will not be changing, so will the transaction logging application process fall over?
Will I be able to turn off logging in order to delete the old data, or will the data be deleted from the repository once I delete it from the production database and then turn log shipping back on?
Am I doing this entirely the wrong way? Note that I did not set up this database backup scheme (log shipping) and am not free to make any change that I wish.
October 13, 2010 at 3:54 pm
Are you sure the data repositories are being backed up? if they are in a state to receive log backups it will not be possible to back them up.
there is no need to turn off logshipping, in fact, don't. what you must do is ensure the deletes are batched up and transaction log backups are taken often enough to prevent the transaction log blowing up in size.
As the logs are restored to your backup databases the deletes will be replayed on those databases. As long as you manage log growth the process will not fall over.
If you mean delete the data from the production databases but leave it in the data repositories you cannot do that when using logshipping. all DML is replayed to the backup and you cannot 'miss out' backups, the log chain will be broken and the next one will fail to restore.
---------------------------------------------------------------------
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply