Your SQL Server just decided to throw its toys out of the cot and it was right slap bang in the middle of that big index rebuild you were doing, or that mighty transaction. Now you look to carry on where you left off and really hope nobody picked this up. The only problem is that in SSMS you see the database status as “IN RECOVERY”.. What? Luckily SQL server is smart, really smart and it can repair itself when it hit a big boo-boo. Which is all well and good, but nobody want

Good question, and normally it gets accompanied with some shouting. We normally tend to stagger our workloads into something that resembles a lot of backups in a row and some smart index rebuilds followed by some more backups. But that is not really the point of this little post. I had a situation where I needed to do a “quick” backup on 500GB Database. Needless to say I wanted to tell everyone to bugger off my server for some time and I wanted to know exactly how long… ..‘C

Ever wanted to query your AD from SQL and then list all the members of your Distribution Lists? Because you can’t do it, sheesh, that was easy. There are some limitations in OPENQUERY which means you cannot query multi-value fields from SQL, but you can pull results from a specific Distribution lists. Well funny you should end up here then, because I have just the thing. Something like this would work to get members of a DL SELECT name FROM OPENQUERY( ADSI,'SELECT name, membe

I use Dynamic SQL queries for everything.. yes even for buttering my bread and putting on my socks. Yes, there are some security risks when using Dynamic SQL, but I know the risks and manage the implementation on code very carefully. In most instances this relates to script I run to manage stuff or reports. Where to begin: 1. Erland Sommarskog, dude you have made my life so much easier with this post. I keep it open in my browser at all times. The depth of information with re

So you want to make your life easier. You want a nice big whopping bonus. You want that dodgy admin who talks in korn shell to finally respect you because you can also script dammit.. Well you have come to the right place.Let’s get to the juicy bits. This script will show you: o Result Set 1: Shows you execution plans information, if you have high plan usage and lots of Adhoc queries consider changing to ‘optimize for ad hoc workloads’ o Result Set 2: Shows execution pla

Not really, but it is a nice little title isn’t it. So you are looking for a quick fix for your indexes. Use this little script. Thanks to Brent Ozar and Clayton Kramer for your work on the script, I just chopped it a bit for our South African climate and added some other sections. It will show you suggested indexes for performance improvements, then show you unused indexes and then finally heaps. Edit: I changed the “ChangeIndexStatement” that gets generated to include a fil

I seem to revisit this more often than I really feel comfortable with since the first post back in 2014, but there are just so many times when I cannot perform a restart and still need to make changes. Sometimes you just need to restart So if you cannot wait for a change window, or you find yourself in a pinch you can use this technique to bypass the "Restart Computer" check on SQL installations. 1. Mount the SQL server ISO 2. Open Command prompt and navigate to your SQL Serv