Post reply

ODBC SQLStateL 42000 error causing failed backups

  • August 2, 2005 at 10:14 am

    #109823

    First, I am a major newbie so whatever replies any of you may send, please provide as much 4-1-1 as you can and don't assume anything

    I was forced to take over a database and network for a small LAN about seven years ago with zero experience or training.  To date, I have been able to figure out each problem encountered... until now.

    I set up all of my backups to run on a maintenance schedule since I move us from SQLBase to SS 7.0 and have run without fail.  Once each month, I do a manual backup after running Month End processing and then compress the backup to be placed on media and moved off site.  The database's current size is about 6.5 Gig.

    On 8/1/05 when I started the manual backup, it failed.  The error read as follows:

    Microsoft SQL-DMO (ODBC SQLState: 42000)

    The Microsoft Tape Format (MTF) soft filemark database on backup device 'H:\SS_BACKUPSSSS\TN62' cannot be read, inhibiting random access.  Backup or restore operation terminating abnormally.

    When I checked the event viewer, it indicated that there was insufficient space on the disk (our backups go to a separate partition on one of our servers drives).  I checked and confirmed that to be the case.  I removed a number of useless files, etc. and gained 4.90 Gig of free space.  That has not solved the problem.  The scheduled backups do not work and even deleting all of the scheduled maintenance plans and recreating them does not work.  The verification phase of the maintenance plan runs successfully, but the backup fails immediately.  I have run DBCC CHECK and it returns no errors.

    I am going to be moving our database to a new server running  RAID 5 configuration so as to enhance performance, gain much-needed disk space and migrate to SS 2000.  Until then, I need to keep things working in the current configuration.

    There are no users logged into the database when the backups are run, no changes in permissions have been done, in essence, the server has not been touched, but ran its last successful backup under the scheduled maintenance plan on Friday, July 29, 2005.

    I really appreciate any help than can be offered.

    Thanks,

    LANMan

  • August 2, 2005 at 1:23 pm

    #578962

    Ok here is the deal:

    • First you need to check there is sufficient space meaning atleast 1 1/2 times the size of database.
    • Try to shrink the database before you do the backup so that it will save some space .
    • Check the tape inserted in proper and right tape.

    With all these take care you should be able to backup the db successfully. Good luck

  • August 2, 2005 at 1:38 pm

    #578964

    Vambati,

    Thanks for the quick reply.  In order of your recommendations/instructions, here is my situation.  I do not have 1 1/2 times the db size in free space.  The db is about 6.5 Gb and I have 4.90 free space (I'm going to be moving everything to a new server with RAID 5 configuration ASAP, but for now, I have to work with what I have).  How do I go about shrinking the database and what effect would that have on the db, especially when I port it to our new server that will have more-than-adequate disk space? Finally, and this is the real kicker, there is not a tape drive involved for the backups.  They back up to a directory set up in the H: drive partion on one of the hard drives in the server.  That has puzzled me as to why the error message would refer to a tape drive.   Any thoughts?

    LANMan

  • August 3, 2005 at 10:13 am

    #579216

    LANMan,

    I used to see this error all the time.  It happens when a backup file is corrupt, usually due to a failed backup when you run out of disk space, and you try to backup to that file again.  The MTF message refers to a soft filemark, which has to do with how the file is formatted.  When the format is corrupt, it decides it can't write to the file, even if you're using INIT.  The easy answer is to delete the file before trying to run the backup.

    The rest of your problem isn't easy. Shrinking the files won't make a difference, because only the data pages are backed up.  You might try reindexing, in case some indexes have fragmented and can be compacted.  Try running sp_spaceused to get an idea of the data space used (not the file size).  Otherwise, you really need more disk space for your backup.

    HTH,

    Dylan

    PS it just occurred to me that you might try backing up to two different files on two different drives, but I've never tried this.

    Dylan Peters
    SQL Server DBA

  • August 3, 2005 at 11:38 am

    #579263

    Dylan,

    What you are suggestion concerning the deletion of the backup due to probable corruption is something I considered after researching everything I could find on this error initially.  I think you are right and am waiting for a response from the designer of the database to see if they have any issues with this resolution.  I will let you know how things turn out as soon as I can.

    Thanks for your help.

    Gary

  • August 4, 2005 at 8:05 am

    #579584

    To all that offered help on this problem, just letting you know it has been resolved.  It was a corrupted backup file that resulted from the lack of disk space.  I reclaimed almost 5 Gb of drive space, deleted the suspect file, set up all new schedules for backups and integrity tests and let them run last night.  Results, a completely successful series of backups, so our system is now stable again.

    Thanks again for all of your collective input on this problem - as always, all of you came through and helped me solve another problem.

    LANMan

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply