SQLServer 2012 Installed copy showing problems:

Question

My problem is like this: I had a copy of SqlServer 2012 installed on my machine. It's been there for over 3 years without any glitches at all. Just 4-5 days ago, a problem sprouted up. When I started Management Studio it told me that

msdb got corrupted so it cannot be opened.

The complete message is something like this:

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

So what could be wrong here? What sudden changes/anomalies could have crept in that has made this unstable? Someone told me it could be due to a wide range of possibilities. The reason could be anything. Even some nuget packages affect the database. Initially I though this could have been an issue with login, permissions etc. So I tried to run as administrator also. No, it did not cure this problem. If you try to create a new database it simply tells me, that I can't do it. The message is something like this:

An exception occurred while executing a T-SQL statement or batch.[Microsoft.SqlServer.ConnectionInfo]. Database msdb cannot be opened. It has been marked as SUSPECT by recovery. [Microsoft Sql Server, Error:926]

How do I recover from this? Can you provide me some guidance? Or a clue where precisely to look for the hints of problem? All my work is stalled. Any kind of assistance in recovering my ailing sqlserver installation will be humbly received.

So, I'm requesting you all to show me the way. Thanks in anticipation.

Answer 1

I fixed mine with Solution C from the following website. my MSDB was corrupt and not loading so I stopped the services and replaced it with the files from the template in the SQL Server directory.

https://www.mssqltips.com/sqlservertip/3191/how-to-recover-a-suspect-msdb-database-in-sql-server/

"The templates are saved in "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Templates" (the path varies by version and install choices, this is the default for SQL Server 2012). By shutting down the instance and replacing the bad MSDB data (msdbdata.mdf) and transaction log (msdblog.ldf) files with the template files I was able to restart the instance without error!" (just incase the website link doesn't work I have quoted it here).

Fissh

Answer 2

If your MSDB is corrupted, restore from your most recent backup. That's the safest thing to do and that's why we have backups to begin with.

If you do not have a backup of MSDB, you have a couple of options.

1. Recreate it. Detailed instructions here: https://msdn.microsoft.com/en-us/library/dd207003(v=sql.110).aspx#CreateMSDB. This is the best way to ensure you get a clean, functional MSDB and is the fastest way to get up and running again. IMPORTANT: Doing this means you lose all jobs, backup history, etc... that is stored in MSDB. Remember to recreate all maintenance jobs after you're done else you're just waiting for the next thing to fall over (e.g. transaction log backups no longer run, tlogs grow till you run out of disk space - now you can't run any queries that will commit transactions).

2. DBCC CHECKDB WITH Repair_allow_data_loss is another option which you'll probably find if you google/bing the issue. This might work but it is not recommended. The problem is you don't really know what will be lost. It works by deleting what it can't read then fix the links to get the database physically functional again. Once that's done, you'll have to go back and figure out what remains and is still functional. That is tedious and error prone. Besides, if you're gonna do this very thorough manual check to ensure all your jobs are intact, you're better off just re-creating them on a new, clean MSDB.