RE: killed/rollback stuck on object_name(99)

[Enric]

hi,

Right, try again using this:
KILL <your_process> WITH STATUSONLY

Because of your process has been running a long time the rollback will take
a lot of time (not the same, of course, but a lof anyway)

Rollback is undoing changes and transactions commited

--
Current location: Alicante (ES)

"tthrone" wrote:

> Hello:
>
> I have a process that's been stuck for two days.. It's a stored procedure
> that runs as part of a scheduled SqlAgent job. I tried to kill the process
> which put it into a rollback. Kill with statusonly returns:
> SPID 52: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
>
> I ran dbcc page for the resource that is listed in the wait type
> (PAGEIOLATCH_UP)
> and it points to Obj_id 99. Running "select object_name(99)" returns the
> object name "Allocation".
>
> Does anyone know what this means and how to allow the rollback to complete?
> This spid blocks other processess that try to run in the affected database.
> Even Enterprise Manager is blocked. Can't refresh table list or procedure
> list in EM. Current activity times out. I can use sp_who2 to see active
> processes.
>

[tthrone]

Hi Enric,

I did that. It returns:

> SPID 52: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
>

It's been returning the same thing for two days. It doesn't appear to be
making any progress on the rollback. The original process should have done
123,000 row inserts on a previously empty table. I can't imagine 123k rows
should take 2 days to rollback. I think it's totally stuck and idle.

[JT]

First, try to find out what application and T-SQL statement caused this
situation so perhaps it won't repeat:
DBCC INPUTBUFFER (spid) will display the last T-SQL statement sent by the
client application owning this SPID.
SP_LOCK (spid) will list information about what specific objects the SPID
currently has locked and what type of lock (table, page, etc.).

Next, try to diagnose what is going on with your server hard disks, memory,
etc. that may have caused this unusual cirsumstance. If the server is
running critically low on disk space, this can cause problems when
attempting rollback a large transaction. Also, go into the windows
management console and review the event logs for possible evidence.

This article describes how get more detailed information about the current
status of the SPID:
http://support.microsoft.com/default.aspx?scid=kb;en-us;171224

For example, the Process Status Structure (PSS) has the following values:
0x4000 -- Delay KILL and ATTENTION signals if inside a critical section
0x2000 -- Process is being killed
0x800 -- Process is in backout, thus cannot be chosen as deadlock victim
0x400 -- Process has received an ATTENTION signal, and has responded by
raising an internal exception
0x100 -- Process in the middle of a single statement transaction
0x80 -- Process is involved in multi-database transaction
0x8 -- Process is currently executing a trigger
0x2 -- Process has received KILL command
0x1 -- Process has received an ATTENTION signal

This article describes how to identify and troubleshoot an orphaned
connection:
http://support.microsoft.com/kb/137983/EN-US/

If the SPID can't be killed, then:
1. stop the SQL Server service (no need to reboot)
2. using Windows Explorer, move the data and transaction log file(s) to
another location
3. re-start the service
4. restore the database from the most recent backup

"tthrone" <tth...@discussions.microsoft.com> wrote in message
news:D7361B71-3C5A-41CE...@microsoft.com...

[tthrone]

Thanks JT. I know some answers to questions/issues you listed. I know the
transation that was in-flight, but I don't know why it stuck. Still can't
figure out why it remains stuck, but I found something interesting in the
process of doing some of what you suggested.

For one, I see this spid blocks some of my attempts to use sysobjects. I
mentioned that I can't refresh procedures or tables in EM on this database.
I think that's why. I have it narrowed down to one (maybe a few) affected
tables. I can query sysobjects so long as I don't try to read certain rows.
Not sure how that happened!

I think I'm going to have to try your suggestion about stopping the service
and restoring.

Thanks for the help.

[JT]

When querying sysobjects (or any other blocked table), you can get around
the locks by changing the isolation level to read uncommitted data. However,
this should not be used in a production system except perhaps in some
reporting situations.

set transaction isolation level read uncommitted

select * from sysobjects

"tthrone" <tth...@discussions.microsoft.com> wrote in message

news:1C4B57D4-1894-4688...@microsoft.com...

[tthrone]

I normally do set the transaction isolation level to read uncommitted. I did
that in this case as well.
I even tried using the hint "with(readuncommitted)" but it was still blocked
by the stuck spid when I tried to return the sysobject rows of tables that
were affected.

Our DBA is going to bounce the service later today. I'm hoping it will
clear up after the restart.