Ran into an issue today where I was trying to track down a mysterious lock on one of the Databases I was working on.  After banging my head against the keyboard for a bit I was directed to Adam Machanic's WhoIsActive Script and the day was saved.  I'll do a quick demo to show you some of the more basic features but I'd recommend taking a look at it in detail.

Say we have an evil SQL Script like the one below:

Begin Transaction t1
while 1=1
BEGIN
    update Person.Address set AddressLine1 = '1970 Napa Ct.'
    WHERE AddressID = 1
END
Commit Tran t1

Once this bad boy starts executing it will lock that record to all updates until it completes (so forever).  Now lets suppose someone else comes along and tries to run the query below (notice the red boxes):

At this point you realize that something has gone horribly wrong...  But not to worry, once you've installed the WhoIsActive stored procedure you can quickly identify which processes are running and which ones are causing blocks.  Lets take a look at the results of EXEC sp_WhoIsActive below:

After a quick look at the results we can see that the blocking_session_id is 55.  Once we know that we can either kill the session or wait it out.  Luckily we can also take a look at the SQL that is executing because it is in the sql_text column, so you will know that waiting it out would be a lost cause.  I recommend downloading the WhoIsActive script and trying it out for yourself!