Blog

It’s crucial for database administrators to recognize signs of pending doom.  Make sure you’re able to quickly spot problems in SQL Server Management Studio by studying these screenshots.

Databases that haven’t been backed up in 24 hours

Databases with old compatibility levels

Databases running out of space on the C drive

SQL Server with Priority Boost enabled

Databases infested with zombies, cockroaches, and zombie cockroaches

What’s that?  You can’t see the differences in the screenshots?  That’s because SQL Server Management Studio doesn’t show you serious problems.

The more I learn about SQL Server and the more customer servers I examine, the more frustrated I get with SQL Server Management Studio.  It’s the year 2012, and the product’s been out for well over a decade – yet dangerous settings like Priority Boost and Lightweight Pooling don’t even have warnings in the GUI.  Just enable ‘em, click OK, and your server’s performance and reliability can go right down the toilet without so much as a warning icon in SSMS.

When you’re facing performance or reliability problems, your first step should be to run our free sp_Blitz® stored proc to show you this stuff.  It just breaks my heart when we do our SQL Server health checks, find these problems within the first few minutes, and everyone’s surprised that someone enabled these obscure options long ago without telling anyone.

sp_Blitz®: because no one likes zombie cockroaches in the database server.

↑ Back to top
  1. It almost makes you wonder if Microsoft is leaving some of these simple warnings because they want to keep the 3rd Party Companies going.

    At the same time, if you have a hundred developers all using SSMS with read only access, you could drown the database by trying to display all the warnings. So some fore thought would definately be needed.

    Here’s to SQL 2016!

  2. You’re not really surprised, right? After all, Microsoft built SSMS on top of a VS shell. Translation: development, development, development. It’s not optimized as an operational tool. Sure, we’ve got some bells and whistles that help, but let’s face it – the majority of the innovation into SSMS has been on the developer side.

  3. I don’t look at SSMS as an administrative tool; rather it’s a development tool. Sure, if you are a sysadmin you might want to glean some of this information via SSMS (in fact you can review Compat Level by clicking on Databases and Object Explorer Details).

    Microsoft provides the plumbing to get this information, but as one poster noted I think they are leaving the UI portion up to 3rd parties like Quest, Idera, Red Gate, etc. It’s also possible that not providing these sorts of capabilities native (fancy dashboards, etc.) keeps the cost of the base product down.

    I personally like this model – don’t bloat the price tag of the product by introducing a bunch of fancy dashboards, but if you want you can go purchase the one you desire. The cost of 3rd party tools can often be justified by sheer efficiency – I save countless hours using Spotlight, which has been my tool of choice for a good six years. To each their own. Some may just want to use native monitoring methods.

  4. I also do wonder if 3rd party vendors are behind missing or botched features (MDW, UCP etc)

    zombie cockroaches: crunchy on the outside, soft on the inside.

  5. I kept staring at all those screen shots and trying to find the difference; like one of those games you played as a kid. I learned the unfortunate truth of it all when I read what was below!
    I was wondering what new features you discovered in management studio, thanks for the disappointment :)
    Going to be looking at sp_blitz next week (hopefully). Thanks for the heads up.

  6. I watched the Quest video awhile back and was introduced to sp_Blitz and the chest hair wig in a single afternoon. A real beauty and the beast moment. Anyway, I loved it then, love it now, even answered a Stack Overflow question just yesterday using it as an example.

    http://stackoverflow.com/questions/12790196/vs-style-snippets-in-ssms-2012-do-they-exist/12790750#12790750

  7. Oh and by the way… people may not like zombie cockroaches in the database server but a little bird told me that Robert Davis likes the occasional raccoon in one.

  8. I wish SSMS would warn about auto-shrink.

    Recently out production app suffered downtime because someone (intentionally) enabled auto-shrink. It produced a severe spike of load after a big table had been dropped. It was shrinking like mad for an hour or so.

    • If you want to be proactive, you can probably setup a policy using PBM that will audit and block such changes. The facet is Database and field is @AutoShrink. You can probably apply the policy to all your servers if you want to go that far out. Good luck finding the culprit.

  9. informative article for SQL server error …we should have a continuous track on these errors becoz these errors may became critical in future …also we can create alert for these critical event though Lepide Event Log Manager ..it will give a real time notification by email

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php