It's important to be able to analyze the deadlock graph once it's been detected. I'm going to give a brief run down on what I look at. The process nodes show information regarding the actual modification. The node that is X'd out is known as the deadlock victim. This is the process that was killed as part of the deadlock solution. Each process node shares the SPID, the deadlock priority (ranges from LOW: -5, Normal: 0, and High: 5), Log Use (the space the process uses in the

Deadlocks can be a serious plague to your existing systems. I am a fan of being proactive whenever possible. So instead of waiting for complaints from users or application developers, and then having to dig through logs and extended events, we can setup some type of alert. I've had clients that did not have any means of knowing when a deadlock was occuring on their server. How to setup a deadlock alert: Turning on a few trace flags might be a good idea. Trace Flag 1205 and 1

What is it? A trigger is almost like a command/function that executes automatically in response to another transaction that has executed. Think of it as kind of a stored procedure that executes automatically when an event occurs. Types: DML Triggers - After and Instead AFTER triggers can run after an insert, update delete or merge. INSTEAD OF trigger can override the actions of a particular DML statement. They can provide some sort of error or value checking. Virtual Tables:

While Azure's web portal is quite sophisticated and streamlined, nothing beats the efficiency of powershell. Building off of my previous posts, I'm going to provide a few powershell commands to login and create a new Azure SQL Database. The first step allows you to login to your Azure account. It gives you a neat little prompt. Once logged in, the powershell window will provide you with some information regarding your subscription. The next two commands sections provide a w

You might be turning the gun on yourself if you ever have to implement this. Disclaimer: I would never suggest running this in a production environment without good reason. I was asked to come up with something that will automatically kill blocked processes until the developers could figure out how to fix it (despite my suggestions for performance tuning). I wrote a simple stored procedure and it was placed into a job that ran continually. You can, of course, modify the quer

We could use this sign when we have approved downtime for upgrades. So this isn't exactly just for SQL, this cmdlet can be used stop and start any clustered service. It proved especially useful for me since I am currently on a project that requires the systematic stoppage of clustered SQL Services so that the storage team can do their thing. As always, test this in a non production environment before implementing during a migration. Prerequisite: Import the failover clusters

Mendenhall Glacier SQL cruise was something I've always wanted to do ever since it's inception (years ago). Of course, no company was going to send me to any conference with the word "cruise" in the title. Rather, they would send me to local training facilities that cost 3x's the price and pushed an agenda that wasn't in line with what the businesses I was currently working with wanted. It was also taught by instructors who were not MVPs, MCMs and renown authors. Now that I'm

Is this where clusters go when they die? A great way to test failover or to simply move disable a clustered SQL Service is to utilize powershell. You can create a script and hand it over to the server team or you can schedule it. Most of the time certain cluster maintenance tasks take place after business hours. And if you've already worked a long day, it may free up a bit of your time and energy to use powershell. Now, the failover cluster module is only available if Failove

I couldn't find a relevant photo for this post. But you can enjoy a nice cup of coffee in the morning when you automate stuff. As always, a great way to save time is to automate SQL Server installs. If you're in a situation where you have to install SQL Server on multiple servers in the same day and they're standardized you can take advantage of the unattended install method. First thing is to run through the setup wizard so that you can create the configuration.ini file. Thi

My war torn copy. I spent the last few months looking like a total dork reading this book on the train. But I'm a rockstar when I get to my client's office thanks to much of the information found in this resource. The book doesn't just give you scenarios and how to react to them, rather it helps you to think analytically when facing certain SQL Server symptoms. Places to start and then you use your understanding of the process to direct the next troubleshooting steps. After r