Today SharePoint managed to freak me out for a few minutes. I was looking at a customer’s farm and when I ran the PowerShell cmdlet Get-SPContentDatabase it was only returning one content databases even though my farm has two content databases. The output looked like this.

So then I thought about it and figured I had the problem where the account I was logged in as didn’t have access to the other databases. So then I grabbed Todd’s blog post on How to create a SharePoint 2010 admin account and stop using sp_farm because I knew it would jog my memory on how to give my account access to the databases. I ran through it and I still got just the two databases. UGH.

So then I figured when all else fails try Central Admin. When I went in to manage content database sure enough they were all there. But then that is when I saw a clue. The content database I couldn’t see was “Stopped”.

Surely that isn’t the issue? But I try anyway. I quickly set the database back to started and then run my PowerShell again.

That fixed it. Well, I guess you learn something new every day. The more we play with it the more it seems to be a lot of little things that happen when you leave a database in the stopped/offline state. I think as a rule of thumb leaving databases in this state is a bad idea. Some of the weirdness we have seen is with timer jobs, like the profile sync job, don’t run against those databases.

Why do people leave databases in the stopped state?

The main reason people do this is because they do not want any additional site collections to be created in that content database. And that is admirable goal if you are trying to control database sizes. The key is the better way to accomplish this is to set the maximum number of site collections to the current number of site collections. Same result without weird behavior like this.

Another way to see all of the content databases regardless of state

This comes from one of my developers named Jonathan Mast. He suggested instead of using Get-SPContentDatabase that I use Get-SPWebApplication | ForEach {$_.ContentDatabases} | Select Name to get all databases regardless of state. That works pretty well.

Todd would like you to know

If you use Get-SPDatabase | Select Name, Status SharePoint will give you a wonderful list of all of your SharePoint databases (config, services, and content) and show you their status. In this case my mystery database would have shown as Disabled. Another nice trick when fighting through a problem like this.

Hope this helps

Shane – SharePoint Consulting