Quick Tip #1 – Management Studio Query Shortcuts

I’m planning this “Quick Tip” section just to throw out any tips that I think might come in handy.  A lot of you may already know them, but I’m sure over time I’ll find something that has escaped your notice!

Management Studio 2005 and 2008 (and Query Analyzer, but no-one’s still using that out of choice, are they?) provide three shortcut keys that run T-SQL commands, and allows you to customise up to 12 different commands.  The defaults are:

1. Alt-F1, runs sp_help.  If you have an object name highlighted, it will run sp_help on that item.  Very useful for seeing the columns, constraints and indexes on a table.
2. Control-1, runs sp_who.  If you highlight a login name, the output will be for that single login.
3. Control-2, runs sp_lock.  If you highlight a number, sp_lock will be run for that session ID.  If you highlight two numbers separated by a comma (e.g. “51, 52″, without quotes), you’ll pass both parameters into sp_lock.

Additionally, you can customise these commands in Management Studio from the Tools | Options | Environment | Keyboard dialog.  For example, you could set Control-F1 to be “sp_helptext”, or modify sp_who to be sp_who2.  Note that any changes will only apply when you open a new query window.

A good example of a custom Query Shortcut would be the following query:

SELECT TOP 10 * FROM AdventureWorks.Sales.SalesOrderHeader
ORDER BY SalesOrderID DESC

If you assign it to a shortcut key, say Control-3, every time you press Control-3 you’ll see the 10 most recent SalesOrderHeaders. 

Personally, I don’t customise the shortcuts, as I deal with many different systems, and am constantly using different installations of Management Studio, so it’s not worth configuring the environment.  However, if you use a single installation of Management Studio, custom query shortcuts could save you lots of time.

Cluster Won’t Start Due to Incorrect Startup Parameters

I experienced an issue recently where a cluster could not start SQL Server as I had modified the startup parameters and mistakenly entered one incorrectly.  I didn’t notice until after I’d stopped SQL Server, and was unable to restart it.  I changed the value again in SQL Server Configuration Manager, but discovered that every time I attempted to bring SQL Server back up using Cluster Administrator, the incorrect value was back in place.

The reason for this is that the cluster controls the startup parameters, and will replicate the value through all nodes of the cluster.  The only way to change this parameter is to change it on the active SQL Server node.  However, if you don’t have an active node, because you’ve just broken it and it won’t start…

Now that you understand the Catch-22, what can you do to fix it?  There’s really only two solutions – tell the cluster to replicate a different value to all the nodes (impossible without PSS’s help, as far as I know), or get SQL Server running without the cluster.

There’s two ways of booting SQL Server in this instance.  The first is to start SQL Server (sqlsrvr.exe) from the command line, specifying the paths to the master database (remember, we can’t read from the registry due to the cluster updating it).  The second option is to modify the registry, and then start SQL Server via the Services control panel.  Note that modifying the registry is highly NOT recommended, but may be your only option. 

Once SQL Server has started locally, use Cluster Administrator to start SQL Server on the cluster.  As SQL Server is already running, the Cluster Administrator hooks right into it.  From there, you can use Configuration Manager to fix the startup parameter, have it replicate through the cluster, and restart SQL Server.

Phew.  The moral of the story is to double check your parameters before shutting down SQL Server!

I’m happy to hear any other possible solutions to this problem that I might have missed.