HELP! Can''t backup my database.

  • I am running Win2000 with SQL2000.  My backup has been working fine for a couple of days, until this morning.  Here's the error message I got from my nightly backup job:

    [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 945: [Microsoft][ODBC SQL Server Driver][SQL Server]Database 'IEX39' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.              

    [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.                                                                                                                                                                     

    1. I have plenty of backup space - over 1.6 terabytes free on the backup drive

    2. No users are logged into the database - test database and server was rebooted after the failed backup

    3. I can query the database fine and view a list of tables in Enterprise manager

    4. The SQL Server log pretty much says the same thing as the error message, not much help

     

     

     


    Live to Throw
    Throw to Live
    Will Summers

  • I also tried to create a new maintence job and just backup the problem database.  Same error.

    I also tried to create a new maintence job to optimize the database.  Same Error.

    I also tried to create a new maintence job to reorganize the database.  Same Error.


    Live to Throw
    Throw to Live
    Will Summers

  • Does look like a space problem - perhaps with the db itself.  Have you checked to see whether the 'Automatically grow file' property has been set (check the data file and the log file).  If not, you may have your solution.

    If you can successfully backup other databases, that also indicates a problem with this one specifically.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • I always turn off the automatically grow and shrink options on the data and transaction files.


    Live to Throw
    Throw to Live
    Will Summers

  • OK - so have you run out of growing space?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • I have plenty of 'growing' space.  I run a system that needs all of the processing power that it can have.  I don't want to take the hit of the database growing and shrinking without me controling when it happens.


    Live to Throw
    Throw to Live
    Will Summers

  • OK, no offence intended.

    This link may be of interest - they seem to have the same symptoms as you.

    http://www.talkaboutdatabases.com/group/comp.databases.ms-sqlserver/messages/123008.html

    Other than that, I'm running out of ideas.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Ok, I had to do some research on what other people were doing, but here's the conclusion:

    1. Permissions were changed on the drive where the database resides and not rebooted

    2.  Everyone still had access to the databases on that drive

    3.  Machine was rebooted a couple days later and when SQL Server was restarted, it was unable to get access to the drive and put the databases into suspect status.

    I put the permissions back on the drive and rebooted SQL Server and now everything works fine.

    Thanks for any responses to this post.


    Live to Throw
    Throw to Live
    Will Summers

Viewing 8 posts - 1 through 7 (of 7 total)

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