What do you do if you have a set of data and log files you can’t attach because the log is damaged? Here I show you a way to attach it, fix it, and get going again. Paul Randall also discussed this in his blog here: Hack-attack a damaged DB
Join in our live DBAs at Midnight webshow
Fridays at 11pmCST on our Ustream channel!Watch all past shows at MidnightDBA.com
Quite often it can be useful to not only get a list of DB sizes, but also sum it up in the last line. This can be useful for estimating backup file sizes, server sizing and more. Here I’ll show you a couple ways to sum up DB sizes so take your pick.
Title: Sum DB Sizes with Powershell (right-click to download video)
Author: Sean McCown
Date: 1/4/2011
Length: 08:54
Size: 6,901 KB
This is gonna be a quick post, but I wanted to throw out a couple easy ways to avoid using xp_cmdshell.
Unfortunately this xp has gotten a bad rep, but only because it’s been abused. If you use it properly there’s no reason why you can’t continue to get some good use out of it for many years to come. Actually, I use it for my backup routines for a couple reasons… first, when I wrote them there really wasn’t a better choice. And second, while I can architect a solution around it quite easily, it’s ridiculous to go so far out of my way to code around something that isn’t a huge problem. The solution would be more complicated than the original problem, so why bother? Oh yeah, and since I’m in a mixed shop there’s also that problem with having something like powershell on every box and I don’t wanna have to have a different routine for my SQL2K boxes, than I do for all the rest. So using xp_cmdshell is still a good fit for me.
Anyay though, there are some good reasons to avoid it when it’s not necessary (part of using it responsibly). So we’re going to talk about 2 XPs today that will help you do just that.
First, there’s xp_FixedDrives. This will report all the drives on your server and the amount of free space in MB. Now, this can’t replace a well-formed PS script that can give you a lot more info, but it’s a nice start when you just need to look at drive space real quick.
Second, we have xp_create_subdir. This handy little xp will let you create a directory. You just pass it the path you want to create and you’re golden. So it would look something like this:
xp_create_subdir ‘C:\Mydir\Anotherdir\theFinalDir’
And just like in DOS it’ll build the whole path for you so none of those dirs have to exist ahead of time.
Anyway, hope this finds someone well.
A user request came in to write something in powershell that would replace sp_MSForEachDB. I took it a step further and showed you how to make it even more flexible for an enterprise environment.
Here’s a cute little piece of code to add up the sizes of all your DBs on a server. It comes in handy when you’re trying to see how much space you’ll need to duplicate the server either for DR, or reporting, or even just to see how much backup space you’ll need.
Make sure you’re at the DB context of PS:
>SQLSERVER:\sql\ServerName\default\databases
Then type this code:
>dir | %{$Total += $_.size}
>$Total
Ok, that’s it. Not too bad, huh? Now, let’s work on making it look like something you can actually work with.
Ordinarily you would prettify this by just dividing it by the measure you want to convert it to. So if you wanted to convert it to GB, then that would look like this:
>$Total/1GB
However, you can’t do that here. Here’s what you need to know.
This output is in MB, and the “/1GB” trick assumes that the output is in bytes. So lets assume that your $Total is 4189811.4375. Remember now, that’s in MB. So here’s what the 1GB trick will yield.
>$Total/1GB
0.00390206597512588
That’s not even close to right. And again, that’s because the 1GB trick assumes your output will be in bytes. So here the best way to convert this to GB is to do the math yourself like this:
>$Total/1024
4091.61273193359
Of course, you can still use one of the tricks to get the number you’re looking for, but since you’re already in MB you have to go 1 measure down to make it come out right.
>$Total/1KB
4091.61273193359
So why is that? Well, because 1KB is only a shortcut for 1024, and from MB, the conversion to GB is /1024. And the problem of course with doing it this way is that it’s deceiving as hell. Anyone else looking at this will think you’ve converted to KB because they may not realize that the original output is in MB. So don’t use this one, I only threw it in there to show you how to come up with the right number. It also shows you something about how the trick works.
Now, you can still use the 1GB trick, you just have to massage the data first. So here we’re going to put it back into bytes and then convert it to GB. This is really just to prove to you that the output for this trick really is assumed to be in bytes.
>($Total*1024*1024)/1GB
4091.61273193359
Personally I’d never do it this way. Sure, it shows you and someone else what’s going on so you can read the code, but that’s also what comments are for.
>$Total/1024 # Convert to GB. Can’t use /1GB because this output is in MB and the /1GB assumes the output to be in bytes.
See, what’s wrong with comments?
Oh yeah, sorry guys, I forgot this one thing. I thought about going back and adding it to the original post, but frankly this is just easier.
This is a continuation of my original post, and part of the Non-SQL Friday.
It’s been suggested to me by more than 1 person that I post some of my ranting content anonymously on a different site. This would serve to preserve my *good* name, and at the same time still get the content out there for all to see. The problem I have with that is that the internet is full of people saying obnoxious things anonymously. That’s easy. Getting online and posting to a forum under a pseudonym that you think a company sucks for some reason, or that you hate so-n-so is the most common form of expression out there. And it doesn’t stop there does it? No, it doesn’t. People take advantage of being anonymous to be really over the top horrendous and say they’re glad certain people are dead, or praise violence against entire groups of people because they somehow justify it. So being anonymous certainly allows us to see what others are really thinking, but in a very large way it also discredits them. If you’re so proud of your opinions then why do you have to hide? Stand up and say it like a man! So this is something I considered when I first started writing. Should I make a lot of the things I say anonymous or should I stand behind everything I say?
Well, I clearly chose to stand behind my words and put myself out there. And I think it adds validity to what I say because I am regarded by some in this industry and my words do carry weight. And I know for a fact that I’ve effected real change. When I slam a vendor for something, I make myself available to them not only for comment, but I’ve also helped many of them work out their problems. Of course there are vendors who have refused help and want to just call me names and try to discredit me, but nothing I say about a vendor is untrue. And that’s the key to doing what I do. I have to be right when I call a vendor to the mat.
And there’s something you, as the public, don’t get to see when I post something really bad about a vendor. Most of the time (very few exceptions) I’m posting it only as a last ditch effort to shame them into finally doing the right thing after talking with them many times and getting nowhere. One of the things I really can’t stand is when a vendor comes into my shop and lies about what their product can do and then I have to live with the consequences. It’s not fair, and it’s not right. So what recourse does a person have when they were lied to and the product clearly can’t handle enterprise-level traffic? You can complain to the vendor, but they’ll prove to you very quickly that they really don’t care. They’ve got you money. So what else? Nothing. So what I do is I use my position in the media to effect change. And I’m telling you that I have done so. The number of times I yelled at Quest publically has really made a difference. Shortly after a big post about Spotlight, the dev team scheduled a series of meetings with me to discuss what was wrong with it and how to fix it. We had a number of meetings on the topics I wrote about. And I know that the new product line started shortly after. The same goes for Foglight. And LiteSpeed was another one I helped. Back when they were Imceda I helped them a lot, and even for a short time once Quest took them over. So what the public doesn’t see is the work that goes on both before and after a big post that seems really harsh, when in reality it actually isn’t.
The point is though that I put my name on stuff I write and it adds credibility. I’ve actually got skin in the game. If I did everything anonymously the vendor would have no way to contact me to get more details so they could do something about it. So I’m not just out there cursing vendors. I’m also helping them where they’ll let me. And it hasn’t backfired as much as all of you think. While MS doesn’t like everything I say about them, they know I’m right about it and they’ve on a number of occasions called me and put me in touch with the team in charge of that feature so we could talk it out. Hell, that’s how I originally met Buck and Dan. They were assigned to mediate my concerns about SSMS. And I always feel that as long as I’m fair, I can be harsh. And sometimes I’m harsh just to get their attention. You may not realize this, but I’ve heard that more than a few people on the product teams at MS and the upper brass read my blog. And I know for a fact that meetings have been kicked off internally to discuss some of my posts.
So while some in the industry like to call me one thing or another or say that I don’t represent their organization well or that I don’t set the right tone for the image you’re trying to portray, just remember this… I’m a DBA. And I’m representing other DBAs out there who are sick to death of taking it from behind from the vendors and who don’t have an international outlet to do this themselves. My goal is to protect DBAs from making really bad decisions and sometimes nice just doesn’t cut it. Companies don’t listen to nice. But when a company sees that they’ve really pissed off a blogger with some skin in the game, then they sit up and listen.
So how many of the rest of you actually put your skin in the game?
This blog is part of Jen’s Un-SQL Friday collection. You can read her blog on it here.
Branding is an interesting topic to me because not many people have managed to build a more interesting brand or reputation than I have. I’ve been called on a couple occasions ‘the Howard Stern of SQL’, and while I’ve never had a naked girl in my studio, I think I have been somewhat of a shock jock. When I hit the DB world through my very outspoken InfoWorld blog I upset a lot of vendors. Not many people (and nobody in the SQL Server world) had ever talked to them the way I did, nor did they ever take the candid approach to reviewing their products that I did. Throughout the years I’ve continued to be really open about my opinions and my feelings towards vendors, products, company policies, industry standards, and the like. Now I’ve got the MidnightDBA brand and despite it being an excellent moniker, it begs the question whether being so outspoken is the best way to market a brand with such potential.
The argument of course is how much does being that way limit your audience? And can you really differentiate yourself from others without doing something to be different? There are two real reasons I’ve constructed my brand the way I have. The first is that I’ve just really never learned social niceties so I have no self-control. The second is that I’ve always found the bulk of the industry’s writings to be too vanilla and sanitized, lacking any real interesting perspective. So what has my reputation gotten me? Well I really think I’ve done alright for myself. I’ve continued to climb my professional ladder and I’ve made a really solid brand that people are really starting to like. Have I been held back at all by my approach though? Oh, that’s really kinda hard to say. The only measurable penalty I’ve suffered is that I’m not allowed to speak at PASS. Other than that though, I’ve ruffled a few feathers with others in the industry but that’s about it.
It was suggested to me a while back that being the way I am makes my audience much smaller. I’m splitting my potential because there’s a good portion of the audience who won’t follow me because they don’t appreciate my style. Yeah, I suppose that might be true, but I’ve given this a lot of thought, and I like doing things the way I do them. I like calling vendors to the mat. Frankly, I’m sick to death of reading reviews of crappy software where they just talk about how nice the colors are and how well it installs. Many of these are products I’ve used and they’re complete crap. I’m the lead DBA in a very large enterprise and I have real enterprise concerns, so when a vendor tells me their product is an enterprise-level tool I expect it to be. And I don’t care to mince words when reporting on it because I always figured other DBAs want to know what the real scoop is. Anybody can write about the positive aspects of a product, but nobody has the guts in this industry to tell you what you really need to know. So yeah, while I may split my audience by being so outspoken, the audience I do have is loyal and full of really cool people that I’m proud to call my friends. People like Denny Cherry, SQLChicken, Mark Souza, Allen White, Buck Woody, Dan Jones, Paul Randal (and Kim of course), Wendy Pastrick, Steve Jones, Brian Knight (and Devin), Grant Fritchey, Adam Machanic, and the late Ken Henderson are people I’m proud to call my friends. There are many more and sorry I can’t just sit here and name everyone, but you know who you are. You’re the friends of not only the MidnightDBA brand, but of me personally. So while I may have alienated a certain population of the SQL community, it may be best for both of us if we just leave it as it is. Because compromising myself just to pick up a few extra followers or to be able to speak at a single conference will not only disappoint me, but ultimately it’ll disappoint the fans because not being yourself isn’t sustainable.
Besides guys, don’t take yourself so seriously. Let’s lighten up and have some fun with DBs. It’s a stuffy enough topic as it is without us making it worse. And I don’t know really what it is about me that gives me such a reputation. There are speakers out there who say far worse things on stage at conferences than I ever have (or would), and nobody thinks the worse of them. I’ve heard very prominent speakers make some pretty racy sexual jokes, as well as some really borderline racist jokes. Yet they keep getting invited back to speak at the same conferences year after year. Whereas I separate my blogging life from my speaking life and I know there’s a difference between what I can say on my blog and what I can say on a stage. Just like there’s a difference between what I can say on my personal blog and what I can say on my NetworkWorld blog, or in an official magazine review. So it’s not like I don’t know the difference between the different outlets, but for some reason people act like I don’t.
To tell the truth I had really planned to cover the topic of whether you should brand yourself under your name or under some clever moniker like MidnightDBA or SQLChicken, but I got sidetracked and now this is just getting too long. So maybe someone else will cover it instead.
And what all this boils down to is you should really just be yourself. You are what makes your brand, and that’s why it’s *your* brand. Nobody else can step in and be the MidnightDBA. So whatever you choose to do, just realize that you’ll never please everyone. If I were to sanitize my writing, then I would lose the audience I have now because that’s not what they want to see. My writing wouldn’t be interesting and my webshow would be a flop. Sure, I would gain the other half of the industry I don’t have now, but at what cost? At the cost of my self-respect. And not being myself wouldn’t be fun for me so I wouldn’t write as much. So be yourself. People will either follow you or they won’t, but at least you’ll know the ones who do are yours to keep.
From time to time you run across something that demonstrates perfectly exactly what the industry needs. As I sit here at the PASS day-3 keynote I’m watching Dr. DeWitt talk about how the query optimizer finds query plans. This is a very complicated topic under the covers and he’s covering some of the really complicated aspects. He’s already explained the different types of histograms and how they work (I’m sure only at a high level), and he got into selectivity, etc. And the thing is, he’s explaining this stuff in such simple terms and making it so easy to understand, it just hit me like a ton of bricks; why isn’t this material being taught everywhere?
This is one of the things I’m always talking about when I say there’s no really good training out there. There are plenty of people out there teaching queries and tuning techniques, but nobody is bothering to break this stuff down so that people actually understand the terms and what they really mean. Most of the time what they do is just define some terms briefly and then move on and expect everyone to be able to go back home and apply this stuff. But without this background of how this stuff actually works and how the terms really fit together and how you get bad plans, etc, then people aren’t going to be nearly as successful as they could be. Dr. DeWitt is clearly concerned with teaching people how to think instead of just what to think.
This is always the type of training I move towards because I find I never commit anything to memory unless I understand how it all works and fits together. So what I’d like to see happen is for someone to put something like this together in a video series, or in a pre-con… something. That’s a pre-con I’d actually pay for out of my own pocket. A whole day of someone dedicated to making sure I understand how this stuff works… Hell Yeah! But seriously, where does someone go to learn stuff like this? Because this info isn’t out there in any human readable form. And it’s apparently not just me either, cause he’s been brought back by overwhelming request, so I’d say the better part of the room is also screaming for this type of info.
The new version of SSMS takes advantage of the new language services that the team has been working on for so long now. So far it’s actually looking pretty good. You can finally refactor columns throughout all of your code because the language service comes directly from the engine it knows everything about the code you’re looking at. It’s more of a dev feature, but plenty of DBAs will find this useful as we all wind up doing dev anyway.
There’ll be more on that in the future, but I just wanted to write this blurb to let everybody know that the language service is coming along. This is the first real step in bringing us a much better intellisense. Does that mean you’ll no longer need Red-Gate’s tool? No, because SSMS still won’t format your code for you or do some of the other cool things SQLPrompt will do. Things like * expansion, and uniform table aliases are just 2 of the things Red-Gate brings to the table. Unfortunately for them though, the new SSMS will start to close the gap so they’ll have to come up with something else if they wanna stay in that game.
FileTable is another new feature in Denali. FileTable is the next generation of FileStream. What this allows you to do is copy files into specified folders on your server, and they automatically get stored in SQL. So you define a FileTable in SQL and point it at a folder. Now, anything you put in that folder, shows up in the DB itself. You can still view the files directly from the file system, only when you do, you’re actually reading them out of the DB. So we’re really starting to obscure the line between DB and Windows.
This is one of those features that’s really cool. To be able to drag a group of files into a folder and have them automatically added to the DB is really cool. Not only can you add/delete them, but you can also modify them. You can set file properties using t-sql and it’ll persist that change to the file system. So now I have to wonder what the future of powershell will be because working with a large amount of files is something we do with powershell the most. It’s one of the things powershell does really well. But now that we’ve got this, if we’re able to define a folder as a DB table, then we don’t have to use powershell for these types of file ops anymore… we can just use set-based t-sql.
The possibilities are churning in my head.