SQL Server Management Studio 2012 hangs

Question

When I click on the "Databases" node in "Object Explorer" it just keeps on "Loading items" until at some point it just hangs.

This happens only when connecting to a remote server, not when accessing a database on my PC.

It also doesn't happen with any other node.

The guys at the web-hosting company didn't have any trouble with it. (But they're running 2008, and so is the SQL server there)

I reinstalled the whole SQL server etc. but to no avail.

What might be the problem?

Answer 1

I experienced this same problem: when accessing a remote server with the Object Explorer, SSMS would hang indefinitely. The Windows System Event Log would show DCOM error 10009 ("DCOM was unable to communicate with the computer MACHINE_NAME using any of the configured protocols.").

The solution was to clear the MRU history and other settings from my profile. To do that:

1. Close any open instances of SSMS 2012
2. In Explorer, open "%AppData%\Microsoft\SQL Server Management Studio"
3. Rename the "11.0" folder to something else, like "11.0.old"
4. Open SSMS 2012

You'll see that your MRU list has been cleared. You should then be able to re-enter your credentials and use SSMS as normal.

If everything works, you can delete the renamed folder. Otherwise, delete the new "11.0" folder that was created and rename the original one back to "11.0".

I have no idea whether it's actually the MRU list that's causing this problem or if it's some other profile data.

We were able to discover that SSMS is trying to make a DCOM connection over port 135 to the SQL Server (perhaps for SSIS, T-SQL Debugging, or something else). Our firewall was configured to block port 135. By opening the port in the firewall we were able to use SSMS (hence the reason it worked against local databases but not remote ones). Unfortunately, an open port 135 is an invitation for a lot of attacks, so that wasn't a practical solution for us.

Answer 2

Turn Auto-Close off on all the databases. Worked like a charm to me! Every time you expand or refresh the database list, server has to awake the databases causing the hang.

Just run this to find all the databases that have auto-close on

SELECT name, is_auto_close_on
FROM master.sys.databases AS dtb 
WHERE is_auto_close_on = 1 
ORDER BY name

Credits to http://social.msdn.microsoft.com/Forums/sqlserver/en-US/99bbcb47-d4b5-4ec0-9e91-b1a23a655844/ssms-2012-extremely-slow-expanding-databases?forum=sqltools

To turn-off this setting for a database - Right click on database instance in object explorer -> Click properties -> Click "Options" in left navigation pane in database properties window -> Change the value of Auto Close property to "False" in right pane as shown in the snapshot below:

Answer 3

Assuming you have access to only one database at the hosting company (which is almost always the case, at least with a certain username/password), you can avoid the need to use the dropdown at all by setting your registered server to default to the database you're supposed to access:

(It may take longer here, too, but this will be one-time. You can also type it instead of waiting for the list to populate.)

This way, even if the login the host created for you routes you to tempdb or something by default, Management Studio will still put you in the context of your database.

I see now that you are talking about the Object Explorer node, not the "Use database" dropdown that I somehow interpreted incorrectly. An exercise to try might be to highlight the databases node (don't expand it) and click on F7 (Object Explorer Details). If this loads for you then it can be an alternative to navigate through the hierarchy and, as a bonus, you can show lots of entity attributes here and also multi-select, two things you have no control over in Object Explorer.

If that doesn't help, then your host should be helping you better than they appear to be. If SSMS 2012 is supported then they should be able to test this in SSMS 2012 and confirm or deny that they can reproduce it. If it is not supported then I think your recourse is to install SSMS 2008 as well (they can co-exist) and use it for managing this specific server.

Of course, just about anything that you can do in Object Explorer (and plenty of things you can't), you can do by using the catalog views and/or DMVs. So before you determine what to do, you may want to review (or share with us) exactly what you are using Object Explorer for - if there is a way to do it without Object Explorer, you might like the workaround better than having two versions of the tool (since the improvements in 2012 SSMS have absolutely nothing to do with Object Explorer).