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:
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?