29

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?

ispiro
  • 26,556
  • 38
  • 136
  • 291
  • Do you have access to more than one database, with those credentials, at the hosting company? (Usually this is not the case.) – Aaron Bertrand Jun 10 '12 at 18:02
  • Also can you define "hangs"? Does that mean you've just given up on it, or does it actually go into "Not Responding" mode? Have you run some kind of network profiler to see what data is being transmitted during this time while you are waiting? It could be blocked at the other end, or it could just be transmitting data v e r y s l o w l y... – Aaron Bertrand Jun 10 '12 at 18:17

13 Answers13

45

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.

Jaecen
  • 855
  • 1
  • 9
  • 17
  • 1
    Deleting the 11.0 folder worked for a couple of days, but 1 week later it's back to how it was initially with Object Explorer completely locking up when trying to expand the Databases node, or even before I get a chance to do that. It's happening with 2 separate SQL server instances I try to connect to that are at shared web hosts with a lot of other DBs on them. The same 2 SQL server instances I was able to connect to without any issues in SSMS 2008. I'll probably delete the 11.0 folder again, but a pain to do because I need to add all my settings back in. – Ben Amada Nov 11 '12 at 05:28
  • The only permanent solution would be to open port 135 on your firewall, but I don't recommend that. – Jaecen Dec 04 '12 at 18:49
  • Thanks a lot! After deleting 12.0 folder for SSMS 2014, everything works faster – veljasije Mar 27 '15 at 13:35
6

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:

Auto close option in database properties window in SQL Server 2008 R2

RBT
  • 24,161
  • 21
  • 159
  • 240
Nuno Agapito
  • 220
  • 3
  • 12
  • 1
    Thanks. If this is true - that a database must be woken up to appear in the Object Explorer window - I wonder _why_ it's like that. I'd like to see it and _then_ choose what database I need... – ispiro Jan 31 '14 at 12:46
  • Seems SSMS previous to 2012 didnt had the need to do this. 2012 must do something that requires the DB to be loaded... – Nuno Agapito Jan 31 '14 at 16:53
  • Also, googling it around, seems that having databases on auto-close is a very bad practice... I switched all mine (had like 40 databases) and the waiting time went from 1-2 minutes to 0 seconds :) – Nuno Agapito Jan 31 '14 at 16:55
  • Spot on mate! I have over 1,000 databases on each SQL server! and was taking around 40seconds to expand databases! ran script form https://support.managed.com/kb/a402/turn-off-auto_close-on-all-ms-sql-databases.aspx and now it opens within a second! only 40 on each server were actually set to auto close too... Regards and many thanks Liam – Liam Wheldon Dec 05 '14 at 13:29
  • It actually works like a charm. +1. For me it was happening whenever I was deleting a localdb database from (localdb)\v11.0 instance. At times it shows a message box showing message "Object reference not set to an instance of an object" just after deleting localdb database. When this popup message comes then only object explorer gets hung-up and rest of the UI is responsive. But at times when this message box doesn't come up then whole of the UI becomes unresponsive. – RBT Jun 06 '16 at 22:28
5

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:

enter image description here

(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).

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thanks for the detailed answer. / I tried putting the database name in the "Connection Properties" – it just connects to the server like before. / I tried "Object Explorer Details" – same problem. / The web-host does not support 2012. / I am currently downloading SSMS 2008 but would like to avoid that option for fear that it might interfere with 2012 (Is it documented that the 2 can coexist?). / As for catalog-view and DMV – sounds promising – what are they? / And thanks again. – ispiro Jun 10 '12 at 18:56
  • I don't know if it's documented but as long as you're not using Visual Studio or BIDS etc. then I can give you my personal assurance that they will not break each other (though I am not 100% sure who will win out in preferences / keyboard customizations etc. if you install 2008 second - I've always done it the other way around). I don't think using the 2008 version will change anything, but it will at least give you a legitimate gripe that you can break it in the version they support. They should be able to try to reproduce from *outside* of their cozy little network (where I'm sure it works). – Aaron Bertrand Jun 10 '12 at 18:58
  • As for your comment above – After a long time I have to use "Task Manager" to end the process. / As for "network profiler" – wish I knew what that is. – ispiro Jun 10 '12 at 18:59
  • Catalog views are the metadata for your database. So if you want a list of tables, `SELECT * FROM sys.tables`, procedures, `SELECT * FROM sys.procedures`, etc. http://msdn.microsoft.com/en-us/library/ms174365.aspx / http://msdn.microsoft.com/en-us/library/ms189783.aspx – Aaron Bertrand Jun 10 '12 at 19:00
  • I _am_ using Visual Studio. And Visual Web Developer. – ispiro Jun 10 '12 at 19:00
  • Then I will not attach any assurances. :-) I have used both in side-by-side mode when using Management Studio only. If you're using Visual Studio 2010, I have heard of side-by-side issues that are only resolved if you apply Visual Studio 2010 SP1 (or again, if it is already applied). I would do this before opening either copy of Management Studio again. If you're using an older version of Visual Studio, sorry, but you're out of my depth. – Aaron Bertrand Jun 10 '12 at 19:02
  • Has been applied for a long time. I think I'll try that option. Thanks again for all your help. And using VS2010. – ispiro Jun 10 '12 at 19:03
4

In my case deleting the profile folder worked exactly once. The next time I opened SSMS 2012 it would freeze again when connecting to a server. SP1 didn't fix this either.

That was until I found the following simple workaround described on a ticket by Ben Amada over at connect.microsoft.com: Always close the Object Explorer Details before closing SSMS 2012.

So the complete workaround for me is this:

  1. Follow Jaecen's answer, but close SSMS 2012 again after it created a clean profile folder
  2. Apply Hoodlum's recommendation and copy SqlStudio.bin from the old profile folder to the new one (the old profile folder can be deleted afterwards)
  3. Everytime before closing SSMS 2012 make sure the Object Explorer Details window is closed

The first two steps are required only once, or if the Object Explorer Details window was left open accidentally.

Edit

I just noticed that closing the Object Explorer Details window is also required when (re-)connecting to an SQL server in the same SSMS session. So basically whenever connecting to a server the Object Explorer Details windows has to be closed.

ecdsa
  • 542
  • 3
  • 12
4

I spent over a month with Microsoft SQL Support troubleshooting this. It has been submitted as a bug.

I have both SQL 2012 SSMS and VS 2012 installed on Win 7 (64).

Deleting the profile folder never worked for any reasonable length of time.

The workaround we found was to ensure that my SSMS profile defaulted to the Master database when connecting. It appeared to have something to do with the fact that I'm connecting with Windows Authentication and I belong to more than one AD group that have SQL permissions assigned AND I don't have SQL specific permissions set up on my AD account.

PTansey
  • 567
  • 4
  • 12
2

I am connecting to several remote servers rangig from 2000 to 2012. SMSS on local PC is SQL Server 2012,SMSS is 11.0.2100.60

SSMS freezes several times a day.When this occurs, I go via RDP to the local server / SMSS / Activity Monitor and kill the processes from my PC with Database Name = master, one at a time, until SMSS on my PC unfreezes.

This always works, however, a cure for the disease raher than the symptoms would be highly welcome.

Herbert Tobisch
  • 41
  • 1
2

Have some SQL Servers from 2000 to 2012, access then through SMSS from my desktop. Problem occurs with varying frequency, looks like this: when I collapse a server in object explorer, SMSS freezes.

looking in activity monitor on the server in question, i find a process in master db with host = my desktop executing the following query

SELECT dtb.name AS [Name] FROM master.dbo.sysdatabases AS dtb ORDER BY [Name] ASC SMSS

killing the process frees SMSS.

Herbert Tobisch
  • 41
  • 1
1

Here is what worked for me Open SSMS click on connect to object explorer button in the connect to server dialog box expand options >> click reset all Done!

Koko
  • 21
  • 2
1

I've test approximately all above answers but my SSMS got stuck in expanding the database list. I found the problem finally. The problem was because of a database that I restored it but It did restore correctly at the end. Then When I expanded the database list it was sticking.

I run a the query 

SELECT 
dtb.name AS [Name]
,dtb.database_id AS [ID] 
,CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible] FROM master.sys.databases AS dtb

Then the result took too long and at the end timed out but When I filter the stuck database I got result.

SELECT 
dtb.name AS [Name]
,dtb.database_id AS [ID] 
,CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible] FROM 
master.sys.databases AS dtb 
Where name <> 'StuckDB' ORDER BY [Name] ASC

At the end I decided to detach StuckDB to solve my problem.

SeeSharp
  • 608
  • 1
  • 13
  • 21
0

I have now applied SQL 2012 Service Pack 1 (through Windows Update) and it seems to work fine now, though it does take a very long time to load.

ispiro
  • 26,556
  • 38
  • 136
  • 291
0

"Open SSMS click on connect to object explorer button in the connect to server dialog box expand options >> click reset all" - it works

user3331508
  • 1
0

I solved this problem by changing my default database back to master.

Tuan
  • 5,382
  • 1
  • 22
  • 17
0

Go to database properties at SSMS and change compatibility to 2012. Then check.

enter image description here

Kavi
  • 181
  • 1
  • 6