The burrito sure looks good but it is most definitely a time bomb waiting to happen! So now that I have you good and hungry (who’s ready for a breakfast taco?) you are probably wondering what this has to do with SQL Server. Well as it turns out I come across these time bombs every so often. Someone has made a change in SQL Configuration manager that was not a valid change. SQL is quite happy to accept whatever you configure and it will keep humming along just like nothing has changed. And in fact nothing has actually changed in the way SQL Server is running, there is just a change in how it will run the NEXT time it starts. You see all of these configuration changes do not take effect until SQL Server is restarted. (In all fairness configuration manager does warn you, but not everyone reads what they click.)
So now it is 3:00am and your just getting into that really good sleep stage and dreaming those TSQL dreams of yours. Suddenly out of nowhere you are awoken by someone from OPS saying “Hey we rebooted your server for … and now SQL refuses to start.” After stumbling out of bed and popping open the laptop the first thing I do is head to the SQL Server Logs. The SQL Server Logs become our best friend in this situation and give us the best chance at quickly getting back to bed before you fully wake up and then get stuck watching infomercials for the next 2 hours because you can’t get back to sleep.
As you look through the log you finally come across something like the following:
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\mastlog.ldf -T1234". Operating system error 2: "2(The system cannot find the file specified.)".
If you were watching closely you see that the file name has -T1234 at the very end. Ah ha! Someone tried to add a trace flag but it was not correctly added. This brings me to my first point of the article, make sure that your trace flags are added correctly. Make sure that you only use a semicolon ; to separate the arguments and that there are no spaces between the arguments. The correct entry should look something like this (if you were wanting to capture deadlock info):
-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\mastlog.ldf;-T1204;-T1222
My second point is that you should not make changes to the configuration unless you are ready to restart SQL Server right then and there. Don’t make the change with plans to reboot later as later may not come. OK so lets say you do have a good reason to make the change and reboot later tonight. Verify the change. The change is being written to the registry. For startup parameters it is easy to see if they were entered correctly by pulling up regedit and drilling down to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\Parameters where MSSQL10.MSSQLSERVER is the version.instance name of the instance you are interested in. Here you will see one new string for each valid argument. The default registry entries will look something like the following:
If you have correctly added the trace flags then you should see the additional strings as follows:
Now there is one more gotcha. Clusters. If you tried to move the resource group to another node and SQL Server failed to start you would think you could just make the same change as above but you would be wrong. Clusters are just a bit trickier. The cluster service uses something called a checkpoint file in order to move the pertinent registry settings from node to node. With a cluster we have to perform a couple of extra steps.
1.) Take the SQL Server service offline via Cluster Admin.
2.) Open a command prompt window and type the following to disable the checkpoint for SQL Server:
cluster res "SQL Server (Instancename)" /removecheck: "Software\Microsoft\Microsoft SQL Server\MSSQL10.x\MSSQLSERVER"
3.) Insure that the parameters are correctly specified on each node. If they are not correct, fix them now.
4.) In the command prompt type the following to enable the checkpointing for SQL Server.
cluster res "SQL Server (Instancename)" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL10.x\MSSQLSERVER"
5.) Bring the SQL Server resource online.
Well now that I have knocked out this post and hopefully kept you in your happy slumber, I think it is time for a breakfast burrito! Who's with me?
