Man, I still just get so jazzed when I work with powershell. Every time I need to do something long and hard(that was for you Buck), I look in powershell and it lets me just lay back and enjoy the long and hard.
So this time we’ve got like 85 DB files for our main DB. I know that’s a lot but it’s been spread across so many LUNs for so long and it’s just grown and grown as it’s filled up the LUNs. Now we finally got a couple really large LUNs and we’re trying to consolidate the files. And now that we’ve moved most of them to a single LUN, we want to stop the autogrowth on most of them so they don’t grow anymore before we empty them and then drop them. Because what’s the point really in putting them all on a single LUN if you’re not going to get rid of some of them. So we definitely don’t want them to grow anymore before we can empty them.
The task at hand was to set all of these files (most of them really) to not autogrow. The choices were do it in the GUI, or write a T-SQL cursor to go through all the files and create an alter database command in dynamic SQL for each one. Neither of those is very attractive considering that there are so many files. Of the 2 of them though, the cursor is the more attractive solution. Then I thought of powershell. I hadn’t specifically done anything like this in powershell before, but I figured I’d be able to get some love. I’m not going to teach you powershell here, or explain the script too much. I’m just gonna give you the code and if you have any questions you’re free to write, but it’s pretty straightforward. I’ll be making a video on this tonight for those of you who like to see things in action.
PS SQLSERVER:\SQL\MYSQLBox\DEFAULT\Databases\MyDB\filegroups\data\files> gci | where{$_.Name -ne “MyDB_Data”} | %{$_.set_GrowthType(“None”); Alter();}
OK, so a quick glance at the important parts.
1. Make sure you’re in the right node. Notice I’m in data\files.
2. Here I’m filtering out the one file I don’t want to be effected. So the main data file I want to still be able to grow. I’ll talk about that more in a min.
3. Don’t forget to use the Alter() at the end. Usually whenever an object has an alter method it wants you to use it. If you don’t it’ll change it in cache in your powershell session but you won’t see the change persisted to the DB. So you’re not really making the change to the object in SQL, just in your powershell pipeline. You can tell if something has an alter method by doing this at the cmdline ‘gci | gm’. If there’s an alter method there, then use it.
OK, you don’t have to filter. If you find it too much trouble or if your files are named in such a way that it would be too hard to snipe a single file for some reason, then you can change them all and then just go back to the GUI to change the last one back to what it was.
So there you go. We have a nice 1-line script to alter as many files as you need. And you can clearly tack as many file changes on there as you need. And the best part about it is that the code doesn’t really change if you need to do another file operation. All you do is change the method call. Ahhh, the power of powershell.
