And why would you use them….
First of all, what are the defaults? There are a couple of places to look. Place #1 is SQL Server Configuration Manager (SSCM). Open that up, and highlight SQL Server Services, right-click on the SQL Server item and select Properties. Click on the service tab, and you will see something similar to this:
The highlighted portion is the actual exe that is run for the service. The full text of it is:
“C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr.exe” –sMSSQLSERVER
The other location to find this info is in the registry, at HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\MSSQLSERVER
Here we see the first startup option to discuss, -s. According to BOL, this is used for starting named instances. In this case, I have the default instance running, hence the MSSQLSERVER parameter.
Startup options fall into three categories: master database info, memory management, and troubleshooting options.
Master Database Info:
Using the –d and –l options, you can specify the location of the master database data and log file locations.
Memory Management:
-g Specifies an integer number of megabytes (MB) of memory that SQL Server will leave available for memory allocations within the SQL Server process, but outside the SQL Server memory pool. SQL IS PRETTY SMART ABOUT SELF TUNING, SO I WOULD BE LEERY of using this. BOL states Use the default for the -g parameter unless you see any of the following warnings in the SQL Server error log: “Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE <size>” or “Failed Virtual Allocate Bytes: FAIL_VIRTUAL_COMMIT <size>”. Either one of those would scare me enough that I would probably be calling MS Professional Support Services.
-h Required for hot adding RAM to a 32 bit instance. How many 32 bit instances have the hardware capability to hot add RAM?
Troubleshooting Options
-m The biggie. Starting SQL with this option is SINGLE USER MODE!!! This means that your apps will not be able to connect if you are. This is almost a last resort option. You can add a parameter for an application, such as –m “sqlcmd” so that you can run the command line SQL app. Of course this means that you would not be able to run SSMS – that would be another connection. You also would not be able to troubleshoot SQL Agent tasks – SQL Agent also requires its own connections
-f Start with a minimal configuration. This is the bail out option – you have configured something so poorly that you can’t fix it – such as a resource governor set up that does not let anyone connect. You can start up SQL with the –f option, disable resource governor, and then restart. And then go test out your resource governor set up on a non-production system.
And the one I have seen most often: -T. This is used with a parameter to indicate a trace flag. Some of the more common ones I have used are 1204 and 1222 for deadlock tracing.
Refereces:
BOL:
http://msdn.microsoft.com/en-us/library/ms190737(v=sql.105).aspx
http://msdn.microsoft.com/en-us/library/ms345416(v=sql.105).aspx