Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Why does TRUNCATE TABLE hang? Expand / Collapse
Author
Message
Posted Wednesday, October 24, 2012 10:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 9, 2014 11:51 PM
Points: 30, Visits: 119
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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Post #1376776
Posted Thursday, October 25, 2012 1:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 40,609, Visits: 37,070
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-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 40,609, Visits: 37,070
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

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, November 21, 2014 8:48 AM
Points: 323, Visits: 986


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




You seems a Oracle Guy !


-----------------------------------------------------------------------------
संकेत कोकणे
Post #1376845
Posted Thursday, October 25, 2012 3:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 9, 2014 11:51 PM
Points: 30, Visits: 119
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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 5,466, Visits: 7,647
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
Posted Thursday, October 25, 2012 4:39 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 8:15 AM
Points: 652, Visits: 1,428
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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 9, 2014 11:51 PM
Points: 30, Visits: 119
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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 9, 2014 11:51 PM
Points: 30, Visits: 119
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 12»»

Permissions Expand / Collapse