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

SQL Server Configuration Manager: Startup Parameters

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

T-SQL
1
2
3
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE registry_key LIKE N'%MSSQLServerParameters';
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

T-SQL
1
2
3
SELECT  value_name, value_data
FROM sys.dm_server_registry
WHERE registry_key LIKE N'%SQLServerAgent%';
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

T-SQL
1
2
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
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.