killed/rollback stuck on object_name(99)

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.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 proces
s
> 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.
>|||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.
"Enric" wrote:
> hi,
> Right, try again using this:
> KILL <your_process> WITH STATUSONLY
> Because of your process has been running a long time the rollback will tak
e
> 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:
>|||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/defaul...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" <tthrone@.discussions.microsoft.com> wrote in message
news:D7361B71-3C5A-41CE-A4D0-68685B910E3E@.microsoft.com...
> 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.
>|||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" wrote:

> 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/defaul...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" <tthrone@.discussions.microsoft.com> wrote in message
> news:D7361B71-3C5A-41CE-A4D0-68685B910E3E@.microsoft.com...
>
>|||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" <tthrone@.discussions.microsoft.com> wrote in message
news:1C4B57D4-1894-4688-8E32-6157E28AD503@.microsoft.com...
> 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" wrote:
>|||I normally do set the transaction isolation level to read uncommitted. I di
d
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.
"JT" wrote:

> When querying sysobjects (or any other blocked table), you can get around
> the locks by changing the isolation level to read uncommitted data. Howeve
r,
> 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" <tthrone@.discussions.microsoft.com> wrote in message
> news:1C4B57D4-1894-4688-8E32-6157E28AD503@.microsoft.com...
>
>