Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automation: Changing SQL Service Startup Parameters using T-SQL


Automation: Changing SQL Service Startup Parameters using T-SQL

Author
Message
Shaunt_Kolf
Shaunt_Kolf
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 165
Comments posted to this topic are about the item Automation: Changing SQL Service Startup Parameters using T-SQL
SteveHood79
SteveHood79
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 309
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.
Erik Harman
Erik Harman
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 145
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.
Tobar
Tobar
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 Visits: 758
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.
cdesmarais 49673
cdesmarais 49673
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1163 Visits: 1423
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.
rstone
rstone
SSC-Addicted
SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)

Group: General Forum Members
Points: 405 Visits: 824
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
Helpdesk: "Perhaps I'm not the only one that does not know what you are doing." ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search