Log shipping error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Log shipping error

Author

Topic

skybvi
Posting Yak Master

193 Posts

Posted - 2011-12-05 : 15:02:47

Hi,
Maybe this is a dumb question but I can't think it properly, I am highly confused.

I have log shipping errors as of a result, I see that the transaction log shipping status is red for 2 databases (it shows time since last restore 1700 minutes).

I am unable to find the error so I think I should restore (full) the databases.

We do full backups in the night.

I am confused as to where( prod side or log shipping server) should I restore the full database backups so that the databases are fully in sync.

Regards,
Sushant
DBA
West Indies

skybvi
Posting Yak Master

193 Posts

Posted - 2011-12-05 : 15:05:55

also, when I go to the log shipping server-->database-->
tasks->restore
i get option of just transaction logs

Then how do i do full database backup on log shipping server?

Regards,
Sushant
DBA
West Indies

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-05 : 15:27:16

The restore would occur on the log shipping server, known as the secondary server. You would restore the full backup plus the entire transaction log chain to the secondary server. You are doing transaction log backups on production, right?

I would troubleshoot the issue though before proceeding with a restore. Go to the secondary server and open up the error log. If it is failing on a restore, then it'll show the error in there. You may have an issue with the copy job though. You'll need to verify where it is failing in the process.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

skybvi
Posting Yak Master

193 Posts

Posted - 2011-12-05 : 15:36:10

Hi Tara,
Yes, im doing trans log backups on production.
We do log shipping from 6 am to 8pm

I wanted to started full backup after 8pm and then restore it on log shipping server...
But as i told earlier

when I go to the log shipping server-->database-->
tasks->restore
i get option of just transaction logs

Then how do i do full database restore on log shipping server?

The error(restore job) iam geting in log shipping is :--

*** Error: Could not apply log backup file '\\log shipping server\SQLBackups\RTW\RTW_20111205100001.trn' to secondary database 'RTW'.(Microsoft.SqlServer.Management.LogShipping) ***
2011-12-05 16:30:01.76 *** Error: This log cannot be restored because a gap in the log chain was created. Use more recent data backups to bridge the gap.
RESTORE LOG is terminating abnormally.(.Net Sql

Regards,
Sushant
DBA
West Indies

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-05 : 15:54:47

I don't use the GUI to do my restores. I use RESTORE DATABASE and RESTORE LOG.

Do you have anything truncating the log? It seems something broke the tlog chain, according to the error message. You can't have any interruption to the chain when using log shipping.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

skybvi
Posting Yak Master

193 Posts

Posted - 2011-12-08 : 08:15:27

Hi,
I restored full backups on log shipping server and then manually restored all the transaction logs until that particular time.
It was fine that time everything...

after that (some time),
I see that the latency of the last file restored went upto 50 mins and my thresold set by me was 30 mins, so it sending me alerts.
I can increase the thresold and get rid of the messages but i don't know why it again start lagging..

The size of logs also is not much its around 15-25 mb every 5 mins.

Regards,
Sushant
DBA
West Indies

skybvi
Posting Yak Master

193 Posts

Posted - 2011-12-08 : 08:19:41

I also tried
Stopping backup and copy jobs for 1 hour and just allowing to run restore jobs for 1 hour so that it gets back in sync, it did
but again
when I enabled other 2 jobs, after some time, again it got to lag by 50 mins ..

Regards,
Sushant
DBA
West Indies

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-12-08 : 12:33:33

You'll have to investigate hardware/network latency to figure out what's going on. If the size of your logs are that small at all times of the day, then it should be rather fast to copy/restore. So I am suspecting you've got slow disks or slow network at times causing this. I really don't know. We only had high latency when our rebuild indexes job ran to cause bigger tlog backups.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources