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

Automation: Changing SQL Service Startup Parameters using T-SQL Expand / Collapse
Author
Message
Posted Thursday, December 19, 2013 12:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 4:54 AM
Points: 2, Visits: 165
Comments posted to this topic are about the item Automation: Changing SQL Service Startup Parameters using T-SQL
Post #1524457
Posted Thursday, December 19, 2013 1:59 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, September 6, 2014 4:27 AM
Points: 83, Visits: 300
While I feel it's probably good that you can do this through t-SQL if required, I think the idea of adding -t1222 is more than a little out-of-date. Starting in 2005 I would be using a trace to get the deadlock graph, which you can run queries against instead of reading through your logs, not to mention leaving your logs in a state that they're still legible. Then in 2008 you could have started using extended events to do the same thing as well.

It's still a bit scary changing the registry through t-SQL... Be careful and blow up personal test machines, then Dev, then prod if you're doing this. A small misstep here leaves you manually changing parameters to get SQL to even run again, and you don't know that you made the mistake until you restart services.
Post #1524484
Posted Thursday, December 19, 2013 6:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 20, 2014 7:54 AM
Points: 1, Visits: 130
Other ways to approach this from 2005+; You could use the article from the SQL team at http://blogs.msdn.com/b/sqltips/archive/2005/08/19/sqlregsettings.aspx to use CLR to both read and write to the registry checking the entries before writing. Or, you can use the undocumented stored procedures like master..xp_regread and master..xp_regwrite to change the registry.

Overall, if there are company wide flags that you need to implement, this is a quick way to do them, good article.
Post #1524545
Posted Thursday, December 19, 2013 7:20 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
Shaunt,

Thanks for the article.

"The credit belongs to the man who is actually in the arena; whose face is marred by sweat and blood; who strives valiantly; ..." -- Theodore Roosevelt

I will probably never have opportunity to use the "parameter" knowledge you put forth, but the first line of your first snippet of script brought joy to my heart. I started in the "C" and Oracle space where, at least from the early 90s, you have been able to declare and assign a variable in one step. Imagine my disappointment when I came to SQL Server, at version 2005, and was unable to do so.


<><
Livin' down on the cube farm. Left, left, then a right.
Post #1524573
Posted Thursday, December 19, 2013 10:47 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 18, 2014 3:49 PM
Points: 845, Visits: 1,100
I recently had to deal with this for a puppet project. I dealt with it using the command line+powershell rather than SQL, but this is a cool approach.
Post #1524715
Posted Thursday, December 19, 2013 11:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:57 PM
Points: 373, Visits: 613
I also did it with powershell. I created a function with a parameter that is a list of servers. It would also prompt for a restart the service if needed. It also allowed changing the errorlog location which added the need to copy folder ACL. Now, I think I would start with something simple like [url=http://www.erichumphrey.com/2011/03/change-sql-startup-parameters-with-powershell/ ][/url] and see if I could get it work work remotely.

Randy
"Perhaps I'm not the only one that does not know what you are doing."
Post #1524731
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse