All systems are susceptible to failure. No software is perfect, and therefore it is natural that SSMS too will fail. Today, I have put together some of the most common failures that I have experienced with SSMS and their known workarounds.

The reason I have kept this part of the tutorial as a L200 is because the workarounds and tips from this post should be used with caution.

General troubleshooting

The Object Explorer is not visible/hidden

The most common scenario when my Object Explorer sometimes goes missing is when I switch back to my standard laptop resolution after projecting using a projector or on a larger screen.

Obviously, the first step in troubleshooting a hidden object explorer is to make sure the Object Explorer is indeed enabled by toggling it using the F8 key or by using the View menu.

If this does not resolve your issue, then the problem starts becoming a bit ugly. Save all your work within the SSMS, and restart the SSMS. In most cases, restarting SSMS restores the Object Explorer.

An important criteria for any recovery mechanism is to make as little collateral damage as possible. Restarting a system allows the system to preserve user data, while repairing the point of failure successfully. It is therefore that standard protocols like restarting systems are developed. However, in some cases, it becomes impossible to recover the system completely. Over the many years of my experience with SQL Server, I have faced such a situation with SSMS only once. If the components of SSMS simply fail to become visible on screen, the last option left is to give the system a “shock” by removing all customizations (repositioning, resizing, etc) applied. This can be done by using Windows –> Reset window layout option.

Using the “Reset Window Layout” clears out customizations to the default window positions and sizes and takes the SSMS back to the default state.

Possibly large number of connections when only one SSMS instance is connected to the SQL Server

When working with SSMS, you might find that while only one instance of SSMS is connected to the SQL Server, the number of connections being opened up is not proportional. The reason is that SSMS does a tradeoff between features and additional connections. The Object Explorer, Object Explorer details and other such “heavy-weight” windows open their own dedicated connection to the SQL Server instance.

You can read about my little experiment regarding the multiple connections of SSMS here: http://beyondrelational.com/blogs/nakul/archive/2011/01/17/the-multiple-connections-of-ssms.aspx

Avoid the urge to use all features at the same time on a production server, or during troubleshooting – not only will it confuse you, but also might slow down the SQL Server (if the server is under pressure of some sort).

Finally, if the SQL Server has a database which is in single-user mode, it will be available to only one of the multiple sessions that SSMS starts. The trouble is that which connection will come first is unknown. Hence, it is always a good practice to have nothing but the query editor open when working with either single-user databases or single query modes like the DAC (Dedicated Admin connection).

So, configure your environment wisely, and the power of SSMS will be with you.

Clearing the SSMS History

For those of you who work with multiple SQL servers with multiple logins, the “Connect to Server” window seems to fill up very quickly. Sometimes, these servers might not even be in existence (e.g.. if you frequently refresh your virtual RND/test environments).

For those using the yet-to-be-released SQL Server 2012 (code named “Denali”), you can select the value and hit the delete key just as you would delete the entry from any other drop-down. However, if you are using SSMS for SQL Server 2005 (in which case you should upgrade) or SQL Server 2008/2008 R2, you can refer my post on clearing SSMS history here: http://beyondrelational.com/blogs/nakul/archive/2011/02/17/clearing-ssms-history.aspx.

Advanced Troubleshooting – Troubleshooting the T-SQL Debugger

In 2011, I presented a session on the “Underappreciated Features of Microsoft SQL Server” during the Exclusive SQL Server Webcast series on Security & Scalability. One of the items that I demoed in the VTD was the T-SQL debugger. The T-SQL Debugger exists at least since SQL Server 2000 and as the versions progress, we can see a lot of changes being made to the debugger. While preparing my environment for the T-SQL debugger, I encountered multiple issues related to firewall security and sequence of the Visual Studio versions installed. You can find my posts on each of these errors here:

• Enabling T-SQL Debugger in SSMS
• T-SQL Debugging – Connection Errors & Firewall settings
• T-SQL Debugging – SSMS Errors – MSDBG32.dll

By the way, you can find a recording of my VTD session here:

• PPT here: http://beyondrelational.com/media/p/12497.aspx
• Webcast Download:http://download.microsoft.com/download/6/0/C/60CBF7F0-5558-4C00-ACC0-ABFF25A92244/Day5_SQLServer webcast.wmv

Concluding the series

This concludes my series of tutorials on SSMS. Last year, a lot of engineers who had never worked with SQL Server came up to me looking for some guidance on the SSMS interface. My goal therefore was to focus on features that would make their lives easier and make them feel comfortable within the confines of the SSMS IDE. I hope that these tutorials will help all the budding SQL Server enthusiasts.

If these tutorials helped you, or if you have any suggestions, do let me know. Your feedback is most welcome.

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here! | Tweet to @nakulv_sql