none
database msdb cannot be opened due to inaccessible files or insufficient memory or disk space - in recovery pending status

    Question

  • came in this fine monday morning and it looks like developers were running some kind of trace that filled the primary DATA folder with about 80,000 5mb trace files.  now that process has stopped and the logfiles have been cleaned up, but when attempting to connect to the server using the management console i get the error:

    Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details. (.Net SqlClient Data Provider)

    when i check the status it is in recovery pending mode.  i have a backup from yesterday but im not sure if this database became corrupt before that backup or not because this process was ongoing over the weekend.  the last timestamp on the msdb and log files is 7am this morning.. 

    I am not sure how to proceed recovering the msdb database with limiting interruption to the users.  any advice is extremely appreciated.  this is sql server 2008.  i can login via sqlcmd and see it is in recovery pending status:

    1> select state_desc databasestatus_sysdtabase from
    2> sys.databases where name ='msdb'
    3> go
    select state_desc databasestatus_sysdtabase from
    sys.databases where name ='msdb'

    databasestatus_sysdtabase
    ------------------------------------------------------------
    RECOVERY_PENDING

    (1 rows affected)
    1>

    Monday, August 24, 2009 5:03 PM

Answers

  • FIXED.

    so, as precaution i duplicated general problems with msdb on my personal vm to verify that restarting the service with those files having problems wouldnt create problems similar to starting without model, or master, and like you stated it didnt.  the server came up fine but on my machine i had removed the msdbdata / log files and then tested a restore while the agent was down doing a simple : restore database msdb from disk = ' $PATH' go at sqlcmd and it worked.

    HOWEVER, doing this on the production server with the problem DIDNT work, it returned an error saying there was a process using the file.  i stopped the sql server service, and restarted it, verified the agent was down, and attempted the restore again with the same error.  then i restarted the whole windows server and guess what, it all came up fine.  no need to restore, msdb came back online and was no longer in suspect/recovery_pending status.  strange, but sometimes you just have to reboot!

    thanks again for all your help, hopefull this helps someone else in the future with a similar problem.

    Monday, August 24, 2009 9:14 PM

All replies

  • Do what the mesage say. Read the messages in the errorlog file. Closely. And again. And go back several files (generations) trying to find the first time it happened. You want as much info as possible. It is likely that you are in for a restore of MSDB, and assuming you do regular DBCC CHECKDB, you will find when the most recent clean checkdb was performed, in the errorlog file. If not, then you just have to work yourself backwards... Restoring msdb shouldn't be special in any case, aslong as you stop Agent (cannot have any connection i the db when restoring).
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Monday, August 24, 2009 5:28 PM
  • Thanks for your response, the error log seems to coincide with the last timestamp on the msdb datafile and log:

    2009-08-24 07:00:01.22 spid5s      Error: 17053, Severity: 16, State: 1.
    2009-08-24 07:00:01.22 spid5s      LogWriter: Operating system error 112(There is not enough space on the disk.) encountered.
    2009-08-24 07:00:01.26 spid5s      Write error during log flush.
    2009-08-24 07:00:01.29 spid53      Error: 9001, Severity: 21, State: 4.
    2009-08-24 07:00:01.29 spid53      The log for database 'msdb' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

    the backup that occurred last night at 10pm was successful.

    when i checked the database with databasepropertyex msdb is marked suspect.  this is my first restore on a production system database, im wondering if a simple service restart might resolve it since it is in recovery_pending mode and the problem that probably caused this error no longer exists.

    what would be your course of action?  this is a virtual machine so my thoughts are:

    take a snapshot
    stop sql agent
    attempt running: restore database msdb
    or
    attempt restarting the sql server service and risk the service not starting because the database is in suspect mode


    Monday, August 24, 2009 5:45 PM
  • Having a broken msdb should not cause SQL Server to not start - the database is not *essential* for SQL Server (it is for Agent, but that is a different thing). I can't really say if restoarting the service will fix it. Recovery pending means that SQL Server can't recover msdb since some part of the ldf file is lost. I doubt that restarting the machine/service will magically re-create whatever was missing at that point in time. But you never know, of course.

    Have you done lots of work in msdb since your last backup? Creating jobs, SSIS packaged in msdb and such? If you don't ming loosing stuff in msdb since that backup, then I would probably go for a restore.

    ... but it is difficult to advice without having your hands on the system...
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Monday, August 24, 2009 6:01 PM
  • thanks again for your reply.  no, i created a scheduled job last friday but there is only the database backups and 2 jobs by me to store table growth info, so even if i had to recreate it *shudder* i wouldnt lose that much. 

    i missed a few lines in the error log right after the above lines:


    2009-08-24 07:00:01.29 spid53      Error: 9001, Severity: 21, State: 4.
    2009-08-24 07:00:01.29 spid53      The log for database 'msdb' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
    2009-08-24 07:00:01.88 spid53      Database msdb was shutdown due to error 9001 in routine 'XdesRMFull::Commit'. Restart for non-snapshot databases will be attempted after all connections to the database are aborted.


    that 'resolve any errors and restart the database' hopefully indicates restarting the service will fix the issue, if not since i am reassured at least sql server will come up i can attempt a restore after that.

    my plan is:
    1.  stop the agent,
    2.  restart the sql service, see if agent restarts, see if i can connect
    3.  if not services start but i cannot connect, attempt a restore.

    sound ok?  i greatly appreciate your advice, thanks again!





    Monday, August 24, 2009 6:15 PM
  • FIXED.

    so, as precaution i duplicated general problems with msdb on my personal vm to verify that restarting the service with those files having problems wouldnt create problems similar to starting without model, or master, and like you stated it didnt.  the server came up fine but on my machine i had removed the msdbdata / log files and then tested a restore while the agent was down doing a simple : restore database msdb from disk = ' $PATH' go at sqlcmd and it worked.

    HOWEVER, doing this on the production server with the problem DIDNT work, it returned an error saying there was a process using the file.  i stopped the sql server service, and restarted it, verified the agent was down, and attempted the restore again with the same error.  then i restarted the whole windows server and guess what, it all came up fine.  no need to restore, msdb came back online and was no longer in suspect/recovery_pending status.  strange, but sometimes you just have to reboot!

    thanks again for all your help, hopefull this helps someone else in the future with a similar problem.

    Monday, August 24, 2009 9:14 PM
  • Glad you fixed it. Spooky about that process using the file. I would use some tools (I believe sysinternals has things like process explorer) to find out who is using that file (too late now, though...).
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, August 25, 2009 5:08 AM