Automation: Changing SQL Service Startup Parameters using T-SQL

Question

Automation: Changing SQL Service Startup Parameters using T-SQL

Shaunt_Kolf

SSC Enthusiast

Points: 106
More actions
December 19, 2013 at 12:04 am

#287505

Comments posted to this topic are about the item Automation: Changing SQL Service Startup Parameters using T-SQL

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SteveHood79 SSC Veteran Points: 267 More actions · Answer 1

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 Grasshopper Points: 23 More actions · Answer 2

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 SSCarpal Tunnel Points: 4876 More actions · Answer 3

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 SSCommitted Points: 1863 More actions · Answer 4

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 SSCertifiable Points: 6213 More actions · Answer 5

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 and see if I could get it work work remotely.

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉