Category Archives: Database Administration

Blocking – I Saw What YOU Did and I Know Who YOU Are!

When I was growing up I remember my mom talking about an old, scary, movie that she saw when she was young. In the movie some teenagers were making prank phone calls saying “I saw what you did and I know who you are.” One of the calls happened to be made to a guy that just killed his wife. Joan Crawford played a woman that was romantically inclined to said murderer. She eventually meets her demise when he stabs her because she knew about the first murder.

How every DBA feels when there is blocking

While blocking in SQL server might not be a felony offense (it isn’t…but it should be – WHO’S WITH ME?) as the DBA you not only want to know what is being blocked, but also who is doing the blocking and what in the H-E-Double-Hockey-Sticks they are doing.

At SQL Saturday Orlando I talked about this very thing and the query I defer to for the information.

IMA GONNA HUNT U DOWN!

It might look complicated but it is actually very simple – query sys.sysprocesses with a cross apply using the sql_handle to get the text of the query, and then an outer apply with the same query again but you are joining to the blocking spid so you can get the text for the query that is doing the blocking. Beyond that, you can filter on various columns and refine your output

WHY ARE YOU BLOCKING YOURSELF? WHY ARE YOU BLOCKING YOURSELF? WHY ARE YOU BLOCKING YOURSELF?

Of course, I know you can’t run this without the code (and I know that’s why you’re here…because I SAW WHAT YOU DID!)

If you are looking for a good way to troubleshoot blocking I hope this helps. If you have some folks running queries that are making you stabby, run this, find out what is going on, and then remove their access try to help them so they aren’t making you stabby any more. Then tell them they need to buy you a beverage because they are still alive.

UPDATED: PowerShell – WE HAZ YUR ACTIVE DIRECTORY RIGHT HERE!

I discovered something last week – I had not blogged about little things that I thought I had blogged about. What the heck does that mean? It means that I tried to reference my blog for something because I thought “I totally blogged about that”…and found out that was not the case.

Starting now, I am fixing this situation. There was something that popped up today that called for a PowerShell script and the Get-ADGroupMember cmdlet – get a list of users from a list of groups. Some users are in there more than once so this needs to be a distinct list, unless you are into manually cleaning up things like this, and then I will be sad for you. Because that is kinda sad.

I originally wrote a script with two arrays (one for the initial list and one for the de-duped list of users), but even though this is quick and dirty, that was a little too dirty. Enter the Group-Object cmdlet – it takes this list of names and groups them. No black magic this time. Just a cmdlet, that comes baked into PowerShell giving me what I need.

What? You wanted the code too? Oh, OK.

There you have it – quick, dirty and to the point. Enjoy. 🙂

UPDATE: Mathias Jessen tweeted a one liner for this….so no need for the one array! Woohoo!

I was trying to do this but was also just trying to get it done, and if in doubt, I slap things in arrays. Thanks Mathias!

 

Speaking – SQL Saturday Baton Rouge!

This Saturday will mark the fourth year that I will be speaking at SQL Saturday Baton Rouge at LSU. Out of all the SQL Saturday events I have participated in since 2014, Baton Rouge is one of the few that I have been to every year since. Houston is another one…and actually, these may be the only ones only because they have had an event every year.

Not only am I speaking on Automation with PowerShell and Deadlocks and Blocking, but I am also participating in a panel discussion on Careers in IT. I’m excited to be invited along with some of the other speakers to be a part of this. Looking back on my own school days, I knew I would have a career in IT, but little did I know I would detour from a path in development to the world of SQL Server, and becoming a DBA.

How do they make this happen? Work. Lots of hard work. After helping with SQL Saturday Dallas 2015, joining the NTSSUG board in 2016, and then having an organizing role in 2016, I found out how much goes into the planning of these events. If you have attended a SQL Saturday or you are going to in the future, be sure to say “THANK YOU!” to all the organizers and sponsors. If you want to get more involved in the SQL Server community, SQL Saturday is a great way to do that – just show up at the event, find an organizer and tell them that the SQL Kitten sent you to be their humble servant volunteer for the day…or you could just say you want to volunteer and leave out the other stuff because it might make it weird.

Blogging reboot and PowerShell – Get System Information

After whining about not blogging enough, I am going to do something about this. Whether it is PowerShell or SQL, simple or complex, I know that others can benefit from my knowledge and expand their skill sets.

And you are like “That’s great Amy…where’s the PowerShell we are here for?” Ok, Ok….keep your drawers on! 😉

Recently I was tasked with gathering the system information from all of the servers at a client. Another opportunity for some PowerShell dominance.

With this script you can generate system information files and save them to a specified location. It makes sure a connection can be made to the server first, and then outputs the file. The files are created one at a time, so if you pass in a longer list of servers, you shouldn’t crash your machine. From my testing, this will take some time to run as these files don’t output quickly. Despite that, the output is worth it. This can be modified to pull your list of servers from a file or from a Central Management Server (CMS) instance.

Hope you find this useful. Happy PowerShellin’ 🙂