Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL server startup parameters Expand / Collapse
Author
Message
Posted Wednesday, September 4, 2013 12:05 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 6:13 PM
Points: 95, Visits: 790
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...
Post #1491469
Posted Wednesday, September 4, 2013 3:30 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 1,476, Visits: 2,779
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
Post #1491552
Posted Thursday, September 5, 2013 10:43 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 6:13 PM
Points: 95, Visits: 790
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
Post #1491886
Posted Thursday, September 5, 2013 12:30 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 1,476, Visits: 2,779
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
Post #1491910
Posted Friday, September 6, 2013 8:45 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 6:13 PM
Points: 95, Visits: 790
Thanks a lot bob.
Post #1492281
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse