One of the most reassuring aspects of watching a vintage James Bond film is the comfort of knowing that, just when there seems no further hope that the villain's plans for world domination will be thwarted, Bond will glance up at the wall and notice a big red button. Instantly, he knows that all he has to do is press it and the villain's lair will self-destruct messily, with plenty of pyrotechnics, and armed men being tossed into the air like rag dolls.

Of course, you have to wonder why the technologists who built the lair put that big red button on the wall. It seems to be an irresistible urge, and one to which the creators of SQL Server are not immune, as @SQLPoolBoy noted last week in one of his tweets...

        "Looking at a database that has 99% fragmentation across the board. The cause, AutoShrink"

...a database brought to its knees by some poor soul who had accidentally hit one of SQL Server's big red buttons.

You have to feel sorry for anyone who accidentally accepts the default database sizing and auto-growth settings, or turns on AutoShrink, or accidentally creates a collation conflict, or falls foul of any other of a host of 'Red Button' actions that can eventually lead to metaphorical pyrotechnics and DBAs being tossed in the air like dolls.

SQL Server makes it very easy to tweak its various database- and server-level settings and so it's easy for inconsistency to creep in between database and servers, and it's easy for someone to unwittingly hit one of the red buttons. However, for the DBA managing tens of servers, it's not necessarily easy to find out which buttons have been pressed where, or to find best practice advice on how some of these settings really should be configured for each environment.

However, help in various forms is slowly emerging. Brent Ozar has made publicly available his SQL Server Blitz script, which helps you verify some of the absolute fundamentals (Are backups being taken? Are DBCC checks being run?), and then seeks out a few of the more common red buttons, which may need deactivating.

Then there is also SQLCop, a free community tool for "detecting common problems with database configurations and TSQL code". Named after a similar .NET tool (FxCop), it's broader in scope than the Blitz script, checking everything from configuration settings, to fragmented indexes, to missing Foreign Keys, to "code smells" in stored procedures. Red Gate has done some work with the makers of this tool, in incorporating some of their tests into the SQLTest tool, the idea being that the final step to resolving such problems may be automated testing.

However, there is still much work to be done. What are your favorite "Red Button" actions in SQL Server? What is the best way to find and deactivate them before they cause havoc in your Server and databases?

Cheers,

Tony.