October 24, 2012 at 10:58 pm
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?
October 24, 2012 at 11:12 pm
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 🙂
October 25, 2012 at 1:39 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 25, 2012 at 1:40 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 25, 2012 at 2:53 am
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 🙂
You seems a Oracle Guy !
-----------------------------------------------------------------------------
संकेत कोकणे
October 25, 2012 at 3:12 pm
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.)
October 25, 2012 at 4:02 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 25, 2012 at 4:39 pm
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)
October 25, 2012 at 6:03 pm
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.
October 25, 2012 at 6:08 pm
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.)
October 25, 2012 at 7:00 pm
I killed the 3 SPIDs. They now show 'KILLED/ROLLBACK' under the 'Command' column of sys.dm_exec_requests. However, the tables are still locked.
What now? Will detaching the database help?
October 25, 2012 at 7:43 pm
The rollback can take as long as the transaction that led to it. Give it time to rollback. It will still hold the same locks until the transaction completes. If you are still having issues, repost and I will look at it in the morning.
October 25, 2012 at 8:04 pm
Thanks, George. Will do.
October 26, 2012 at 3:14 am
Do not detach or restart SQL when you have sessions in rollback. SQL will have to continue the rollback after you reattach or restart and the entire DB will be unavailable while it does so.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 28, 2012 at 6:13 pm
Thanks Gail.
My tables are still locked. The database is now useless to me.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply