SQL Server Performance Forum – Threads Archive

How to reduce the restore time

Folks, I support a pair of SQL 2K EE log shipping servers. Below are the details. Primary server: Backing up transLog every 30 mins from 12:00AM to 11:59PM Secondary server:
Copying transLog file every 30 mins from 12:05AM to 11:59PM
restoring only twice per day, 3AM and 3PM. Here comes the performance problem, when restoring, secondary server has to restore 24 transLog files copied from primary servers one time. Sometimes, one translog files sizes around 2GB, total restoring data nears 45GB. It took 8 hours to restore secondary database last time at 3PM. Folks, any idea on how to reduce the restoring time based on my situation. ——————
Bug explorer/finder/seeker/locator
——————

---

I am not expert in this area but fail to understand some things’s 1. If your are copying over the TLOG files every 30 minutes to secondary server, why dont you restore them once it is copied over ? this would reduce time to restore than one time all 24 logs. 2. The primary server on which TLOG backup runs I assume is OLTP data base with lot of modifiactions but 2GB for 30 minutes seems high but may be Iam wrong ….hope you can elaborate like transactions per second running on primary server ? Cheers
Sat

---

See this KBAhttp://support.microsoft.com/kb/873482 and check whether your system is falling under this issue. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

The reason why only restore twice: we have to do reporting during some time period, when restoring TLog, the database will be in loading/recovering mode. The primary server is really busy, with a lot of concurrent connections. Generally the TLog backup is around 1GB/30mins, sometimes it may reach 2.5GB/30mins
Satya, I will research your KBA, and respond here. Thank you, Guys

——————
Bug explorer/finder/seeker/locator
——————

---

In my opinion for reporting server transactional replication is best compare to logshipping.I am a Entry level DBA only.You can ask the comment from experts like satya,Mohammedu Thanks,
Prabhakaran
MS SQL Server DBA

---

Replication is one of the option for reporting but it depends on the size of the database and all the tables involved in replication should have the PK… Did you try running the differential backup twice a day few minutes before your restore time and then restore the differential backup instead of tlog. If I am not mistaken differential backup should be smaller than the 24 tlog backups… Try a shot using differential backup/restore… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

According to my understanding Diffrential backup size will depends on when they have taken the last full backup.Consider If the are taking full backup every sunday then differntial backup size will be more than log backup size only. Thanks,
Prabhakaran
MS SQL Server DBA

---

Yes, you are correct… but better to try differential backup and see how much time it takes… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Forgot to mention…
If you want spend some money you can try third party backup tools like LiteSpeed or SQL safe… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

You are correct, in case of the recovery options then you have to decide how you want to recover the database from the available backups. Transaction log backup is easy and similar to differential backups, but the total process depends upon how you have tested your recovery options.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

---

]]>