Post reply

Enable autogrowth for databases in SQL Server

  • November 29, 2011 at 9:37 pm

    #1414431

    Jeff Moden (11/29/2011)

    Steve Jones - SSC Editor (11/29/2011)

    I tend to agree with Bruce that Powershell is a nice multi-server tool. It's easily deployable to multiple machines, and it allows you to potentially do things that are harder in T-SQL, like looping. Runnable from a separate machine as well without SQL Server.

    Heh... agreed on all points but one... since when did it become "harder in T-SQL" to loop? 😀 I've spent most of my time on these fine forums trying to break people of the habit. 😉

    Harder is a poor choice of words. It's more cumbersome? If you think in T-SQL, it's fairly easy, but if you are primarily an admin, a step by step, rather than set by set, worker, I think seeing a FOR loop in PS is easier to understand. Looping in T-SQL is slightly foreign to the language, IMHO. Cursors work, and WHILE loops do the job, but they feel like a shoehorn'd process.

    The skills in PS also translate well to working with Exchange, Windows, and other MS tools to loop through machines, get/set properties, invoke calls, etc.

  • November 30, 2011 at 4:40 am

    #1414568

    Sorry Steve... I was just having a little fun about loops there. I just thought it a bit ironic that someone was saying it was hard to loop in T-SQL when so many rookies seem to take to it before anything else. 😀

    Shifting gears, I'd sure like to see (might be one and I've just missed it) someone write an article for SSC about PS/WMI that does auto-discovery of all the servers (SQL Server or not) and includes little things like standalone SQL Server Express as well as multiple instance names on each machine.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 30, 2011 at 7:16 am

    #1414663

    Jeff Moden (11/29/2011)

    opc.three (11/29/2011)

    Taking it a step further we can easily employ PowerShell and WMI to discover all SQL Server instances on our network without relying on the SQL Browser. If anyone is interested in that I can post a modified version of a script Denny Cherry originally posted or you can Google for the original.

    I think WMI rocks and if PS can discover all SQL Server instances and you have code for that, it would be absolutely awesome if you could post it here. Thanks, Orlando.

    Sure, here is the original post I was referring to:

    http://www.sqlservercentral.com/Forums/FindPost1100455.aspx

    My version (which I cannot find now) just dumped the server and service names into a csv file similar to what I showed above in the server properties script. Note the script assumes an Active Directory domain and that the script runner has rights to get the list of computers from AD and visit each one to enumerate their services.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • November 30, 2011 at 8:55 am

    #1414740

    Beejug1983, I just want to bring up a couple of final points regarding the script I posted and some of the things Steve, Jeff and opc.three have brought up.

    While what I sent can be easily modified to set the autogrowth properties for all database files on a server I think it’s a VERY BAD IDEA. As Steve pointed out the autogrow type and amount should be set on a per-file basis after evaluation of data needs and growth predictions. That’s why I made the actual growth setting lines comments in the data segment section and didn’t even add them in the log segment foreach loop. He also gave you very good advice about not relying on autogrow, it shouldn’t replace proper space management but only be used as a fallback, emergency measure.

    I don’t know how many servers you’re dealing with but using T-SQL as Jeff suggested to check file size, space used and database recovery type is simple and would be the way I’d take to evaluate current status as a basis for deciding autogrow settings. 150 databases really aren’t that many to check even if they’re all on different servers. Although if they were all on their own server I would probably use the Powershell script to create a report of current settings, sizes and the other info to make the autogrow settings decisions.

    In regard to your responses to opc.three, Steve gave you a link to a good post by Aaron Nelson that explains looping through a text fill of SQL Server names and opc.three gave it to you in his first post to this subject. But in your “Not getting output of power shell script” (which also shows how to loop through a text fill list of servers) you mentioned learning Powershell. One way of learning it is taking pieces of one script and pieces of another script, mashing them together and making a new script that does what you want. Asking for complete scripts may get you what you need but you’ll always be at the mercy of others time and good will. Plus it’s really not a good idea to take any script and run it against any of your servers, especially production, without understanding what it does.

    Please don’t take anything in this post as a snipe at you personally. It’s only meant as helpful advice, not criticism.

  • December 4, 2011 at 9:15 am

    #1416416

    opc.three (11/30/2011)

    Jeff Moden (11/29/2011)

    opc.three (11/29/2011)

    Taking it a step further we can easily employ PowerShell and WMI to discover all SQL Server instances on our network without relying on the SQL Browser. If anyone is interested in that I can post a modified version of a script Denny Cherry originally posted or you can Google for the original.

    I think WMI rocks and if PS can discover all SQL Server instances and you have code for that, it would be absolutely awesome if you could post it here. Thanks, Orlando.

    Sure, here is the original post I was referring to:

    http://www.sqlservercentral.com/Forums/FindPost1100455.aspx

    My version (which I cannot find now) just dumped the server and service names into a csv file similar to what I showed above in the server properties script. Note the script assumes an Active Directory domain and that the script runner has rights to get the list of computers from AD and visit each one to enumerate their services.

    Very cool. And Denny included a link to his book, as well. Thanks Orlando.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 16 through 19 (of 19 total)

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