SQL 2008 Log Shipping

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
atorexAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Daniel_PLConnect With a Mentor DB Expert/ArchitectCommented:
Oh, could you mark correct answer or...?
0
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
atorexAuthor Commented:
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
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
dba2dbaCommented:
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
 
atorexAuthor Commented:
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
 
dba2dbaCommented:
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
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
atorexAuthor Commented:
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
 
Daniel_PLDB Expert/ArchitectCommented:
0
 
atorexAuthor Commented:
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
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
atorexAuthor Commented:
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
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
atorexAuthor Commented:
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
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
atorexAuthor Commented:
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
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
atorexAuthor Commented:

 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
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
atorexAuthor Commented:
OK the result was 850 rows
0
 
Daniel_PLDB Expert/ArchitectCommented:
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
 
atorexAuthor Commented:
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
 
Daniel_PLDB Expert/ArchitectCommented:
I'm glad I could help you.

Take care,
Daniel
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.