KILLED/ROLLBACK WAITYPE OLEDB

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mchuc7719 - 23 Jul 2008 23:14 GMT

Hello to all,

I have SQL Server 2005 SP2 9.0.3054.

I have KILLED a SPID 93 7 days ago, but this spid has the Status:
KILLED/ROLLBACK, WaitType: OLEDB.

Why this process has taken to much time to Kill, How can I kill the process.
If I ran the KILL 93 WITH STATUSONLY I get the message: SPID 93: transaction
rollback in progress. Estimated rollback completion: 0%. Estimated time
remaining: 0 seconds.

Regards.

Reply to this Message

Ajay Rengunthwar - 24 Jul 2008 05:19 GMT

On Jul 24, 3:14 am, mchuc7719 <mchuc7...@discussions.microsoft.com>
wrote:

> Hello to all,
>
[quoted text clipped - 9 lines]
>
> Regards.

Hi,

Could you please give some more details like. The Server you are using
and the database size ..... Also if this server is used only for
database purpose...

Thanks
Ajay

Reply to this Message

mchuc7719 - 24 Jul 2008 16:38 GMT

Hello Ajay,

Well we are using a Dell Server with SQL Server 2003 R2, SQL Server 2005 SP
2 9.0.3054 and this server is used only by SQL Server.

I have a Job running every day at 7:00 a.m. this job execute a SP within
this procedure are using around 5 linked server to INTERBASE (Open Edition)
databases using Firebird ODBC.

I have already Stoped the Interbase server, I have restarted the DTC in my
Server and also I dropped the linked server, but the process still in
KILLED/ROLLBACK.

Regards.

Thank you.

> On Jul 24, 3:14 am, mchuc7719 <mchuc7...@discussions.microsoft.com>
> wrote:
[quoted text clipped - 20 lines]
> Thanks
> Ajay

Reply to this Message

Uri Dimant - 24 Jul 2008 08:11 GMT

Hi
Most likely this SPID was douing pretty big transactions to modify the data
and now it is rolling back to the previous state.
Wait utill it will be completed

> Hello to all,
>
[quoted text clipped - 11 lines]
>
> Regards.

Reply to this Message

Dan Guzman - 24 Jul 2008 12:08 GMT

Does the killed session involve any linked servers, OPENQUERY, etc.? In
that case, check connections to the remote server. Perhaps killing that one
too might clear things up.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

> Hello to all,
>
[quoted text clipped - 11 lines]
>
> Regards.

Reply to this Message

mchuc7719 - 24 Jul 2008 16:43 GMT

Hello Dan,

Well we are using a Dell Server with SQL Server 2003 R2, SQL Server 2005 SP
2 9.0.3054 and this server is used only by SQL Server.

I have a Job running every day at 7:00 a.m. this job execute a SP within
this procedure are using around 5 linked server to INTERBASE (Open Edition)
databases using Firebird ODBC.

I have already Stoped the Interbase server, I have restarted the DTC in my
Server and also I dropped the linked server, but the process still in
KILLED/ROLLBACK by 8 days ago.

But the JOB still working fine.

Colud you tell me what will happen if I restart my SQL Server service, my
database will be damaged?.

Regards.

> Does the killed session involve any linked servers, OPENQUERY, etc.? In
> that case, check connections to the remote server. Perhaps killing that one
[quoted text clipped - 15 lines]
> >
> > Regards.

Reply to this Message

Russell Fields - 24 Jul 2008 19:59 GMT

Well, if this has been in KILLED/ROLLBACK for 8 days, even after your
restart of the server to which you are linked, it seems that you are left
with one option. To shutdown and restart your SQL Server.

I have had to do this a few times, and never have had corruption. (That I
know of.) It has been some years since my last run-in.

You could run a DBCC CHECKDB on the affected DB afterward in order to check.

RLF

> Hello Dan,
>
[quoted text clipped - 38 lines]
>> >
>> > Regards.

Reply to this Message

mchuc7719 - 24 Jul 2008 20:32 GMT

Hello Russell,

Thank your Response, Well I need to schedule the restart de service the
next Week, due at the beginning of the weekend we have a much more activity
than the beginning of the week.

Thank you again, Best Regards.

> Well, if this has been in KILLED/ROLLBACK for 8 days, even after your
> restart of the server to which you are linked, it seems that you are left
[quoted text clipped - 49 lines]
> >> >
> >> > Regards.

Reply to this Message

Med Bouchenafa - 24 Jul 2008 20:30 GMT

you are not alone with this case
I have seen this behaviour so many times with linked servers
Somebody summarize the case here
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=187192

regards
Med Bouchenafa

> Hello to all,
>
[quoted text clipped - 11 lines]
>
> Regards.

Reply to this Message