Today, through various snafus and situations, I found myself waiting a LONG time for a lock to clear up. Here's how I got an alert to pop up on my phone (an iPhone) when that stupid SPID finally rolled back.
You'll need to have database mail configured and ready to go.
First, configure your iPhone to display an alert when you receive VIP mail. Open mail and click on the little "i" in the circle by the VIP section in the mailboxes list. Click "VIP Alerts" and configure as desired. I have all of them selected and a sound configured.
Second, send yourself an email from the server using
Third, on your iPhone, open the email and click on the From field. That will open a contact form for that email address (aka the server). There should be an option to add to VIP on the screen.
Fourth, write a query to email yourself. That should look something like this (typed from memory and subject to typos):
USE msdb; SELECT TOP 1 * FROM TheDB.dbo.TheLockedTable; EXEC sp_send_dbmail @Subject = 'It''s Done!', @Recipients = 'YourEmail';
Make locking work FOR you for a change. The SELECT will block until the lock clears. It sends you an email and you'll get an alert moments later.
Fifth, remove that server from your VIP list. The last thing I want is alerts on every email from my database servers.