Hi,
Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
#######################################################
spid
--
72
95
kpid
--
512
4784
lastwaittype
--
LCK_M_SCH_M
LCK_M_SCH_M
waitresource
--
TAB: 2:1095871390:0
TAB: 2:1544549701:0
login_time
--
2008-02-01 16:24:49.603
2008-02-01 16:44:10.533
last_batch
--
2008-02-01 16:24:50.590
2008-02-01 16:44:10.797
open_tran
--
1
1
status
--
suspended
suspended
hostname
--
WWW1
WWW2
program_name
--
.Net SqlClient Data Provider
.Net SqlClient Data Provider
cmd
--
KILLED/ROLLBACK (before kill was EXECUTE)
KILLED/ROLLBACK (before kill was EXECUTE)
Kill:
SPID 72: transaction rollback in progress. Estimated rollback completion:
0%. Estimated time remaining: 0 seconds.
SPID 95: transaction rollback in progress. Estimated rollback completion:
0%. Estimated time remaining: 0 seconds.
kill 72 with statusonly and kill 95 with status only say the same
#######################################################
I read archive post on group and mainly people suggest restart sql service.
I have this on production environment, so it isn't so easy to do.
Do you know any better way I think restart should be the last one.
--
Regards,
anxcompanxcomp,
I agree that restarting the service should be the last resort.
Unfortunately, I do not know of another resort.
If the locks still being held do not block anyone, then you can wait to
schedule the restart, but if it is blocking users of the system then you
will need to schedule the restart soon. (Happened to me today on a
development server. Always frustrating, but very much so on a production
server.)
RLF
"anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
news:B6034F08-7EA9-47C5-A8F7-CD7B3BD38005@.microsoft.com...
> Hi,
> Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
> #######################################################
> spid
> --
> 72
> 95
> kpid
> --
> 512
> 4784
> lastwaittype
> --
> LCK_M_SCH_M
> LCK_M_SCH_M
>
> waitresource
> --
> TAB: 2:1095871390:0
> TAB: 2:1544549701:0
> login_time
> --
> 2008-02-01 16:24:49.603
> 2008-02-01 16:44:10.533
> last_batch
> --
> 2008-02-01 16:24:50.590
> 2008-02-01 16:44:10.797
> open_tran
> --
> 1
> 1
> status
> --
> suspended
> suspended
> hostname
> --
> WWW1
> WWW2
> program_name
> --
> .Net SqlClient Data Provider
> .Net SqlClient Data Provider
> cmd
> --
> KILLED/ROLLBACK (before kill was EXECUTE)
> KILLED/ROLLBACK (before kill was EXECUTE)
> Kill:
> SPID 72: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
> SPID 95: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
> kill 72 with statusonly and kill 95 with status only say the same
> #######################################################
> I read archive post on group and mainly people suggest restart sql
> service.
> I have this on production environment, so it isn't so easy to do.
> Do you know any better way I think restart should be the last one.
> --
> Regards,
> anxcomp|||Russel
Even if you restart the service, SQL Server attempts to recover the
database and in that case it will take long time.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:Ou5DpNEaIHA.4140@.TK2MSFTNGP04.phx.gbl...
> anxcomp,
> I agree that restarting the service should be the last resort.
> Unfortunately, I do not know of another resort.
> If the locks still being held do not block anyone, then you can wait to
> schedule the restart, but if it is blocking users of the system then you
> will need to schedule the restart soon. (Happened to me today on a
> development server. Always frustrating, but very much so on a production
> server.)
> RLF
> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
> news:B6034F08-7EA9-47C5-A8F7-CD7B3BD38005@.microsoft.com...
>> Hi,
>> Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
>> #######################################################
>> spid
>> --
>> 72
>> 95
>> kpid
>> --
>> 512
>> 4784
>> lastwaittype
>> --
>> LCK_M_SCH_M
>> LCK_M_SCH_M
>>
>> waitresource
>> --
>> TAB: 2:1095871390:0
>> TAB: 2:1544549701:0
>> login_time
>> --
>> 2008-02-01 16:24:49.603
>> 2008-02-01 16:44:10.533
>> last_batch
>> --
>> 2008-02-01 16:24:50.590
>> 2008-02-01 16:44:10.797
>> open_tran
>> --
>> 1
>> 1
>> status
>> --
>> suspended
>> suspended
>> hostname
>> --
>> WWW1
>> WWW2
>> program_name
>> --
>> .Net SqlClient Data Provider
>> .Net SqlClient Data Provider
>> cmd
>> --
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> Kill:
>> SPID 72: transaction rollback in progress. Estimated rollback completion:
>> 0%. Estimated time remaining: 0 seconds.
>> SPID 95: transaction rollback in progress. Estimated rollback completion:
>> 0%. Estimated time remaining: 0 seconds.
>> kill 72 with statusonly and kill 95 with status only say the same
>> #######################################################
>> I read archive post on group and mainly people suggest restart sql
>> service.
>> I have this on production environment, so it isn't so easy to do.
>> Do you know any better way I think restart should be the last one.
>> --
>> Regards,
>> anxcomp
>|||Uri,
I know that can happen, but in actuality I have never had it take more than
a couple of minutes to recover even for a transaction that had been running
a couple of hours. Perhaps this is because rollback in a database that is
not in use yet is a lot quicker than rolling back a busy database.
However, in the case where you cannot kill a process and it is holding
critical locks, is there another choice? If so, I would love to know how to
resolve the problem without restarting the server.
RLF
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e50Rz7JaIHA.4208@.TK2MSFTNGP04.phx.gbl...
> Russel
> Even if you restart the service, SQL Server attempts to recover the
> database and in that case it will take long time.
>
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:Ou5DpNEaIHA.4140@.TK2MSFTNGP04.phx.gbl...
>> anxcomp,
>> I agree that restarting the service should be the last resort.
>> Unfortunately, I do not know of another resort.
>> If the locks still being held do not block anyone, then you can wait to
>> schedule the restart, but if it is blocking users of the system then you
>> will need to schedule the restart soon. (Happened to me today on a
>> development server. Always frustrating, but very much so on a production
>> server.)
>> RLF
>> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
>> news:B6034F08-7EA9-47C5-A8F7-CD7B3BD38005@.microsoft.com...
>> Hi,
>> Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
>> #######################################################
>> spid
>> --
>> 72
>> 95
>> kpid
>> --
>> 512
>> 4784
>> lastwaittype
>> --
>> LCK_M_SCH_M
>> LCK_M_SCH_M
>>
>> waitresource
>> --
>> TAB: 2:1095871390:0
>> TAB: 2:1544549701:0
>> login_time
>> --
>> 2008-02-01 16:24:49.603
>> 2008-02-01 16:44:10.533
>> last_batch
>> --
>> 2008-02-01 16:24:50.590
>> 2008-02-01 16:44:10.797
>> open_tran
>> --
>> 1
>> 1
>> status
>> --
>> suspended
>> suspended
>> hostname
>> --
>> WWW1
>> WWW2
>> program_name
>> --
>> .Net SqlClient Data Provider
>> .Net SqlClient Data Provider
>> cmd
>> --
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> Kill:
>> SPID 72: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> SPID 95: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> kill 72 with statusonly and kill 95 with status only say the same
>> #######################################################
>> I read archive post on group and mainly people suggest restart sql
>> service.
>> I have this on production environment, so it isn't so easy to do.
>> Do you know any better way I think restart should be the last one.
>> --
>> Regards,
>> anxcomp
>>
>|||Russel
Yes, I had an epxerience where one person killed the long running
transaction and later on did a restart m and finally database is gone due to
recovering process
You are right , there is only chance to restart the service, I just wanted
to make apoint if you kill a long running transaction (DBCC REINDEX...) be
careful to restart the service and I'm recommeding to wait till rollback
will be completed
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OHiXLWMaIHA.5976@.TK2MSFTNGP05.phx.gbl...
> Uri,
> I know that can happen, but in actuality I have never had it take more
> than a couple of minutes to recover even for a transaction that had been
> running a couple of hours. Perhaps this is because rollback in a database
> that is not in use yet is a lot quicker than rolling back a busy database.
> However, in the case where you cannot kill a process and it is holding
> critical locks, is there another choice? If so, I would love to know how
> to resolve the problem without restarting the server.
> RLF
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e50Rz7JaIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Russel
>> Even if you restart the service, SQL Server attempts to recover the
>> database and in that case it will take long time.
>>
>>
>>
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:Ou5DpNEaIHA.4140@.TK2MSFTNGP04.phx.gbl...
>> anxcomp,
>> I agree that restarting the service should be the last resort.
>> Unfortunately, I do not know of another resort.
>> If the locks still being held do not block anyone, then you can wait to
>> schedule the restart, but if it is blocking users of the system then you
>> will need to schedule the restart soon. (Happened to me today on a
>> development server. Always frustrating, but very much so on a
>> production server.)
>> RLF
>> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
>> news:B6034F08-7EA9-47C5-A8F7-CD7B3BD38005@.microsoft.com...
>> Hi,
>> Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
>> #######################################################
>> spid
>> --
>> 72
>> 95
>> kpid
>> --
>> 512
>> 4784
>> lastwaittype
>> --
>> LCK_M_SCH_M
>> LCK_M_SCH_M
>>
>> waitresource
>> --
>> TAB: 2:1095871390:0
>> TAB: 2:1544549701:0
>> login_time
>> --
>> 2008-02-01 16:24:49.603
>> 2008-02-01 16:44:10.533
>> last_batch
>> --
>> 2008-02-01 16:24:50.590
>> 2008-02-01 16:44:10.797
>> open_tran
>> --
>> 1
>> 1
>> status
>> --
>> suspended
>> suspended
>> hostname
>> --
>> WWW1
>> WWW2
>> program_name
>> --
>> .Net SqlClient Data Provider
>> .Net SqlClient Data Provider
>> cmd
>> --
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> Kill:
>> SPID 72: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> SPID 95: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> kill 72 with statusonly and kill 95 with status only say the same
>> #######################################################
>> I read archive post on group and mainly people suggest restart sql
>> service.
>> I have this on production environment, so it isn't so easy to do.
>> Do you know any better way I think restart should be the last one.
>> --
>> Regards,
>> anxcomp
>>
>>
>|||Uri, Thanks for the additional note. - RLF
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%236ff8gMaIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Russel
> Yes, I had an epxerience where one person killed the long running
> transaction and later on did a restart m and finally database is gone due
> to recovering process
> You are right , there is only chance to restart the service, I just wanted
> to make apoint if you kill a long running transaction (DBCC REINDEX...)
> be careful to restart the service and I'm recommeding to wait till
> rollback will be completed
>
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OHiXLWMaIHA.5976@.TK2MSFTNGP05.phx.gbl...
>> Uri,
>> I know that can happen, but in actuality I have never had it take more
>> than a couple of minutes to recover even for a transaction that had been
>> running a couple of hours. Perhaps this is because rollback in a database
>> that is not in use yet is a lot quicker than rolling back a busy
>> database.
>> However, in the case where you cannot kill a process and it is holding
>> critical locks, is there another choice? If so, I would love to know how
>> to resolve the problem without restarting the server.
>> RLF
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:e50Rz7JaIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Russel
>> Even if you restart the service, SQL Server attempts to recover the
>> database and in that case it will take long time.
>>
>>
>>
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:Ou5DpNEaIHA.4140@.TK2MSFTNGP04.phx.gbl...
>> anxcomp,
>> I agree that restarting the service should be the last resort.
>> Unfortunately, I do not know of another resort.
>> If the locks still being held do not block anyone, then you can wait to
>> schedule the restart, but if it is blocking users of the system then
>> you will need to schedule the restart soon. (Happened to me today on a
>> development server. Always frustrating, but very much so on a
>> production server.)
>> RLF
>> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
>> news:B6034F08-7EA9-47C5-A8F7-CD7B3BD38005@.microsoft.com...
>> Hi,
>> Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
>> #######################################################
>> spid
>> --
>> 72
>> 95
>> kpid
>> --
>> 512
>> 4784
>> lastwaittype
>> --
>> LCK_M_SCH_M
>> LCK_M_SCH_M
>>
>> waitresource
>> --
>> TAB: 2:1095871390:0
>> TAB: 2:1544549701:0
>> login_time
>> --
>> 2008-02-01 16:24:49.603
>> 2008-02-01 16:44:10.533
>> last_batch
>> --
>> 2008-02-01 16:24:50.590
>> 2008-02-01 16:44:10.797
>> open_tran
>> --
>> 1
>> 1
>> status
>> --
>> suspended
>> suspended
>> hostname
>> --
>> WWW1
>> WWW2
>> program_name
>> --
>> .Net SqlClient Data Provider
>> .Net SqlClient Data Provider
>> cmd
>> --
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> Kill:
>> SPID 72: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> SPID 95: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> kill 72 with statusonly and kill 95 with status only say the same
>> #######################################################
>> I read archive post on group and mainly people suggest restart sql
>> service.
>> I have this on production environment, so it isn't so easy to do.
>> Do you know any better way I think restart should be the last one.
>> --
>> Regards,
>> anxcomp
>>
>>
>>
>|||Uri,
One additional comment: In the case described by anxcomp, I am unsure that
the rollback will ever complete. I kept one in that state open for a day
(and a long day it was, too) and it never rolled back and for the whole time
a
KILL spid WITH STATUSONLY:
reported the following:
Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
I have also seen a similar situation, but it reported:
Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.
FWIW,
RLF
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%236ff8gMaIHA.5208@.TK2MSFTNGP04.phx.gbl...
> Russel
> Yes, I had an epxerience where one person killed the long running
> transaction and later on did a restart m and finally database is gone due
> to recovering process
> You are right , there is only chance to restart the service, I just wanted
> to make apoint if you kill a long running transaction (DBCC REINDEX...)
> be careful to restart the service and I'm recommeding to wait till
> rollback will be completed
>
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:OHiXLWMaIHA.5976@.TK2MSFTNGP05.phx.gbl...
>> Uri,
>> I know that can happen, but in actuality I have never had it take more
>> than a couple of minutes to recover even for a transaction that had been
>> running a couple of hours. Perhaps this is because rollback in a database
>> that is not in use yet is a lot quicker than rolling back a busy
>> database.
>> However, in the case where you cannot kill a process and it is holding
>> critical locks, is there another choice? If so, I would love to know how
>> to resolve the problem without restarting the server.
>> RLF
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:e50Rz7JaIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Russel
>> Even if you restart the service, SQL Server attempts to recover the
>> database and in that case it will take long time.
>>
>>
>>
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:Ou5DpNEaIHA.4140@.TK2MSFTNGP04.phx.gbl...
>> anxcomp,
>> I agree that restarting the service should be the last resort.
>> Unfortunately, I do not know of another resort.
>> If the locks still being held do not block anyone, then you can wait to
>> schedule the restart, but if it is blocking users of the system then
>> you will need to schedule the restart soon. (Happened to me today on a
>> development server. Always frustrating, but very much so on a
>> production server.)
>> RLF
>> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
>> news:B6034F08-7EA9-47C5-A8F7-CD7B3BD38005@.microsoft.com...
>> Hi,
>> Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
>> #######################################################
>> spid
>> --
>> 72
>> 95
>> kpid
>> --
>> 512
>> 4784
>> lastwaittype
>> --
>> LCK_M_SCH_M
>> LCK_M_SCH_M
>>
>> waitresource
>> --
>> TAB: 2:1095871390:0
>> TAB: 2:1544549701:0
>> login_time
>> --
>> 2008-02-01 16:24:49.603
>> 2008-02-01 16:44:10.533
>> last_batch
>> --
>> 2008-02-01 16:24:50.590
>> 2008-02-01 16:44:10.797
>> open_tran
>> --
>> 1
>> 1
>> status
>> --
>> suspended
>> suspended
>> hostname
>> --
>> WWW1
>> WWW2
>> program_name
>> --
>> .Net SqlClient Data Provider
>> .Net SqlClient Data Provider
>> cmd
>> --
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> Kill:
>> SPID 72: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> SPID 95: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> kill 72 with statusonly and kill 95 with status only say the same
>> #######################################################
>> I read archive post on group and mainly people suggest restart sql
>> service.
>> I have this on production environment, so it isn't so easy to do.
>> Do you know any better way I think restart should be the last one.
>> --
>> Regards,
>> anxcomp
>>
>>
>>
>|||>I kept one in that state open for a day (and a long day it was, too) and it
>never rolled back and for the whole time
So what does it do? Is it just killed without rollback the process?
Fortunately or unfortunately :-) I did not happen to use KILL command in a
production/development that is on SQL Server 2005 , moreover I have never
used WITH STATUSONLY oprion
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23tdvtINaIHA.1168@.TK2MSFTNGP02.phx.gbl...
> Uri,
> One additional comment: In the case described by anxcomp, I am unsure that
> the rollback will ever complete. I kept one in that state open for a day
> (and a long day it was, too) and it never rolled back and for the whole
> time a
> KILL spid WITH STATUSONLY:
> reported the following:
> Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
> I have also seen a similar situation, but it reported:
> Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.
> FWIW,
> RLF
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%236ff8gMaIHA.5208@.TK2MSFTNGP04.phx.gbl...
>> Russel
>> Yes, I had an epxerience where one person killed the long running
>> transaction and later on did a restart m and finally database is gone due
>> to recovering process
>> You are right , there is only chance to restart the service, I just
>> wanted to make apoint if you kill a long running transaction (DBCC
>> REINDEX...) be careful to restart the service and I'm recommeding to wait
>> till rollback will be completed
>>
>>
>>
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:OHiXLWMaIHA.5976@.TK2MSFTNGP05.phx.gbl...
>> Uri,
>> I know that can happen, but in actuality I have never had it take more
>> than a couple of minutes to recover even for a transaction that had been
>> running a couple of hours. Perhaps this is because rollback in a
>> database that is not in use yet is a lot quicker than rolling back a
>> busy database.
>> However, in the case where you cannot kill a process and it is holding
>> critical locks, is there another choice? If so, I would love to know
>> how to resolve the problem without restarting the server.
>> RLF
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:e50Rz7JaIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Russel
>> Even if you restart the service, SQL Server attempts to recover the
>> database and in that case it will take long time.
>>
>>
>>
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:Ou5DpNEaIHA.4140@.TK2MSFTNGP04.phx.gbl...
>> anxcomp,
>> I agree that restarting the service should be the last resort.
>> Unfortunately, I do not know of another resort.
>> If the locks still being held do not block anyone, then you can wait
>> to schedule the restart, but if it is blocking users of the system
>> then you will need to schedule the restart soon. (Happened to me
>> today on a development server. Always frustrating, but very much so
>> on a production server.)
>> RLF
>> "anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
>> news:B6034F08-7EA9-47C5-A8F7-CD7B3BD38005@.microsoft.com...
>> Hi,
>> Two processes on my SQL 2005 server stay with status KILLED/ROLLBACK:
>> #######################################################
>> spid
>> --
>> 72
>> 95
>> kpid
>> --
>> 512
>> 4784
>> lastwaittype
>> --
>> LCK_M_SCH_M
>> LCK_M_SCH_M
>>
>> waitresource
>> --
>> TAB: 2:1095871390:0
>> TAB: 2:1544549701:0
>> login_time
>> --
>> 2008-02-01 16:24:49.603
>> 2008-02-01 16:44:10.533
>> last_batch
>> --
>> 2008-02-01 16:24:50.590
>> 2008-02-01 16:44:10.797
>> open_tran
>> --
>> 1
>> 1
>> status
>> --
>> suspended
>> suspended
>> hostname
>> --
>> WWW1
>> WWW2
>> program_name
>> --
>> .Net SqlClient Data Provider
>> .Net SqlClient Data Provider
>> cmd
>> --
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> KILLED/ROLLBACK (before kill was EXECUTE)
>> Kill:
>> SPID 72: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> SPID 95: transaction rollback in progress. Estimated rollback
>> completion:
>> 0%. Estimated time remaining: 0 seconds.
>> kill 72 with statusonly and kill 95 with status only say the same
>> #######################################################
>> I read archive post on group and mainly people suggest restart sql
>> service.
>> I have this on production environment, so it isn't so easy to do.
>> Do you know any better way I think restart should be the last one.
>> --
>> Regards,
>> anxcomp
>>
>>
>>
>>
>|||Uri,
> So what does it do? Is it just killed without rollback the process?
My window into what is really going on is only the tools that SQL Server
provides. Did it rollback at all? Maybe, but it claims it has not. Did it
really make any significant changes? No idea. Did it hold blocking locks?
Yes, preventing some application functions from working. Et cetera.
It _claims_ that it has entered the rollback state, but has rolled nothing
back. :-(
(I hope that anxcomp is finding this interesting.)
RLF|||I've tried switch database to simple or offline mode for a moment, but
without success.
#############################
alter database db1
set single_user
with
rollback immediate
alter database db1
set offline
with
rollback immediate
#############################
It only remains for me to restart :-(
Hope database restore will not take long time.
--
Regards,
anxcomp|||anxcomp,
When you say "database restore" I assume that you mean the automatic
"database recovery" that happens at startup. I reviewed the logs of the
server I had to do this on. (And, yes, I do hate being pushed into taking
that action.)
The SQL Server did some automatic DBCC CHECKDB and other recovery work. For
the big, active database at the root of the problem at recovery time it
said.
Recovery of database "BigDatabase" (111) is 0% complete (approximately
4826 seconds remain)
That would have been about 80 minutes, but it actually recovered in 29
seconds with 1 transaction rolling back.
For what that is worth,
RLF
"anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
news:4EDF93EA-C416-404A-8B20-0D8A982A9944@.microsoft.com...
> I've tried switch database to simple or offline mode for a moment, but
> without success.
> #############################
> alter database db1
> set single_user
> with
> rollback immediate
> alter database db1
> set offline
> with
> rollback immediate
> #############################
> It only remains for me to restart :-(
> Hope database restore will not take long time.
> --
> Regards,
> anxcomp|||Hi,
I've restarted today morning SQL Service. Fortunately SQL started without
any problem (about 30 seconds). ActiveMonitor doesn't show any problematic
processes :-)
--
Regards,
anxcomp
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment