Out of Control

“Sam! Stop this crazy thing!” pleaded Chuck Watson, Data Center Manager, as SQL Sam strode into the server room. Next to Chuck stood an official-looking man and woman, dressed in neat suits. Sam recognized them as Dana Nully and Fox Nullder, the DBAs sent from the consulting agency.

“Don’t worry, Chuck, whatever it is, we’ll get it figured out,” soothed Sam. “What’s going on?”

“We’re just trying to do a simple delete, Sam, and the system just keeps banging away at the disks for hours and never finishes!”

Nullder stepped forward. “What we have here is a clear case of alien abduction,” he said.

Nully rolled her eyes. “Nullder, we have no evidence that aliens were involved here,” she reminded him.

“Well, how else do you explain it, Nully? There’s no way a simple DELETE statement could run so long!”

“Hmmm,” said SQL Sam. “I believe the truth is in here. Let’s see what we can find out. Chuck, tell me exactly what you’re trying to do.”

“Well, Sam, we have a customers table in our accounts database that uses a char(25) column as the primary key. It holds the customer’s phone number, left-padded with zeroes, like this:


“For our testing, we populated the table with 5 million rows of data. Now we want to remove all the dummy customer numbers that look like 800-9XX-XXXX. That’s about a million rows of data. We made a simple DELETE query like this:

DELETE FROM customers WHERE cust_num LIKE '%8009______'

“But this simple DELETE has been a nightmare! First, we ran out of space in the log, even though Truncate log on checkpont was turned on. So we increased the size of the log. Now we don’t run out of space, but the DELETE still runs forever. We’ve even killed the process, even rebooted, but then the database takes forever in recovery mode. So, we’re trying it again. But the delete never stops!”

“Aliens,” muttered Nullder.

“Let’s see if there are any blocking processes,” Sam said, ignoring Nullder. Sam ran sp_who2 and got this result:

SPID  Status     Login HostName    BlkBy DBName                      Command          CPUTime DiskIO LastBatch      ProgramName SPID
----- ---------- ----- ----------- ----- --------------------------- ---------------- ------- ------ -------------- ----------- -----
1     sleeping   sa      .           .   master                      MIRROR HANDLER   0       0      08/10 14:24:22             1
2     sleeping   sa      .           .   master                      LAZY WRITER      0       0      08/10 14:24:22             2
3     sleeping   sa      .           .   master                      CHECKPOINT SLEEP 0       37     08/10 14:24:22             3
4     sleeping   sa      .           .   master                      RA MANAGER       0       24     08/10 14:24:22             4
12    RUNNABLE   sa    TESTSYS       .   accounts                    INSERT           12768   2765   08/10 14:30:40 MS SQLEW