Log shipping (secondary database is in standby mode)

Question

I have a very big database , during reindexing the log backup become 80 GB ,database is in logshipping and secondary database is in standby mode, how can i prevent such a hugh file which contain reindexing log to go to the secondary server in sql server 2000.

Answer 1

Possible solutions:

1. If point in time data recovery is not must or bulk log operation is not expected during the reindex operation Consider using Bulk-Logged recovery model for your log shipping. I repeat that be cautious because it may affect your point in time recovery. Read below before doing so:

Restore Under the Bulk-Logged Recovery Model : http://msdn.microsoft.com/en-us/library/ms186229.aspx

and Considerations for Switching from the Full or Bulk-Logged Recovery Model : http://msdn.microsoft.com/en-us/library/ms190203.aspx

2. Another solution can be to go for custom Log shipping which will be intelligent to switch from FULL to BULK-LOGGED and back to FULL, but that can be done only at the time of non business hours where no user will be connected to the user database since it requires to be in Single User mode.

Also I will suggest to read http://social.msdn.microsoft.com/Forums/en-CA/sqldisasterrecovery/thread/bb1aa993-4f13-4010-8ff8-8b5a689b4e47

Hope it helps.

Answer 2

thnx ajay...

actually i have a database of 200 gigs with log shipping configured and it has a table of around 140 gigs. every night i need to rebuild the index because of which my log file becomes huge. and the corresponding log backup increases about 40 gigs.(even when i rebuild with bulk log)

to send transaction log backup (40 gig) over the network to the secondary server is a hurculine task, which fails almost every time.

is there any solution by which i can reduce the size of this transaction log backup.?

and what if i dont need to rebuild a index in secondary database.?

Answer 3

After seeing the backup size(40GB) of the transaction log file and database size(200GB) I have few suggestions:

1.   Go for stripped backup of transaction log which will backup the transaction log in multiple smaller files. With this, chances network failure for transferring a huge backup file will be less, because the size of one backup files will be quite smaller. Also if any of the file copy fails, the administrative overhead and consumed time will be quite less. The number of stripes is up to you to decide based on network throughput. I will advise not to go above 4GB because of old server build.

Stripped backup example below where a transaction log backup is getting divided into 6 chunks:

backup log testdb to
                 disk='c:\temp\testdb_1.trn',
                 disk='c:\temp\testdb_2.bak',
                 disk='c:\temp\testdb_3.trn',
                 disk='c:\temp\testdb_4.trn',
                 disk='c:\temp\testdb_5.trn',
                 disk='c:\temp\testdb_6.trn'
                 with init
2.       The other option would be to migrate from SQL Server 2000 to 2008 and choose compressed backup.

3.       Also looking at the size I would advise to go for some compression tool such as SQL LiteSpeed, BMC Backtrack, and Redgate etc.

4.       You can even advise to go for SQL Server 2005/2008 database mirroring.

5.       If it is very high transactional system even SAN/SRDF can be a good solution.

Hope that helps.

Answer 4

Possible solutions:

1. If point in time data recovery is not must or bulk log operation is not expected during the reindex operation Consider using Bulk-Logged recovery model for your log shipping. I repeat that be cautious because it may affect your point in time recovery. Read below before doing so:

Restore Under the Bulk-Logged Recovery Model : http://msdn.microsoft.com/en-us/library/ms186229.aspx

and Considerations for Switching from the Full or Bulk-Logged Recovery Model : http://msdn.microsoft.com/en-us/library/ms190203.aspx

2. Another solution can be to go for custom Log shipping which will be intelligent to switch from FULL to BULK-LOGGED and back to FULL, but that can be done only at the time of non business hours where no user will be connected to the user database since it requires to be in Single User mode.

Also I will suggest to read http://social.msdn.microsoft.com/Forums/en-CA/sqldisasterrecovery/thread/bb1aa993-4f13-4010-8ff8-8b5a689b4e47

Hope it helps.

Just for the record, changing your recovery mode from FULL to BULK-LOGGED before re-indexing will not reduce the size of your transaction log backups.

Using Bulk Logged recovery model for Bulk Operations will reduce the size of Transaction log backups – Myths and Truths

---

Make everything as simple as possible, but not simpler.
-Albert Einstein

Answer 5

Agree.

Then you probably might think of stripped backup OR Compressed backup OR DB Mirroring.

---

Warm Regards, Ajay

Answer 6

Agree.

Then you probably might think of stripped backup OR Compressed backup OR DB Mirroring.

---

Warm Regards, Ajay

I think you meant striped backups, not stripped backups. The original post is for SQL Server 2000. Backup compression is a feature of SQL Server 2008 and above and DB Mirroring of SQL Server 2005 and above. This means that only striped backups are an option, but that will only work towards improving backup performance, it will not have any chance of reducing the size of the transaction log backups. Striping could also complicate the log shipping scenario.

You can read about SQL 2000 backups here:

http://msdn.microsoft.com/en-us/library/aa225964(v=sql.80).aspx

And here is a good article describing the syntax and behavior in SQL Server 7.0 and SQL Server 2000 to achieve striped backups:

http://support.microsoft.com/kb/325334

---

Make everything as simple as possible, but not simpler.
-Albert Einstein

Answer 7

Instead of doing a rebuild, just do defrag of the table which is huge, which will not need too much of log space and see whether it is helpful. Got to see this link by Kimberly Tripp and Paul Randal . Check this out

Once this is done, you can get a maintenance window once in a month or something like that do reindex. During this time, you can break logshipping and then recreate again.

Unfortunately I have the same problem as yours. and I am dealing it now in this way.

HTH

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com