Solved

SQL 2008 Log Shipping

Posted on 2011-03-08
23
689 Views
Last Modified: 2012-05-11
I have a log ship set-up and yesterday I was informed data has not been updated for couple of weeks. I looked at the 2 servers and the first part is taking place as configured.
the source server is creating the logs placing it in the source DIR and the logs are being moved to the destination DIR, however the destination database is not restoring the logs. I have 2 important questions.
1- how can I resolve the log ship process and make sure it is executing from end to end.
2- how can I get the existing logs from the past 2 weeks restored so the server is up to date.

I have exported the set-up script and is attached. Logship.sql
0
Comment
Question by:atorex
  • 11
  • 10
  • 2
23 Comments
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35069193
What error message(s) do you have in your SQL Server logs?

IIf you want to ensure that all process is running correctly you need to set up alert jobs to send email on failure or/and same to backup/restore jobs.
You can reinitialize log shipping by copying last full backup of primary database and restoring it on the second server WITH NORECOVERY. Next run restore jobs to restore logs from your log shipping folder.
0
 

Author Comment

by:atorex
ID: 35069736
Sorry that would have been a good thing to add to the question, here is the error

The log shipping secondary database distserver.dist_db has restore threshold of 45 minutes and is out of sync. No restore was performed for 59427 minutes. Restored latency is 15 minutes. Check agent log and logshipping monitor information.
0
 
LVL 8

Expert Comment

by:dba2dba
ID: 35070272
Please go through the below thread as it is similar to your case:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_26778710.html

Thanks,
0
 

Author Comment

by:atorex
ID: 35070318
I have found the cause of the issue, the 14421 latency error was caused by the second server being on a different time. I have corrected the time and restored the distserver with a full backup from last night. will the log restore continue from there once the restore is done?
0
 
LVL 8

Expert Comment

by:dba2dba
ID: 35071042
No, it wont continue. You need to setup log shipping again. Below link has step by step guide:

http://sqlserver-training.com/log-shipping-using-sql-server-2008/-

Thanks,
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35071504
It is a matter of logs you have. If you have logs starting from yesterday's backup your log shipping restore job should do it's work. It's slower attempt.
You can always reinitialize you log shipping by taking full backup of your primary db and restoring it to secondary server and then starting copy and restore jobs.
You can also use gui tool to reinitialize log shipping - as it is posted before.

It's up to you what to choose, but you should know is that you need to have all logs in sequence from backup to be able to restore them. If in your enviroment log backups become larger you can adjust your backup and restore jobs with alert jobs schedules to fit in your situation.
0
 

Author Comment

by:atorex
ID: 35083804
I need some more help with this, I re-initiated the log ship and ran for about 4 hours on a 15 minute interval. after that time it fails with the same 14421 error, I need to track the cause of the error.
I have a suspicion on the cause  but don't know that it would affect log shipping.
I had an issue with transaction log size so I created a job to truncate the log 3 times per day this resolved the HD space issue. when I looked at the last log imported it seems to be the last log before the truncate ran, could this be causing the 14421 error?
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35084015
0
 

Author Comment

by:atorex
ID: 35084161
I'm not shrinking it I'm truncating is that not recommended as well? the log is at about 10GB now but it was growing to over 100GB.
so would the truncating of the log cause the 14421 error during log shipping?
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35084350
Yes truncation of log will break log shipping.
Can you take log backup more frequent? You can properly size your logs, please read follwing:
http://sqlserver-training.com/vlf-virtual-log-file-sql-server-performance/-
Log backup cleans log of inactive transactions, with properly sized logs you can optimize it.
0
 

Author Comment

by:atorex
ID: 35085307
This customer has a somewhat high availability set-up, where the database is on a mirror and a log ship as well. to give you a better perspective in the SQL set-up.

there are 2 physical servers
main server has the live database with over 100 stores connected inserting sales transactions
the backup server has a mirror database for fail over
on the backup server a new database was created for log shipping where report server is accessing so not to overload the main server.
So with the Mirror active the recovery model implemented is full thus the log size and cant be set to  simple. I do have a backup with set to run each night and added 3 additional with truncate to keep the size under control.
should these log backups be without truncate?

0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35085614
So you have:
Main server with: so called production database set as mirror principal
Backup server with: mirror production database, another database which is log shiped somewhere else

Or you mean production database is mirrored and log shipped together?
0
 

Author Comment

by:atorex
ID: 35085817
main=production principal
backup=mirror of the production

production ships the log to another instance on the backup

CONFUSING!!!

so that would be (salesdata) database  is the actual database it is the mainserver and in the backupserver as mirror

in the backup server salesdata exists as reportdata database where the log shipped is to be restored


I hope that was clear.
Thanks for all the help by the way!
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35086321
OK. I understand now :) First of all log truncation will break your log shipping because it breaks logs chain so you need to avoid this. If you are running reports against database which participates in log shiping please note that until any activity in that database is finished it wil not recover any new logs. You can generate snapshot against mirror and run reports against that snapshot.
0
 

Author Comment

by:atorex
ID: 35086382
that's a new concept, can that be done on a scheduled time or will it have to be manual, any info on configuring the snapshot?
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35086662
Yes, creating snapshots can be done on schedule. It's up to you how frequent it need to be. You need to take into account reporting needs.
Info about snapshots:
http://blog.sqlauthority.com/2010/04/05/sql-server-2008-introduction-to-snapshot-database-restore-from-snapshot/
http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/read-from-your-mirror-in-sql-server
0
 

Author Comment

by:atorex
ID: 35087260

 Thanks Daniel, this is some awesome capability but my SQL is not Enterprise so I will have to stick with log shipping.
clarification- if truncating breaks the log shipping chain-
how would log maintenance be done?
will the daily backup need to be changed to exclude truncation?

We plan to move the LDF to a NAS soon where 2 TB will be allocated to just that file, should I then stop log backup?
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35087311
I'm sorry I should ask you about edition you have.
To solve your problem you can increase log backup frequency.
Can post number of rows from this command:
USE <name of your database>
DBCC LOGINFO()
GO
And size of your log.
EXEC sp_helpfile
0
 

Author Comment

by:atorex
ID: 35087599
OK the result was 850 rows
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35087672
You definitely need to resize your log file. It has lots of vlf files because of many resizes in small data chunks.
I'd suggest read following:
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
You need to resize your log, set proper growth and increase backup frequency.

To resize log you first need to shrink it:
a)backup your log, b)shrink, c)backup again, d)shrink again, d)set new size according to article
0
 

Author Comment

by:atorex
ID: 35087741
Much appreciated, I will work on that and test the log ship again.

I have stated this before but again this is the best money I spend the fee I pay for this forum is totally worth it.
Again thanks, Daniel.
0
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 35087754
I'm glad I could help you.

Take care,
Daniel
0
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 500 total points
ID: 35087818
Oh, could you mark correct answer or...?
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now