Database locking issues arose this morning for me. When two updates or selects happen to the same object and the same record, record/page locking occurs. In my defense I was running a script for someone else, also I didn’t realize that there were two recurring jobs that started moments into running my script. I had the DB in restricted user mode. I only knew of one job that was going to start this morning but I should have been done with my work way before it ran.

Here is a simple debug script you can run to see if you have locking on your database and what spids are causing it: 

SELECT status, SPID, CPU, Physical_IO, WaitTime, Blocked, HostName, Program_Name, nt_username,Login_Time, Last_Batch, Open_Tran, waittype, lastwaittype
FROM master..sysprocesses with (nolock)
where blocked >0 or open_tran > 0

Also there is the SQL Server 2000 sp_lock command MSDN article about it’s results is here.

After a phone call or two I managed to disable the jobs and kill off any spids that were keeping my script from finishing. I then reenabled/ran them safely when I was done. The angels sang, the publish was complete, and had passed testing.

Lesson learned? Be more attentive to what jobs are running on the server, ugh. *smacks forehead* Gah.

In: Uncategorized | | #