SQL Server 2008 > how to get service startup parameter values by query?

Question

Is it possible to query for the SQL Server 2008 service startup parameter values using T-SQL? I'm specifically looking for the -g parameter that indicates how much memory that SQL Server will leave available for memory allocations within the SQL Server process, but outside the SQL Server memory pool [msdn reference].

Answer 1

I just started mine like that

P:\>net start MSSQLSERVer /g 5000

And then I ran this

EXEC sp_readerrorlog 0, 1, 'Command Line Startup Parameters','/g'

Here is the output

2010-05-07 14:11:09.510 Server Command Line Startup Parameters: /g

As you can see you can do a search for /g to see if it was started with the /g switch

More info about how to use sp_readerrorlog here: Read the error log with T-SQL

Answer 2

You can use a CLR procedure and query the SCM for the current setting. Or look it up in the registry with xp_regread. Both will get you the configured value, which may not be the runtime value.

But there is a caveat, namely that -g is such an obsolete flag that nobody should be using it and you shouldn't be looking for it. Is only relevant for x86 32bit platforms and 32 bit is dead.