SQLTeam.com | Weblogs | Forums

LogShipping Break after truncate the logs for SQL Server 2014 Database


#1

Hello Team
I am new in SQL Server and i don't have much space on our server drive that's why we don't want any huge log files and also we need log shipping for all the database.
We setup SQL Server 2014 Standard Edition on our server and shipping the logs to another server in every 15 minutes and also we have truncate the logs every midnight Sunday. Everything working fine for last six days and another day my logs shipping breaking with below error on primary end and on secondary server end-
1.- Error: BACKUP LOG cannot be performed because there is no current database backup. [whereas we have backup in the drive]
2. Error: This log cannot be restored because a gap in the log chain was created. Use more recent data backups to bridge the gap. [whereas log shipping for this database working properly ]

I think i am doing wrong truncate the logs and logshipping both can not work

Please suggest if i am doing wrong -

Thanks
Raveesh Katiyar


#2

Yup :slight_smile:

You can't truncate logs if you log ship ...

... but I don't think you should truncate logs EVER. If you don't need the ability to restore to point-in-time during the day then set the database to SIMPLE Recovery Model.

If you DO need point-in-time restore capability (for disaster recovery) then you should NEVER truncate logs. The moment you do that you cannot restore to point-in-time, so if you have a disaster shortly after Truncate Logs you have NO ability to restore without data loss (i.e. all you can do is to restore to the last backup BEFORE Truncate Logs).

If you do need to truncate logs (can't imagine why though ...) then IMMEDIATELY take a full backup. That probably won't help you with log shipping - you'd have to ship & restore that Full Backup before log shipping will resume (I'm not aware that SQL will do that automatically for you).

If you have a task that is making huge Logs (such as Index Rebuild) then increase the frequency of Log Backup / Shipping during that time.

Personally I think you 15 minute interval for Log Shipping is quite long ... depends what you want it for, it might just be for Enquiry Users at a second location, but if it is for Disaster Recovery I would want it to be more frequent.

How much data can you afford to lose (at the WORST time of the day :slight_smile: ) ?

If Log Shipping were to happen, say, every minute or two how much benefit would that be after a disaster? (assume the building is destroyed, how many other things will be occupying everyone's minds to get sorted?? such that not having to worry about lost data gives them time to fix the other things)