Automation: Changing SQL Service Startup Parameters using T-SQL

  • Shaunt_Kolf

    SSC Enthusiast

    Points: 106

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

  • SteveHood79

    SSC Veteran

    Points: 267

    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

    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: 4767

    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

    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: 6011

    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.

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

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

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