Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2005
    Posts
    67

    Unanswered: DB in recovery due to lack of space. How to add file while DB is not accessible?

    Hi all,

    I ran a script and suddenly database ran out of space.
    It started to rollback the UPDATE statement, but it fails due to lack of space.
    I don't have the change to add more space now.
    The only thing I could do is to temporarely add a new datafile somewhere else, but DB is not accessible.

    The error message is:

    Database 'XXX' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945)

    The only way to solve this is to restore a full backup ?
    The idea would be to add a new datafile and then let SQL Server to perform the recovery of that DB, but how can I add more datafiles while DB is not accessible ?

    Thanks in advance.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You may be able to open the database in emergency mode (alter database (name) set emergency), then add the file immediately.

  3. #3
    Join Date
    Nov 2005
    Posts
    122
    How about detaching the database, moving the files to another disk, and then attach it again?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    That would be a bad idea, as the database would need to go through recovery before it can be attached. If it is detached, there is a fairly high risk that it can not be re-attached to anything, whether it has disk space or not.

  5. #5
    Join Date
    Nov 2005
    Posts
    122
    Quote Originally Posted by MCrowley View Post
    That would be a bad idea, as the database would need to go through recovery before it can be attached. If it is detached, there is a fairly high risk that it can not be re-attached to anything, whether it has disk space or not.
    Why do you think that would happen? I don't see how the database could go corrupt with a detach as long as the log and db-files are not corrupted and the db is not in SUSPECT state, and running of out disk space will not case a database to go SUSPECT. My only guess is that detach would fail because it will try to shut it down gracefully (running recovery).

    How about shutting down the SQL Server, moving the db files to a new disk, starting up SQL Server, deleting the database (which is now missing its files) from SSMS, and reattaching it?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Because of this line:
    Quote Originally Posted by djuritz
    It started to rollback the UPDATE statement, but it fails due to lack of space.
    We can tell that the database is not currently consistent, because it needs to have this transaction rolled back.

    From this line:
    Quote Originally Posted by djuritz
    The only thing I could do is to temporarely add a new datafile somewhere else, but DB is not accessible.
    (emphasis my own), we can pretty much tell that the database is currently marked suspect. Detach is a nice tool, but it does not allow for a lot of error. The worst case includes not being able to recover the database at all, and depending on the data, this can be an unacceptable risk.

  7. #7
    Join Date
    Nov 2005
    Posts
    122
    Quote Originally Posted by MCrowley View Post
    Because of this line:

    We can tell that the database is not currently consistent, because it needs to have this transaction rolled back.

    From this line:
    (emphasis my own), we can pretty much tell that the database is currently marked suspect. Detach is a nice tool, but it does not allow for a lot of error. The worst case includes not being able to recover the database at all, and depending on the data, this can be an unacceptable risk.
    1. As long as you have the transaction log, SQL Server will be able to put the database to a consistent state by rolling back and forward commited/umcommited transactions.
    2. If the database state is RECOVERING or RECOVERY_PENDING, it is also unavailable, but it doesn't mean that it is corrupt.

    djurits, can you post the result of
    select name,state_desc from sys.databases

    If state_desc is RECOVERING or RECOVERY_PENDING you can safely stop the SQL Server, move the database and transaction log files for you troubled database to a new disk. Start SQL Server and delete the database, the reattach.

  8. #8
    Join Date
    Nov 2005
    Posts
    122
    I do agree that detach would be a bad thing if the database is SUSPECT, since corrupt or missing files is most probably causing the SUSPECT state. A restore would be the best solution.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •