When you install SQL Server, Setup will write a set of default startup options to the Windows registry. You can use the startup options to specify an alternate master database file, master database log file, or error log file.
Here is what the default options are according to Books on line
Default startup options | Description |
---|---|
-d master_file_path |
The fully qualified path for the master database file (typically, C:Program FilesMicrosoft SQL ServerMSSQL.nMSSQLDatamaster.mdf). If you do not provide this option, the existing registry parameters are used. |
-e error_log_path |
The fully qualified path for the error log file (typically, C:Program FilesMicrosoft SQL ServerMSSQL.nMSSQLLOGERRORLOG). If you do not provide this option, the existing registry parameters are used. |
-l master_log_path |
The fully qualified path for the master database log file (typically C:Program FilesMicrosoft SQL ServerMSSQL.nMSSQLDatamastlog.ldf). If you do not specify this option, the existing registry parameters are used. |
If you want to get the startup parameters for SQL Server, you can either read the registry or open up SQL Server Configuration Manager and look at the Startup Parameters tab, here is what it looks like on my laptop
Did you know that you can do this in an easier way on SQL Server 2012? No, I am not talking about using the undocumented xp_regread
stored procedure. A new dynamic management view was introduced, this view is sys.dm_server_registry.
Now if I want to see the parameters, all I need is
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE registry_key LIKE N'%MSSQLServerParameters';
Output will be something like the following, I made value_data shorter and left out registry_key from the output so that it would fit on one line
value_name value_data SQLArg0 -dC:MSSQLDATAmaster.mdf SQLArg1 -eC:MSSQLLogERRORLOG SQLArg2 -lC:MSSQLDATAmastlog.ldf
There is more that you can do, let’s see I want to know some stuff about SQL agent
SELECT value_name, value_data
FROM sys.dm_server_registry
WHERE registry_key LIKE N'%SQLServerAgent%';
output
value_name value_data ObjectName NT ServiceSQLSERVERAGENT ImagePath "C:MSSQLBinnSQLAGENT.EXE" -i MSSQLSERVER Start 2 DependOnService MSSQLSERVER ErrorLoggingLevel 3 JobHistoryMaxRows 1000 JobHistoryMaxRowsPerJob 100 WorkingDirectory C:MSSQLJOBS
To see all that this dmv returns, execute the following
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
It returns 99 rows on my laptop, most of the stuff is TCP/IP related.