SSMS 2012 Object Explorer hangs when expanding Databases node on a server with many DBs - by Ben Amada

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


18
0
Sign in
to vote
ID 768124 Comments
Status Closed Workarounds
Type Bug Repros 15
Opened 10/19/2012 5:06:17 PM
Access Restriction Public

Description

Over the last month, I've tried several times from SSMS 2012 to connect to 2 remote SQL Servers, one is 2005 and one is 2008, both at shared web hosts.  I'm able to connect, but right after connecting when I try to expand the Databases node in object explorer, it hangs and never expands (it's just busy working and becomes unresponsive).  As they are shared web hosts, they have a lot of DBs on them, and I suspect object explorer may be getting hung up on a DB there.  I always end up having to end SSMS via task manager.  With SSMS 2008, I was able to connect to both of these DBs at these remote servers and had no problems.

I am able to open a query window to the server ONLY if Object Explorer is not involved.  I can do this either via File -> New Database Engine Query, or by right-clicking on the server in the Registered Servers area and selecting New Query.  In both these cases, Object Explorer is not involved at all and I can then run queries.  However, I need Object Explorer too so I can easily work with the existing tables, procedures, etc.
Sign in to post a comment.
Posted by ALight on 3/31/2014 at 12:08 PM
I'm able to reproduce the hang, and yes, it only happens when the Object Explorer Details window is open. In fact, it occurs when I open the Object Explorer Details window with the remote server's node selected.

Here's the information Charles Gagnon (Microsoft) requested:

1. Run the following query against the server you're having issues with
SELECT @@VERSION
---
Microsoft SQL Server 2012 (SP1) - 11.0.3381.0 (X64)
    Aug 23 2013 20:08:13
    Copyright (c) Microsoft Corporation
    Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)


2. In SSMS select the "Help" menu and click "About...", then click the "Copy Info" button.
---
Microsoft SQL Server Management Studio                        11.0.3381.0
Microsoft Analysis Services Client Tools                        11.0.3381.0
Microsoft Data Access Components (MDAC)                        6.1.7601.17514
Microsoft MSXML                        3.0 4.0 6.0
Microsoft Internet Explorer                        9.11.9600.16521
Microsoft .NET Framework                        4.0.30319.18444
Operating System                        6.1.7601

Thanks,
Andre
Posted by wvoyek on 3/18/2014 at 12:58 PM
Same problem...seemingly random. Sometimes it expands instantly other times it hangs. There doesn't appear to be any correlating activity either on the server or the client running SSMS. Port 135 is open on the server. Deleting the %AppData% folder did not help.

Microsoft SQL Server 2005 - 9.00.4035.00 (X64)
    Nov 24 2008 16:17:31
    Copyright (c) 1988-2005 Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Microsoft SQL Server Management Studio                        11.0.3128.0
Microsoft Analysis Services Client Tools                        11.0.3128.0
Microsoft Data Access Components (MDAC)                        6.2.9200.16384
Microsoft MSXML                        3.0 4.0 6.0
Microsoft Internet Explorer                        9.10.9200.16798
Microsoft .NET Framework                        4.0.30319.18449
Operating System                        6.2.9200

William
Posted by SQLServerMonkey on 12/3/2013 at 7:56 AM
I can reproduce this issue, normally this issue arises on my system whether there are lots of databases or not. However all of our databases have a very large number of objects (several thousand).

It seems to tie in when memory usage reaches about 330 MB of memory being used.

I have captured a dump file of SSMS, if you can let me know where to upload it I will.

I do have the following add-ins for SSMS installed;

Redgate SQL Prompt
Redgate SQL Source Control
Redgate SQL Search
Redgate SQL Test

SQL Sentry Plan Explorer Plugin

I will attach a dump

SSMS About Info
---------------------------------------------
Microsoft SQL Server Management Studio            11.0.3393.0
Microsoft Analysis Services Client Tools            11.0.3393.0
Microsoft Data Access Components (MDAC)        6.1.7601.17514
Microsoft MSXML                                3.0 6.0
Microsoft Internet Explorer                        9.11.9600.16428
Microsoft .NET Framework                        4.0.30319.18408
Operating System                                6.1.7601
Posted by Daniel Buchholz on 10/16/2013 at 5:43 AM
Same problem here..

@@version:
Microsoft SQL Server 2000 - 8.00.2055 (Intel X86)
    Dec 16 2008 19:46:53
    Copyright (c) 1988-2003 Microsoft Corporation
    Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)


Microsoft SQL Server Management Studio                        11.0.3000.0
Microsoft Analysis Services Client Tools                        11.0.3000.0
Microsoft Data Access Components (MDAC)                        6.1.7601.17514
Microsoft MSXML                        3.0 6.0
Microsoft Internet Explorer                        9.10.9200.16635
Microsoft .NET Framework                        4.0.30319.17929
Operating System                        6.1.7601

And I can confirm having the Object Explorer Details pane closed indeed does help. And I must remember not not open it while I work on this server. If I open it, even the disconnect in Object Explorer will freeze Mgt Studio

Daniel
Posted by Frank Hell on 10/14/2013 at 10:41 PM
Rod's workaround worked for me too.
Posted by Michael K Campbell on 10/7/2013 at 2:17 PM
I found this Connect Item after looking into a situation where a co-worker's SSMS 2012 would keep shutting down after a bit when connecting to a remote SQL Server - and spamming their System logs with DCOM 10028 and 10010/10009 errors.

Ultimately, the title for this Connect item is technically correct - in that it's describing a behavior - but the ACTUAL problem here is that SSMS 2012 is expecting that EVERY SQL Server will accept DCOM connections over port 135 which is simply not the case. In fact, I'm wondering how this issue ever made it past Q&A.
Posted by Ed Lomonaco on 9/24/2013 at 12:59 PM
workaround did not work for me, still having problems
Posted by rgfuller on 9/23/2013 at 4:49 PM
Rod's workaround worked for me, but it broke F7 to open object explorer details.
Posted by Redi311 on 7/19/2013 at 12:03 PM
Rod's workaround worked for me. I'm running on Windows 8 Enterprise RTM and running SQL Server 2012 Developer Edition on my workstation. I have the latest client patches and the issue persisted even after upgrading my instance to SP1 CU5. My build number is 11.0.3373. I was connecting to 10.50.4000 sql 2008r2 instances experiencing the hangs. after applying Rod's workaround I can have the object explorer details open and connect without issue. Whether that will change after time or not is yet to be seen, but I wanted to confirm the workaround fixes my issue as well at this time.

@@version
Microsoft SQL Server 2012 (SP1) - 11.0.3373.0 (X64)
    Jun 29 2013 21:15:12
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )


Thanks! -Ryan
Posted by kookey47 on 7/5/2013 at 9:47 PM
I have now found that if I have the Object Explorer window and Object Explorer details window both closed when I start the SSMS and then connect to the remote database server it loads without any delays. Previously, when I had both those windows open before connecting it would take me 15 to 20 minutes for it to load.
Posted by Alexander Wurzinger on 6/18/2013 at 11:59 PM
Hi,
I forgot to mention, I don't have VS2012 installed only VS2012 and SQL 2012, but since installing VS2010 SP1 this happens less often for me
Posted by Alexander Wurzinger on 6/18/2013 at 11:57 PM
Hi,
I and a Coworker of mine are also experiencing this issue.
The Workarounds explained here don't work for me. (Neither Aoutclose, nor OE Detail-Pannel Closing)
But I found that it only happens if I have set the Default Database in the Connection Properties of the Registered Servers.
When I connect without the Registered Servers then it works fine for me.

The Mainly effected Server - @@Version:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1790.0 (X64)
    Apr 22 2011 11:55:34
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

My SSMS Info:
Microsoft SQL Server Management Studio                        11.0.3349.0
Microsoft Analysis Services Client Tools                        11.0.3349.0
Microsoft Data Access Components (MDAC)                        6.2.9200.16384
Microsoft MSXML                        3.0 4.0 6.0
Microsoft Internet Explorer                        9.10.9200.16599
Microsoft .NET Framework                        4.0.30319.18046
Operating System                        6.2.9200
Posted by jltrdavis on 5/28/2013 at 5:24 AM
Hello,

I have been experiencing this issue with SSMS 2012 as well. I can confirm that not opening the OE Details pane prior to connecting to the remote server is working for me.
Posted by Microsoft on 5/23/2013 at 2:42 PM
Hello all,

Do either of you use the OE details pane? After discussions with Ben we were able to find out that the issue he was having was because of the OE Details pane so if he closed that and restarted SSMS it allowed him to connect without the delay. (Note just closing it isn't enough, it has to have never been opened in that instance when you attempt to connect).

If anyone tries this and it works please let me know, I'm still unable to reproduce this myself so any confirmation of behavior are very much appreciated.

@mizotk1 - What do you mean it "returns the wrong table"? This sounds like a different issue than the others have been having.

Thanks,
Charles Gagnon (chgagnon@microsoft.com)
Posted by mizotk1 on 5/6/2013 at 9:56 PM
I found that I'm having the same problem.
Both Server & Client have SQL Server 2012 (SP1) .
SSMS 2012 returns the wrong table (dbo.EMP) when I expand table node, taking long time to return.
And I'm no longer be able to connect to the server via RDC.
I tied SSMS2012 as above, while I keep the connection (RDC), nothing happens in the remote screen.
The work around here didn't work for me.
I applied windows Update as well as reinstall SSMS2012.

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
    Dec 28 2012 20:23:12
    Copyright (c) Microsoft Corporation
    Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
Posted by JJTennessen on 4/15/2013 at 9:24 AM
I am also experiencing this issue. I have verified that AUTO_CLOSE is off for all databases (including system databases) on the servers I use. Here are the details requested by Charles Gagnon:
1. SELECT @@VERSION:
    Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
    Oct 19 2012 13:38:57
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
2. Help, About...:
    Microsoft SQL Server Management Studio    11.0.3349.0
    Microsoft Analysis Services Client Tools    11.0.3349.0
    Microsoft Data Access Components (MDAC)    6.1.7601.17514
    Microsoft MSXML                        3.0 5.0 6.0
    Microsoft Internet Explorer                8.0.7601.17514
    Microsoft .NET Framework                4.0.30319.17929
    Operating System                    6.1.7601
Let me know if any further information would be helpful in troubleshooting this.
Posted by Heng-yi Liu on 4/12/2013 at 5:17 PM
Disabling Auto_Close property doesn't help. Probably because I can only change my database, but there are still lots of other users' databases which I have no permission.

SELECT @@VERSION =>
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03
Express Edition with Advanced Services (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)

About =>
Microsoft SQL Server Management Studio 11.0.3000.0
Microsoft Data Access Components (MDAC) 6.2.9200.16384
Microsoft MSXML 3.0 6.0
Microsoft Internet Explorer 9.10.9200.16540
Microsoft .NET Framework 4.0.30319.18010
Operating System 6.2.9200
Posted by morshedk on 4/10/2013 at 5:51 AM
Hi,
I've just started experiencing this bug,

IsAutoClose = 0

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
    Jun 17 2011 00:54:03
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)



Microsoft SQL Server Management Studio                        11.0.2100.60
Microsoft Analysis Services Client Tools                        11.0.2218.0
Microsoft Data Access Components (MDAC)                        6.2.9200.16384
Microsoft MSXML                        3.0 6.0
Microsoft Internet Explorer                        9.10.9200.16519
Microsoft .NET Framework                        4.0.30319.18033
Operating System                        6.2.9200
Posted by Ben Amada on 4/4/2013 at 1:55 PM
Hi Charles. The object explorer hangs are happening to me against both a SQL 2005 and SQL 2008 database. I checked and both DBs report IsAutoClose = 0 (disabled). It's possible though another DB on either server has IsAutoClose = 1. I can only access my own DB on this shared DB server. Below is the @@version information for both SQL 2005 and SQL 2008. Below that is the Help -> About -> Copy Info information.

Also as noted in the above description, connecting to these DBs with SQL 2008 SSMS works perfectly fine. It's just SQL 2012 SMS that is hanging every time connecting to either one. Object Explorer in SSMS 2012 does work against separate SQL 2008 servers I access which each have a small number of DBs I have full control over.

Microsoft SQL Server 2005 - 9.00.4053.00 (X64)
    May 26 2009 14:13:01
    Copyright (c) 1988-2005 Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1 (Build 7600: )

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
    Jun 28 2012 08:36:30
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Microsoft SQL Server Management Studio                        11.0.3128.0
Microsoft Analysis Services Client Tools                        11.0.3128.0
Microsoft Data Access Components (MDAC)                        6.1.7601.17514
Microsoft MSXML                        2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer                        9.10.9200.16521
Microsoft .NET Framework                        4.0.30319.18034
Operating System                        6.1.7601
Posted by Microsoft on 4/3/2013 at 3:45 PM
Hello All,

First off for anyone experiencing this issue please verify that the AUTO_CLOSE property (run SELECT DATABASEPROPERTY('<dbname>', 'IsAutoClose'), 1 means enabled) is disabled. If AUTO_CLOSE is enabled for your DB's try disabling it and then opening the Database list in Object Explorer again.


If that doesn't work I'll need the following two pieces of information from anyone experiencing this issue so I can investigate further.

1. Run the following query against the server you're having issues with

SELECT @@VERSION

2. In SSMS select the "Help" menu and click "About...", then click the "Copy Info" button.

---

Then paste these two pieces of information in a reply to this thread.

Thanks,
-Charles Gagnon (chgagnon@microsoft.com)
Posted by Heng-yi Liu on 2/24/2013 at 1:41 PM
I have this same problem too. My shared hosting server is SQL 2008 R2 and it has lots of databases. And I only have access to my own database. When I use SSMS 2008 R2 to connect to this shared server, clicking databases node to expand all databases is very fast. However in SSMS 2012 SP1, it hangs when expanding the databases node.
Posted by Microsoft on 2/21/2013 at 9:37 AM
Hello Ben. Thank you for bringing this to our attention. We really do appreciate the feedback. We’ll investigate and get back to you. -Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)
Posted by GT5700 on 1/2/2013 at 2:30 PM
I can reproduce this by enabling the "Object Explorer Details" view. If I don't enable that view, I don't get the problem. I'm thinking the "Object Explorer Details" view is attempting to get additional information about the remote SQL server as it connects, and it fails. This may explain what _rod_ describes in the posted workaround regarding remote DCOM access.
Posted by Ben Amada on 11/10/2012 at 9:24 PM
The workaround posted by _rod_ was working for a few days, but 1 week later, Object Explorer is locking up again when connecting to 2 separate remote SQL servers - just like it was before. I have to kill SSMS each time I try to connect and expand the Databases node. I loathe deleting the profile folder again because I have to start over adding back preferred options. This and other issues in SSMS 2012 that simply did not exist in SSMS 2008 are frustrating.
Posted by Ben Amada on 11/3/2012 at 3:51 PM
The workaround _rod_ posted of clearing the MRU history appears to be working for me.