Sql-server – MS SQL Server 2008 Transaction log file back up failure

sql serversql-server-2008transaction-log

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

Best Answer

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
Related Question