SQL Server Management studio issues
What will happen if the indispensible SSMS crashes or worse just hangs? A lot of fretting!! (For the not-so SQLCMD savvy)....!!
I have enclosed here two scenarios and their corresponding resolutions
PROBLEM 1
When we start up SQL Server Management studio we get the following pop-up:
"SQL server management studio has encountered a problem and needs to close."
After this the SSMS hangs and crashes
Enclosed below is the sample environment and also encapsulates the steps undertaken to resolve the issue.
ERROR MESSAGE (sample snippet)
From the Application event log:
EventType clr20r3, P1 sqlwb.exe, P2 2005.90.1399.0, P3 434f6009, P4 system.drawing,
P5 2.0.0.0, P6 4889dec2, P7 184, P8 20, P9 system.argumentexception, P10 NIL.
RESOLUTION
1) Launch SSMS by scheduling a task (The steps needed to do so are explained below)
In scheduled tasks, create a Task to launch Management Studio:
FOR 32-bit:
Administrative tools-->Schedule Tasks-->New task-->"C:\Program Files\Microsoft SQL
Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe"
FOR 64-bit:
Administrative tools-->Schedule Tasks-->New task-->"C:\Program Files
(x86)\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE\SqlWb.exe"
SQLWB is a 32-bit application, so it would be running under WOW mode on 64-bit system
IMP: Run the SSMS executable as user who is logged on (not NT AUTHORITY \SYSTEM)
2) If this works then move on to the next step
3) Create a Test user account and launch SQL 2005 Management Studio (Run As)
4) So this would definitely point fingers at a faulty user profile. What we could do then is move to the next step.
5) Create a new profile and try launching SQL 2005 Management Studio.
6) This should launch SSMS successfully.
CAUSE
What exactly is this User profile? It is a cornucopia of files created when a user logs on to a system (A fancy name for the collection of NTuser.dat, My Documents, Favourites, Cookies and History.... etc)
When a user profile is corrupt we will be unable to start any managed application and in our case it happens to be SSMS
Reference:
.NET Application may crash at startup for specific users if user.config file is corrupt
(https://support.microsoft.com/kb/956762 )
How to Use the AT Command to Schedule Tasks
(https://support.microsoft.com/kb/313565 )
PROBLEM 2
SQL Server Management studio starts up very slow. It takes about 5-7 minutes just to launch the login screen.
CAUSE:
SSMS has a high percentage of managed code, all of this code is signed by our signing authority when we ship it. When we launch SSMS we internally call a standard .NET framework 2.0 component .Thereby at start up (provided this setting is checked) the .Net runtime will try to contact crl.microsoft.com(https://crl.microsoft.com/pki/crl/products/CodeSignPCA.crl ) to ensure that the certificate is valid. If there is no internet connection or there is a problem contacting the certificate revocation list server then this will significantly delay SSMS start-up.
RESOLUTION:
There are two workarounds:
1. Uncheck certificate revocation.
Internet Options -> Advanced -> Security -> [ ] Check for publisher's
certificate revocation
Internet Options -> Advanced -> Security -> [ ] Check for server certificate
revocation
2. Configure internet connection for proxy:
Internet options -> Connections ->LAN Settings ->Proxy server
Amrutha Varshini
SE, Microsoft Sql Server
Reviewed by
Amit Banerjee
Technical Lead, Microsoft Sql Server
Comments
Anonymous
May 05, 2009
PingBack from http://asp-net-hosting.simplynetdev.com/sql-server-management-studio-issues/Anonymous
August 01, 2010
C:Documents and Settings<userName>Application DataMicrosoftMicrosoft SQL Server100ToolsShellwindows.prf can cause this behavior. Try clearing or renaming.Anonymous
August 16, 2010
can you just release a hotfix or something? this is ridiculous.Anonymous
April 29, 2016
This problem went away in my case after we applied all WINDOWS patches. I am not sure which exact patch killed it though.