Wednesday, March 17, 2010

One TRUNCATE TABLE Can Use Up All Your Locks (in SQL Server 2000)

Here's an example of how you can learn more from bad schemas than good schemas. This happened to me under SQL Server 2000, and I'm wondering if anyone has seen it on later versions. (post hoc - Sankar has demonstrated that this behavior has changed as of SQL Server 2005.)

I was attempting to truncate a table with maybe 100 million rows (don't ask - that's the bad schema part). When I issued the TRUNCATE TABLE command, however, I got:

Error: 1204, Severity: 19, State: The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.

What I've learned from http://support.microsoft.com/kb/257345 is that "almost all of the locks acquired for a TRUNCATE statement are for extent deallocations". I didn't even know TRUNCATE TABLE used locks, but it makes sense now, of course. I ended up dropping the table and re-creating it.

2 comments:

Sankar said...

Hi Larry,

The functionality you outlined above happens before SQL Server 2005 only. In 2005 and above, an optimization has been implemented to do a 'deferred drop & truncate'. I wrote about this a while ago and you can look up the info @ http://sankarreddy.spaces.live.com/Blog/cns!1F1B61765691B5CD!440.entry

Larry Leonard said...

Thanks, Sankar. I read your article - very informative!

Post a Comment