Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Aug 24, 1999

SQL Sam and the Never-Ending Delete - Part 1

By Steve Hontz

Environment: SQL Server 6.5, SP5a



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

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:

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    12

Continue with SQL Sam and the Never-Ending Delete

 

Go to the solution now!

 

Back to SQL Sam Cases


Steve Hontz is President of The Bit Corner, Inc., a Phoenix-based consulting firm specializing in Windows NT, C++, and SQL Server development. You can reach him at steve@bitcorner.com.




MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM