HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Listing SQL Server instance startup parameters

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
sqllistinginstanceserverparametersstartup

Problem

I have a server I have just restarted and verified which trace flag are active using DBCC TRACESTATUS:


Trace Flag : 3688 Function: Removes messages to errorlog about traces
started and stopped

Here you can see what each trace flag does.

Flag 3688

The start parameters are as follows:

Question:

How can I find what the startup parameters of the SQL Server services are, through T-SQL?

Solution

In SQL Server 2008 R2 SP1 or later, this is made considerably easier via the sys.dm_server_registry DMV:

SELECT
    DSR.registry_key,
    DSR.value_name,
    DSR.value_data
FROM sys.dm_server_registry AS DSR
WHERE 
    DSR.registry_key LIKE N'%MSSQLServer\Parameters';


From: An easier way to get SQL Server startup parameters

Code Snippets

SELECT
    DSR.registry_key,
    DSR.value_name,
    DSR.value_data
FROM sys.dm_server_registry AS DSR
WHERE 
    DSR.registry_key LIKE N'%MSSQLServer\Parameters';

Context

StackExchange Database Administrators Q#114376, answer score: 16

Revisions (0)

No revisions yet.