Tag Archives: Tutorials

Uncategorized

Powershell to Alter Database File Properties

2 Comments

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.

Uncategorized

What makes Powershell Awesome?

5 Comments

I was asked to write a quick blog on what makes PS so awesome. And while it’s a huge topic with many many examples, I’ll give you a couple just to get you going on how cool it is.

1. It makes it really easy to add things to your process that you never could before. Let’s take for example that you want to make a change to your DB but you want to backup the schema for an object or a set of objects first. This is so easy in PS it’s not even funny. The code to script out objects is:

gci| %{$_.Script()}

That’s it!!! And all you have to do is add a filter if you want to script only a certain schema, or a certain object. And the code doesn’t change if you want to script tables, views, function, SPs, etc. All you have to do is change the node of the tree you’re in… and for that you manage it like DOS. So to script out a table you would type this code:

cd sql:\sqlserver\servername\default\databases\MyDB\Tables
Then just run the script code above.

AND to script out the SPs, you just change the contest to StoredProcedures like this.

cd ..
cd StoredProcedures

That will put you in:
cd sql:\sqlserver\servername\default\databases\MyDB\StoredProcedures

Then just run the same script method from above.
So you can see that scripting objects is wicked easy. And now you can backup the schema of a table before you mess with it, or now you can automate the scripting of SPs before you deploy a new version so you can easily rollback.

2. Now since scripting things like we did above will just print the script out to the screen, you’ll want to do something more permenant with them. Have you ever tried to write to a txt file from tsql? You know, you either have to mess with xp_cmdshell or go to one of the sp_OA procedures and make those external calls, right? Well PS solves that problem. So let’s script out the objects from above and save them to a file.

gci| %{$_.Script()} | out-file c:\MyTables.txt

That’s it!!! So now you can save those things to a file and you’ve got them to use again.

That’s not all you can do with files. You can also get the stuff back out of the files pretty easily. Let’s say that you have a list of servers you want to connect to and check that the services are running and you want to turn on the ones that aren’t. I’m not going to show you the code for all of the operations here, but to use a txt file as a list of servers you have but to call the txt file and automatically cursor through the items. So here I’ll just get the list of servers from a file and print them to the screen. Because what you do with them isn’t nearly important here as being able to get them easily.

get-content c:\ServerList.txt | %{$_}

So really, I just listed the servers to the screen one at a time.
The % character is an alias for ‘for-each’. So for each item in that txt, print it to the screen. Easy huh?

And just for completion, you can easily work with services by calling the get-service cmdlet or the wmi class for remote boxes.

3. Getting info for objects has never been easier. Let’s say you want to get all of the sizes for all of the tables in the DB and do something based on the size. While we’re not going to do the entire script, I’ll show you how to get the size for all the tables.

First, switch to the tables node.

cd sql:\sqlserver\servername\default\databases\MyDB\Tables

Then run this code:

gci | %{$_.DataSpaceUsed}

That’ll just print them out to the screen, or you could pipe them to a txt file like above, or you could continue working with them in the pipeline and manipulate certain tables based off of whatever you like.

There are so many more things you can do with it to make your DBA job so much easier that it would make your head spin. I don’t know how we ever got along without it.

Ok, so before I go I’m gonna do just one more because I do this one a lot.
I’m not going to give the code right now, but I’ll tell you about it.

Let’s say you’ve got a new employee and you need to add his acct to a Windows group on 30 boxes, and you need to add him to SQL groups and give him access as well. So He’s got to be able to TS into these 30 boxes and have certain rights in all DBs on these servers.

With PS I can create a list of servers and either keep them in a table, or put them in a txt file and iterate through them and add the user both to Windows groups, and to SQL groups in a single script. So when’s the last time you were able to do that without logging into all the servers individually yourself? And it’s really not that hard.

So PS is well worth your time getting to know. If you need help getting started with resources, then ping me and I’ll be glad to hook you up.

I’ve got some really good stuff on PS on MidnightDBA so go out there and check them out.

Here’s a overview vid I did back in 2008:
http://midnightdba.itbookworm.com/VidPages/PowerShellOverview/PowerShellOverview.aspx

And you can see most of my other PS vids on the powershell tab of this page:
http://midnightdba.itbookworm.com/Admin.aspx

Good Luck.

Uncategorized

Ken’s Legacy

1 Comment

Some of you may remember an obscure little man who used to take a shot at writing now and then.  His name was Ken Henderson.  And he wrote (among others) small series of books called the Guru’s Guide to SQL Server.  He also wrote a little tome on SQL internals.  Now, Ken isn’t with us anymore, but his legacy lives on.  I remember sitting in Ken’s office at MS a few times talking about his vision for what he wanted his books to be.  His goal was to teach at several levels.  What he wanted was for the beginners and the experienced alike to get something out of his books.  And he succeeded very well.  I can still go back and read some of the Guru’s books and understand things I didn’t get before.  I remember saying that to Ken once and he just smiled.  Then he said, finally somebody gets it.  I want people to be able to grow into my material.

So what brought all this on?  Well, as I do sometimes, I was looking through one of my favorite blogs to kinda keep up on things.  I typically go through the back posts to make sure there’s nothing I missed the first time, or see if there’s anything I’ve grown into since reading it the last time.  And by looking at the writing style, and the intent behind it, I see that this author has the same principles that Ken held.  He not only tells you the most intimate details of the inner workings of SQL, but he does it in a way that allows you to learn at different levels.  I don’t even think he does it on purpose because like Ken, he’s just being himself;  a true teacher.

And I’m not sucking up. The only reason I’m writing about this is because it really hit me tonight because I just finished going over an old chapter of Ken’s before I went through the blog.

So since I read his material the same way I read Ken’s, I’m naming Paul Tripp the new Ken Henderson.  Paul, you make me proud to be your friend.

Uncategorized

Vids posted

OK, remember back when I said I was going to be making some vids? Well, I’ve got them done now. I’m doing more all the time. I average about 3/wk. Sometimes more, sometimes less… that’s why it’s an average. Anyway, they’re posted now so feel free to go take a look at them.

My server’s slow, and my connection is ok, but it prob won’t take much to max it out. But if you keep trying, you’ll get them. I don’t know how popular this blog is so I don’t know how many will see this a rush out to download the vids. But I’m working on another hosting solution and I’ll let you know when I have it ready. It could be some time though, huh.

So let me take a couple mins to explain about these vids. This is me sitting up at night doing camtasia after everyone’s in bed. They’re not meant to be these superly produced movie type productions. They’re just me at the computer talking about whatever topic happens to cross my mind that day. I try to do them in small chunks on specific topics so you can get just the info you’re looking for. There’s no need to bog you down with crap about backups when I’m talking about SSIS. So I do my best to stay on topic. And I like to throw some practical advice in there whenever possible. And if I took the time to do any real production on these guys, it would take me weeks to get a video out the door. As it stands, I can make the vid, produce it, and post it in about an hour. This way I can get a lot of vids up instead of spending all my time producing. And it’s only screencast, so you don’t have to look at my face.

Anyway, here’s the link. I look forward to criticism. I’m fairly new at this so I’m still kinda getting my rap down. And I take requests, so if any of you have anything you’d like to see, just shoot me an email and I’ll do my best to get it done for you.

Also, there’s a blog I use to notify you of new vids… I’ve got a lot of vids up, and only a couple blog entries, so I haven’t been really good at sticking with it, but I’ll get better.

MidnightDBA
Just click on the SQL Server link there. It doesn’t look like a link, but it is.