MSDB stuck in recovery mode

  • Hi, i was following this guide[/url] for moving msdb to a new location.

    When i restarted the SQL Server, msdb got stuck in (recovery pending) mode. Any idea why?

    Btw, when i run:

    BEGIN TRAN

    SELECT

    NAME,

    PHYSICAL_NAME AS 'PhysicalFilePath',

    STATE_DESC AS 'DB Status'

    FROM SYS.MASTER_FILES

    ROLLBACK

    I get that the mdf and its ldf is online:

    MSDBDatac:\mypath\MSDBData.mdfONLINE

    MSDBLogc:\mypath\MSDBLog.ldfONLINE

    Thanks

  • Check the permissions on the path and validate that the path is correct (file names included). Also make sure the files have been copied to the same path location specified in the query results you are getting.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Do you mean windows permission?

    I get this error when i try to connect to the server:

    Cannot display policy health state at the server level, because the user doesn’t have permission. Permission to access the msdb database is required for this feature to work correctly.

    I use windows autothication login: server\user that has full rights to the new folder where msdb is placed.

  • ensure that the sql service account has Windows permissions on the Folder you moved the files to. Also, verify that the place you moved the files to is the same one that is listed in the output of your query you provided.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I'm sure that the paths are right, I've pasted them from the query into windows run window and it found both of the files.

    Is there any way to test if my user has sufficient rights to the DB?

    I'm using a standard windows account, with full administrator rights.

  • Is the database still in recovery? If it is then check the folder permissions in windows for the SQL Service Account.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Recovery pending means SQL cannot open one or more of the files.

    Check the error log for details. There will be details of what file and what error.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • It is still in recovery...

    However, Ive checked permissions of the origin folder and they had MSSQLSERVER as a user.

    Ive tried to search for that user but couldn't find it.

    Found and added this two accounts:

    SQLServerFDHostUser$servername$MSSQLSERVER

    SQLServerMSASUser$servername$MSSQLSERVER

    but still can't connect to msdb...

  • memymasta (1/3/2013)


    It is still in recovery...

    However, Ive checked permissions of the origin folder and they had MSSQLSERVER as a user.

    Ive tried to search for that user but couldn't find it.

    Found and added this two accounts:

    SQLServerFDHostUser$servername$MSSQLSERVER

    SQLServerMSASUser$servername$MSSQLSERVER

    but still can't connect to msdb...

    You don't need to add those accounts. You would want to ensure that the Service Account shown in services control panel has permissions.

    As Gail said, please check the log for errors and post those errors.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I must admit I'm pretty bad with right permissions...

    @gilamonster

    This is the error from windows logs:

    Login failed for user 'Server\User'. Reason: Failed to open the explicitly specified database 'msdb'. [CLIENT: <local machine>]

    @SQLRNNR

    There's so many SQLServer services in control panel (which one is the service account?)... but i suppose SQL Server (MSSQLSERVER) is the one to look out for...

    It states that the process is logged in as NT Service\MSSQLSERVER.

    Should i add rights to the new folder or change rights in control panel?

  • SQL Server error log. There will be messages about being unable to open a file. Check there, post the messages.

    Not interested in the login failure. It's an effect, not a cause and uninteresting.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Check the SQL Log in SSMS for errors regarding msdb that are not like the one you just posted. You are getting that error because msdb is in recovery.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Yey! Added "everyone" as a user, restarted the server and msdb works now!

    Well, now we know for sure that it was rights issue, ill figure out which account to add tomorrow...

    Thanks for the help guys! Really, you always help me out!

    Love this forum <3

  • I am also having issues in my production environment where I just noticed that my msdb database is in recovery pending state and seems like backup has failed and now SQL Agent is gone from SSMS.

    This is the only error log in SQL Log:

    Source Backup Message BACKUP failed to complete the command BACKUP DATABASE msdb. Check the backup application log for detailed messages.

    The error in Event Viewer:

    1. BACKUP failed to complete the command BACKUP DATABASE msdb. Check the backup application log for detailed messages.
    2. Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr = 0x80040e14. SQLSTATE: 42000, Native Error: 3013 Error state: 1, Severity: 16 Source: Microsoft SQL Server Native Client 11.0 Error message: BACKUP DATABASE is terminating abnormally. SQLSTATE: 42000, Native Error: 945 Error state: 2, Severity: 14 Source: Microsoft SQL Server Native Client 11.0 Error message: Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

      Please this is a PRD env't, your thoughts is greatly appreciated.

  • I am also having issues in my production environment where I just noticed that my msdb database is in recovery pending state and seems like backup has failed and now SQL Agent is gone from SSMS.

    This is the only error log in SQL Log:

    Source Backup Message BACKUP failed to complete the command BACKUP DATABASE msdb. Check the backup application log for detailed messages.

    The error in Event Viewer:

    1. BACKUP failed to complete the command BACKUP DATABASE msdb. Check the backup application log for detailed messages.
    2. Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr = 0x80040e14. SQLSTATE: 42000, Native Error: 3013 Error state: 1, Severity: 16 Source: Microsoft SQL Server Native Client 11.0 Error message: BACKUP DATABASE is terminating abnormally. SQLSTATE: 42000, Native Error: 945 Error state: 2, Severity: 14 Source: Microsoft SQL Server Native Client 11.0 Error message: Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

      Please this is a PRD env't, your thoughts are greatly appreciated.

Viewing 15 posts - 1 through 15 (of 16 total)

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