Why does TRUNCATE TABLE hang?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2008 » SQL Server 2008 - General » Why does TRUNCATE TABLE hang?

Add to briefcase

18 posts, Page 1 of 2

1 2 »»

Why does TRUNCATE TABLE hang?

Expand / Collapse

Author

Message

Posted Wednesday, October 24, 2012 10:58 PM

SSC Rookie

Group: General Forum Members
Last Login: Sunday, May 19, 2013 5:20 PM
Points: 30, Visits: 84

I'm trying to empty a staging table using TRUNCATE TABLE in a stored procedure, but the sp just hangs.

The table is only 400k rows, nobody else should be accessing it, and the sp worked previously. The only thing I think I've changed is to add indexes.

What should I try?

Post #1376771

Posted Wednesday, October 24, 2012 11:12 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 8:34 AM
Points: 23, Visits: 128

There must be an open transaction on table which means there is a lock on table

u can follow these steps

check which query is lock the object u r trying to truncate by following.

Select blocking_session, sid, serial#, wait_class,seconds_in_wait From v$session where blocking_session is not NULL order by seconds_in_wait

let me know if problem is still there Smile

Smile

Post #1376776

Posted Thursday, October 25, 2012 1:39 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:17 PM
Points: 37,713, Visits: 29,968

Something's blocking the truncate.

Check sys.dm_exec_requests, see what the wait type is, what the blocking session is. See what that blocking session is/was doing.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1376818

Posted Thursday, October 25, 2012 1:40 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:17 PM
Points: 37,713, Visits: 29,968

peterdru401 (10/24/2012)

check which query is lock the object u r trying to truncate by following.

Select blocking_session, sid, serial#, wait_class,seconds_in_wait From v$session where blocking_session is not NULL order by seconds_in_wait

Is v$session some custom view that you have? It's not a standard SQL Server object.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1376819

Posted Thursday, October 25, 2012 2:53 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 323, Visits: 964

u can follow these steps

check which query is lock the object u r trying to truncate by following.

[b]Select blocking_session, sid, serial#, wait_class,seconds_in_wait From v$session where blocking_session is not NULL order by seconds_in_wait

let me know if problem is still there Smile

Smile

You seems a Oracle Guy !

-----------------------------------------------------------------------------
संकेत कोकणे

Post #1376845

Posted Thursday, October 25, 2012 3:12 PM

SSC Rookie

Group: General Forum Members
Last Login: Sunday, May 19, 2013 5:20 PM
Points: 30, Visits: 84

Thanks, all.

"Invalid object name 'v$session'."

I queried sys.dm_exec_requests as recommended by Gail, but I don't know what I'm looking at. Nothing in the output refers to the problem table by name.

(Nb. I have had training in BIDS but not in SQL Server, and there are massive gaps in my self-education.)

Post #1377306

Posted Thursday, October 25, 2012 4:02 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:02 PM
Points: 5,674, Visits: 6,118

Allen,

Short form, Truncate Table requires an exclusive lock on the table schema (SCH_M) to activate, which means that it can be the only thing accessing the table when it tries to run.

What you're going to end up having to do is try to figure out where you've got a hung transaction that's got a SCH_S lock against that table or one of the indexes. It's going to be a bit of a dig to pull it off and it can be a pain in the arse.

It may seem like overkill, but 'bounce' the server. Just stop/start the SQLServer process. It'll go through a shutdown/recovery process (which you DON'T want to stop for any reason if you can help it) but that should clear any lingering transactions or unexpected issues.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1377322

George M Parker

Posted Thursday, October 25, 2012 4:39 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 09, 2013 1:13 PM
Points: 516, Visits: 1,004

Something has to be blocking the truncate table statement. This is kind of an old school script as it uses code from sp_lock, but it should tell you exactly which connections have locks on the table and what type of locks they are.

declare @tablename sysname
select @tablename = 'Replace with your table name'

select convert (smallint, req_spid) As spid,
rsc_dbid As dbid,
rsc_objid As ObjId,
rsc_indid As IndId,
substring (v.name, 1, 4) As Type,
substring (rsc_text, 1, 32) as Resource,
substring (u.name, 1, 8) As Mode,
substring (x.name, 1, 5) As Status
from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
where master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
and rsc_objid = object_id(@tablename)

Post #1377327

Posted Thursday, October 25, 2012 6:03 PM

SSC Rookie

Group: General Forum Members
Last Login: Sunday, May 19, 2013 5:20 PM
Points: 30, Visits: 84

Thanks, Craig.

Unfortunately, I'm developing on a production server (not my choice), and there's a lot of stuff on it. I might be able to get it restarted over the weekend, but for now I think I'll have to use a surgical approach.

Post #1377337

Posted Thursday, October 25, 2012 6:08 PM

SSC Rookie

Group: General Forum Members
Last Login: Sunday, May 19, 2013 5:20 PM
Points: 30, Visits: 84

Thanks, George.

Your script turned up the same 3 SPID's I became suspicious of after I ran EXEC sp_who2. Should I just kill them? (They are my processes.)

Post #1377338

« Prev Topic | Next Topic »

Add to briefcase

18 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse