0

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?

  • Is the sa SQL Server Login still in the sysadmin group? It could be a permission issue. – hot2use Apr 27 '18 at 9:04
  • @hot2use, yes it is "public" and "sysadmin", by the way i experience the same problem also loggin in with any another administrator user. – user193655 Apr 27 '18 at 9:54
  • After clicking the plus icon, do you get the error immediately (implying access or permission issues) or after some time (implying blocking or resource contention)? – Aaron Bertrand Apr 27 '18 at 15:27
  • I did a quick test and created a new database with an administrator account. After that I logged on to the server with an ordinary user and try to expand the new database. I get the same message as you get ("The database test is not accessible. (ObjectExplorer)") with the same callstack (at ....get_CanGetChildren() at ...RequestChildren(...) at ....BuildChildren(...)" (SSMS 12.0.5207.0 and SS 12.0.5207.0 EE). So at least I can confirm that missing rights can be one cause for this. – sticky bit Apr 28 '18 at 0:06
1

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.

0

I found the reason was my pc had an issue in resolving DNS, each query to SERVER\INSTANCE was going in timeout, using IP, Port solves the issue.

I did not find yet why the DNS does not work, but that was the issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.