SQL Server Management Studio – Impossible to Expand Database

sql serversql-server-2008-r2ssmstroubleshooting

I am experiecing a strange problem. I have a SQL Server 20008 R2 instance that recently (after years of running smoothly) started to become slow.

One symptom that I notice is that when working from SQL Server Management Studio, on the server itself and logged as sa, I click on the + symbol near the database icon, after clicking I get an error and after OK I see that the "+" is disappeared, you can see in this screenshot how it looks like after I tried on four databases:

database tree

The error I get is (in Italian):

Impossibile accedere al database MyDatabase. (ObjectExplorer)
------------------------------
Percorso programma:

   in Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.DatabaseNavigableItem.get_CanGetChildren()
   in Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItem.RequestChildren(IGetChildrenRequest request)
   in Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ExplorerHierarchyNode.BuildChildren(WaitHandle quitEvent)

It means "Impossible to access the database MyDatabase".

This is just a symptom as I said, the main issue is that from a client the connection is slow, very slow but working.

I restarted the SQL Service service for this specific instance.

This is a named instance, I have also the default instance that works fine.

Can anyone give me a pointer to a solution please?

Best Answer

Open a new query window and run SELECT * FROM sys.databases. See if the database in question is listed in the results.

If it is, see if you can access it by running USE MyDatabase. If that works, see if you can pull up a list of tables by running SELECT * FROM sys.tables.

You may want to run DBCC CHECKDB MyDatabase and see if any problems are detected.

Related Question