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