MS SQL Server 2008 Transaction log file back up failure

Question

I've done some reading around this subject here and on StackOverflow but nothing really answers my question enough for me to go forward.

I'll explain we have a MS SQL 2008 server running, the transaction log file backup has been failing every night, now I've had a look at some of the other errors we've had, long and the short of it, it looks like this could be disk or I/O or so I'm informed, as this is a hosted machine we've initiated a change but that takes time and Sods law means the closer you are to making a change the more likely something catastrophic will happen. ie I need to make sure we're safe.

I'm not a DBA (If I was I probably wouldn't be asking questions).

Now as I understand it, if the transaction log backup fails when you are on a Full recovery model, then you're stuffed.

So I've changed to a simple recovery model (I appreciate the implications interms of point in time better we have a backup than one that will fail on restore) as I understand it a Simple Model writes everything to the database during the backup, what I'm not sure of is, what this really means in terms of the transaction log. ie if the database died this moment and I had to restore from a backup, but the transaction log backup fails what this means to me. We have a backup plan for the DB and the logs. We know the logs backup fails, but if it's in simple recovery mode and the database is backed up but the log back up fails would I be able to recover the database? By recover lets get this straight, I'm talking about getting the thing working again by simple restoring from my last back up?

Now to the 2nd part, if we assume there is a problem with a sector on the disk (Appreciate I need to move as soon as I can, but new machine etc etc are out of my sphere of influence), then it occurs to me that deleting and recreating a log file won't work as the file is likely to be in the same part of the disk. So wouldn't creating new log file and removing the old log file (but leaving the old file on the disk) be the thing to do? As that would effectively stop the bad sector being used (Different partition would also solve this, just this is a C: and D: affair and space is tight on C:.

So if so, how do I do this? I guess I'm really asking for step by step here as I'm unsure of all implications, I've read elsewhere that you will be in a whole world of pain if you just delete a log file by say stopping the service deleting the file and recreating new file same name and starting the service. So what do I do?

Really appreciate all comments

Thanks in adavance

Answer 1

Have you tried running a full backup followed by another transaction log backup? What error messages are you getting?

We know the logs backup fails, but if it's in simple recovery mode and the database is backed up but the log back up fails would I be able to recover the database? By recover lets get this straight, I'm talking about getting the thing working again by simple restoring from my last back up?

If the database fails, you can recover the database with the last full backup regardless of whether it's set to full or simple recovery. Simple recovery doesn't support log backups at all. So basically, you'd lose everything from your last full backup forward.

So wouldn't creating new log file and removing the old log file (but leaving the old file on the disk) be the thing to do?

NO. DO NOT DELETE YOUR LOG FILE. You'll probably kill your database and the symptom you're trying to fix is that it won't back up properly.

I'm going to recommend this article, "A beginner’s guide to SQL Server transaction logs." Especially this bit:

Can SQL Server database work without a transaction log?

No, that is not possible due to the SQL Server design and ACID compliance. ACID stands for atomicity, consistency, isolation, and durability.

Also this article on Full vs. Simple recovery.

To crib from this article:

1. Run dbcc checkdb on the database
2. Stop all user activity in the database
3. Switch to the SIMPLE recovery model (breaking the log backup chain and removing the requirement that the damaged portion of log must be backed up) (which you've done, yes)
4. Switch to the FULL recovery model
5. Take a full database backup (thus starting a new log backup chain)
6. Start taking log backups

Answer 2

Many thanks to everyone here, everything was very helpful.

Especially knowing about simple recovery backups.

FYI we changed the log path to another partition which resolved the issue. Moving was another problem as I had to recover space from another partition create a new partition etc etc.

Many thanks to everyone, FYI I tried voting both your answers as they both helped, but apparently I'm too much of a noob to vote..

Answer 3

I understand it a Simple Model writes everything to the database during the backup, what I'm not sure of is, what this really means in terms of the transaction log. ie if the database died this moment and I had to restore from a backup, but the transaction log backup fails what this means to me. We have a backup plan for the DB and the logs. We know the logs backup fails, but if it's in simple recovery mode and the database is backed up but the log back up fails would I be able to recover the database?

Irrespective of recovery model,SQL writes everything to log as you said,but there are quite a few things you need to make note off

Suppose below is your backup plan: Full backup each day at 6 AM,Differential backup every 12 hours,Log backup every 5 mins(assuming you are in Full,bulk logged recovery model)

Now lets take each recovery model and get things straighten out:

Simple: In Simple recovery model,you can not take log backups,so suppose a failure occurs at 8 AM,you only can restore 6 AM backup,that means you have two hours of data loss. So to answer your question,you dont have point in time restore capability,but you can restore database

Full recovery model: Assume the same failure happens at 8 AM,since you have log backups every 5 minutes,you will be able to restore right upto the point of failure by taking tail log backup .

http://www.sqlskills.com/blogs/paul/disaster-recovery-101-backing-up-the-tail-of-the-log/

Bulk logged recovery model: Bulk logged recovery model works in the same way as Full recovery model but with some advantages like some transactions are minimally logged provided your database is didn't configured for replication.Further you cant restore to point in time if your backup contains bulk logged transactions.More on this here.

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2830-bulk_logged-recovery-model/

So please know how much data loss is acceptable and choose recovery model based on that.

I've read elsewhere that you will be in a whole world of pain if you just delete a log file by say stopping the service deleting the file and recreating new file same name and starting the service. So what do I do?
You can just simply delete log file,SQL server uses log to bring the database to bring back into consistent state.Rather than taking this extreme step,i would recommend switching to Simple recovery model and take backups based on data loss acceptable.

Since you are saying backups are working fine and this is happening only with TLOG backups(which i assume are written to different disk,otherwise you might have faced the same issue with backups too),why not change path of logs to backups location till issues are sorted out.

You also can take log backups to network path,worth trying until disk issues are sorted out

backup log testdb to disk='\testdb_tlogbackups\testdb.trn' with compression