SQL 2008 Log Shipping

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.

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.

Please go through the below thread as it is similar to your case:

https://www.experts-exchange.com/questions/26778710/Live-Database-not-in-Sync-with-log-shipped-Database.html

Thanks,

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?

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,

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.

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?

Please do not shrinnk logs or database files.
You can read following.
http://www.straightpathsql.com/archives/2009/01/dont-touch-that-shrink-button/
http://blog.sqlauthority.com/2010/08/12/sql-server-shrinkdatabase-for-every-database-in-the-sql-server/
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/13/629059.aspx

How big is your log file size?

USE <your database name>
EXEC sp_helpfile

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?

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.

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?

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?

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!

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.

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?

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

 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?

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

OK the result was 850 rows

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

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.

I'm glad I could help you.

Take care,
Daniel