SQL server startup parameters

• Robin35

  SSCertifiable

  Points: 6471

  More actions

  September 4, 2013 at 12:05 pm

  #282760

  Hi,

  i'm trying to find the startup parameters for sql server 2008 and 2005 using t-sql...

  for sql 2008 R2 i was able to get the information using the below script......

  SELECT * FROM sys.dm_server_registry

  Please let me know if there is any t-sql script to find the start up parameters....i would like to avoid the gui...

  Thanks in advance...

• Robert Klimes

  SSCoach

  Points: 17865

  More actions

  September 4, 2013 at 3:30 pm

  #1647512

  you can use xp_instance_regread to find the startup parameters on 2005 and 2008. the key path would have to chnage for named instances.

  DECLARE @startup VARCHAR(255)

  DECLARE @arg VARCHAR(10)

  DECLARE @i INT

  SET @i = 0

  SET @startup = ''

  WHILE(@startup IS NOT NULL)

  BEGIN

  SET @arg = 'SQLArg'+CAST(@i AS VARCHAR(2))

  SET @startup = NULL

  EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', @arg, @startup OUTPUT

  SELECT @startup

  SET @i= @i+1

  END

  Bob
  -----------------------------------------------------------------------------
  How to post to get the best help[/url]

• Robin35

  SSCertifiable

  Points: 6471

  More actions

  September 5, 2013 at 10:43 am

  #1647790

  Thanks Bob for the response. i'm able to see the result....can i see all the startup parameters using this script ?

  with this script...

  SELECT value_name,value_data FROM sys.dm_server_registry

  i'm able to see all the values....

  please let me know

  thanks in advance

• Robert Klimes

  SSCoach

  Points: 17865

  More actions

  September 5, 2013 at 12:30 pm

  #1647811

  the query you provided will show you all registry values, not just startup parameters. you would need to add where clause to return startup parameters only.

  SELECT value_name,value_data FROM sys.dm_server_registry

  WHERE registry_key = 'HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL11.EXPRESS\MSSQLServer\Parameters'

  you can run my provided code on 2008 R2 and compare it to the results from sys.dm_server_registry. it should be returning all the startup parameters.

  Bob
  -----------------------------------------------------------------------------
  How to post to get the best help[/url]

• Robin35

  SSCertifiable

  Points: 6471

  More actions

  September 6, 2013 at 8:45 am

  #1648119

  Thanks a lot bob.