Instead of working, I blog.

Just double posting for a while till everyone gets the hang of the new location. Don't forget that officially my blog has moved here and I won't be posting very much longer at this location.

I know Jen's already blogged on this, but this is my little contribution to the whole, how cool are we, theme.

Brad Mcgehee just came to speak at our user group and to my surprise he thought enough of what we're doing to write a blog about it.

So I thought I'd take this time just to explain a little more so maybe some more of you can start doing it at your user groups too.

I started doing this last year when it really started bothering me that the user group model is so limited. The problem is that you bring in a speaker to give a session on a topic, and next month you do the same thing with a different speaker and a different topic. The problem is that you don't have time to go deeper into any of the topics. You only get to barely touch on the subject and there's no time to really learn it. So I decided to start up a progressive class before the user group. I lasts like 6-8 mos and each session builds off of the previous ones. The downside however is that it takes you 6mos to complete the class, but it's better than nothing and it's a free class. I actually treat it like a real class that you would go to at a training center only I like to think I go into more depth sometimes.

It's a really good model because if you hold it before or after your main user group meeting the people are coming anyway so you've got a built-in audience. And you'd be surprised how many people show up to the group that never did before. So anyway, that's all I've got on that. I hope some more of you start doing this and if you do I'd like to hear how it works for you. And I'd like to also give a special shout-out to Brad for recognizing my genius.



Petri for us all

The Petri IT KnowledgeBase just opened up its SQL section and Jen and I are the authors. I know, Jen also wrote about this too... what can I say, I have nothing original so I have to steal others' material. Anyway, it's really a nice site they've put together for us so come check it out. Currently we've got some nice barebones "I know nothing about SQL" articles so if you're a complete newbie then you can start here and we'll explain what all this stuff actually means.

Hey everybody... after like 5yrs of blogging here, I've made the decision to move this blog over to MidnightDBA.com.

Jen and I are consolidating our blogs in the same location so she's moving too. I'm pretty much up and ready to go in the new location so update your RSS feeds to point to the new location because this'll probably be my last post at this address. Maybe I'll double-post for a while, I'm not sure.

Here are the links to our new blogs, and they're actually on a linux server so the URLs are case-sensitive.

http://www.midnightdba.com/DBARant/
http://www.midnightdba.com/Jen/

Hey everybody... it's been a while since I've blogged, but that's what starting a new gig will do to you. I've had so many new things to rant about here and I'm sure I'll be getting to those in the future, but right now I want to share a funny story with you.

We had a group of devs who were trying to run a fairly simple insert and it kept filling up the disk. And when I say it was filling up the disk, i mean like 50K rows were filling up like 200GB. So they came to me to see if I could fix it. This is where it gets fun.

It really didn't take me too long in my investigation to find that they had the data file set to autogrow by 131,000%. That's right, I said it! 131,000 percent. So now that I found the problem I was able to set it to something more reasonable, shrink the file and let them get on with their insert.

So it started me thinking about what other DBs in the place had similar issues because I've been hearing about disk space problems here and there. So I wrote a powershell to go out and check the file growth rates for every SQL box and I found a lot of the same type of stuff. There were a lot of them set for several thousand percent growth, several of them set for 1MB growth, and everything in between. In general, as a good generic setting to start from, I like to grow my files 1GB at a time. It's a good round number that works in a lot of cases and then you can snipe the ones that need something else. And then I altered the powershell to go out and change the growth rates of all the DB files out there to 1GB. Life is good again and we have the beginnings of a happy shiny environment.

Oh y, and in the same discovery I also found 110 DBs set to autoshrink and I also took care of those.

The other day I blogged on a cool way to alter database file properties.  And I promised I would film the solution for you.  Well, I got home that night and started filming but when I pulled up sqlps I was stopped dead in my tracks by something I couldn’t explain.  The methods to get and set were missing.  I checked my other box and they were there, so what happened?  The only difference was that the box I wrote the blog from was XP and the box I was filming from was Win7.

So I pinged a couple really smart guys as MS (and I cc’d Buck).  So after a couple of days we have an answer.  There’s a new feature in powershell 2.0 that hides the getter and setter methods from you by default in get-member.  They’re officially calling it an enhancement.

There are 2 new parameters that get-member supports that allow you to control what you see.  You can now use –view and –force.

-view takes the parameters Extended, Adapted, Base, All

-force doesn’t take any additional parameters.

However if you’re looking specifically for getter and setter methods, you’ll have to use –force because –view doesn’t display them.  Here’s what that’ll look like:

dir | gm –force

Now you can see everything.  It’s interesting though is that by defalt, PS loads all the methods, it just doesn’t show them to you.

Here’s the MSDN blog that explains this and so much more.

And I’d like to give a special thanks to Robert Hutchinson at MS for chasing this down for me while Buck and I sat on the sidelines helpless.

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.