All things SQL. Plus other stuff.

SQL Sam and the Never-Ending Delete

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.

“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:

0000000000000008002781234

“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:

This entry was posted on March 9, 2009, 1:43 pm and is filed under SQL Sam. You can follow any responses to this entry through RSS 2.0.
Responses are currently closed, but you can trackback from your own site.