January 3, 2013 at 8:57 am
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
January 3, 2013 at 9:15 am
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
January 3, 2013 at 9:24 am
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.
January 3, 2013 at 9:27 am
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
January 3, 2013 at 9:31 am
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.
January 3, 2013 at 9:34 am
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
January 3, 2013 at 9:42 am
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
January 3, 2013 at 9:47 am
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...
January 3, 2013 at 9:50 am
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
January 3, 2013 at 10:00 am
I must admit I'm pretty bad with right permissions...
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>]
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?
January 3, 2013 at 10:02 am
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
January 3, 2013 at 10:05 am
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
January 3, 2013 at 10:16 am
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
October 2, 2024 at 1:54 pm
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:
Please this is a PRD env't, your thoughts is greatly appreciated.
October 2, 2024 at 1:55 pm
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:
Please this is a PRD env't, your thoughts are greatly appreciated.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply