1

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?

Improve this question
4
  • Is the sa SQL Server Login still in the sysadmin group? It could be a permission issue.
    – John K. N.
    Commented Apr 27, 2018 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
    Commented Apr 27, 2018 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
    Commented Apr 27, 2018 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
    Commented Apr 28, 2018 at 0:06

2 Answers 2

Reset to default
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.

Improve this answer
0
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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