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
Showing posts with label stay. Show all posts
Showing posts with label stay. Show all posts
Monday, March 26, 2012
KILLED/ROLLBACK - LCK_M_SCH_M - Estimated rollback completion: 0%
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
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
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...
>
|||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...
>
|||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...
>
|||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,
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...
>
|||>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...
>
|||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
sql
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
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
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...
>
|||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...
>
|||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...
>
|||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,
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...
>
|||>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...
>
|||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
sql
Wednesday, March 7, 2012
Keep TableRow Together
I have a table with 3 tablerows. This 3 tablerows needs to stay together on the same page for each record. Sometime, a function set the Hidden property of one of this tablerow to true, so I cannot just fit the table to get a multiple number of 3.
I use visual studio 2003 to create this rdl.
Thanks,
Marco
Can you restructure your report to have a single table row with a rectangle containing the current three rows of data? By default, SSRS has an implicit keeptogether that attempts to keep containers together when rendering them to a paged format. You can also see books on line for additional pointers:
"Rendering Considerations for Automatic Sizing and Positioning" http://technet.microsoft.com/en-us/library/aa337175.aspx
Larry
|||I will try and i'll let you know!
Thanks,
Marco
|||Hey Marco,
Did you fix your problem? I have an identical one and I would like to know if you could solved. Please share your solution with me, it will be very much appreciated.
Ioan Cozma
|||I'll test that tonight or tomorow. Si I will notice you as soon as possible.
Best regards,
Marco
|||Hi,
Sorry to be long...
So, it don't resolve completly my problem. For now, all lines are grouped, so this part is ok.
I have 5 lines, each have "Can decrease to accommodate contents" checked. So when Hidden property is set to True, there is no blank line.
But, the tablerow seem to have a static Height and no "Can decrease to accommodate contents". What could I do to get one or to simulate one?
Thanks,
Marco
|||Have you thought about using a list rather than a table? The table and matrix controls are just specalized forms of lists. A list would give you the ability to do what you want. I have confirmed that by aligning text boxes to the top of the list and others below, the resulting report will not skip lines for hidden text boxes. Make sure to align the left and right edges as if the text boxes were within a table.
Larry
Keep TableRow Together
I have a table with 3 tablerows. This 3 tablerows needs to stay together on the same page for each record. Sometime, a function set the Hidden property of one of this tablerow to true, so I cannot just fit the table to get a multiple number of 3.
I use visual studio 2003 to create this rdl.
Thanks,
Marco
Can you restructure your report to have a single table row with a rectangle containing the current three rows of data? By default, SSRS has an implicit keeptogether that attempts to keep containers together when rendering them to a paged format. You can also see books on line for additional pointers:
"Rendering Considerations for Automatic Sizing and Positioning" http://technet.microsoft.com/en-us/library/aa337175.aspx
Larry
|||I will try and i'll let you know!
Thanks,
Marco
|||Hey Marco,
Did you fix your problem? I have an identical one and I would like to know if you could solved. Please share your solution with me, it will be very much appreciated.
Ioan Cozma
|||I'll test that tonight or tomorow. Si I will notice you as soon as possible.
Best regards,
Marco
|||Hi,
Sorry to be long...
So, it don't resolve completly my problem. For now, all lines are grouped, so this part is ok.
I have 5 lines, each have "Can decrease to accommodate contents" checked. So when Hidden property is set to True, there is no blank line.
But, the tablerow seem to have a static Height and no "Can decrease to accommodate contents". What could I do to get one or to simulate one?
Thanks,
Marco
|||Have you thought about using a list rather than a table? The table and matrix controls are just specalized forms of lists. A list would give you the ability to do what you want. I have confirmed that by aligning text boxes to the top of the list and others below, the resulting report will not skip lines for hidden text boxes. Make sure to align the left and right edges as if the text boxes were within a table.
Larry
Friday, February 24, 2012
Keep sp in cache
hi, I wonder if anyone knows if it is possible to specify that a specific
stored procedure always should stay in cache or for example set that i
specific sp has "high cache priority"?
Regards
Juaninho
No. You can be sneaky about it though and have a default parameter that is
used as a flag to return immediately. Then you can set up a sql agent job
to fire every so often (minutes/hours?) with that flag set, which will keep
the plan in cache. This could lead to poor query plans if the OTHER
parameters for the sproc (if any) are not typical.
TheSQLGuru
President
Indicium Resources, Inc.
"Juaninho" <Juaninho@.discussions.microsoft.com> wrote in message
news:29BC33AD-8DC2-462A-89F0-B984366895B7@.microsoft.com...
> hi, I wonder if anyone knows if it is possible to specify that a specific
> stored procedure always should stay in cache or for example set that i
> specific sp has "high cache priority"?
> Regards
> Juaninho
|||On Apr 24, 5:41 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> No. You can be sneaky about it though and have a default parameter that is
> used as a flag to return immediately. Then you can set up a sql agent job
> to fire every so often (minutes/hours?) with that flag set, which will keep
> the plan in cache. This could lead to poor query plans if the OTHER
> parameters for the sproc (if any) are not typical.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Juaninho" <Juani...@.discussions.microsoft.com> wrote in message
> news:29BC33AD-8DC2-462A-89F0-B984366895B7@.microsoft.com...
>
>
> - Show quoted text -
If you are using SQL Server 2005 Look at USE PLAN, KEEPFIXED PLAN in
BOL
Regards
Amish Shah
http://shahamishm.tripod.com
stored procedure always should stay in cache or for example set that i
specific sp has "high cache priority"?
Regards
Juaninho
No. You can be sneaky about it though and have a default parameter that is
used as a flag to return immediately. Then you can set up a sql agent job
to fire every so often (minutes/hours?) with that flag set, which will keep
the plan in cache. This could lead to poor query plans if the OTHER
parameters for the sproc (if any) are not typical.
TheSQLGuru
President
Indicium Resources, Inc.
"Juaninho" <Juaninho@.discussions.microsoft.com> wrote in message
news:29BC33AD-8DC2-462A-89F0-B984366895B7@.microsoft.com...
> hi, I wonder if anyone knows if it is possible to specify that a specific
> stored procedure always should stay in cache or for example set that i
> specific sp has "high cache priority"?
> Regards
> Juaninho
|||On Apr 24, 5:41 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> No. You can be sneaky about it though and have a default parameter that is
> used as a flag to return immediately. Then you can set up a sql agent job
> to fire every so often (minutes/hours?) with that flag set, which will keep
> the plan in cache. This could lead to poor query plans if the OTHER
> parameters for the sproc (if any) are not typical.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Juaninho" <Juani...@.discussions.microsoft.com> wrote in message
> news:29BC33AD-8DC2-462A-89F0-B984366895B7@.microsoft.com...
>
>
> - Show quoted text -
If you are using SQL Server 2005 Look at USE PLAN, KEEPFIXED PLAN in
BOL
Regards
Amish Shah
http://shahamishm.tripod.com
Keep sp in cache
hi, I wonder if anyone knows if it is possible to specify that a specific
stored procedure always should stay in cache or for example set that i
specific sp has "high cache priority"?
Regards
JuaninhoNo. You can be sneaky about it though and have a default parameter that is
used as a flag to return immediately. Then you can set up a sql agent job
to fire every so often (minutes/hours') with that flag set, which will keep
the plan in cache. This could lead to poor query plans if the OTHER
parameters for the sproc (if any) are not typical.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Juaninho" <Juaninho@.discussions.microsoft.com> wrote in message
news:29BC33AD-8DC2-462A-89F0-B984366895B7@.microsoft.com...
> hi, I wonder if anyone knows if it is possible to specify that a specific
> stored procedure always should stay in cache or for example set that i
> specific sp has "high cache priority"?
> Regards
> Juaninho|||On Apr 24, 5:41 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> No. You can be sneaky about it though and have a default parameter that is
> used as a flag to return immediately. Then you can set up a sql agent job
> to fire every so often (minutes/hours') with that flag set, which will keep
> the plan in cache. This could lead to poor query plans if the OTHER
> parameters for the sproc (if any) are not typical.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Juaninho" <Juani...@.discussions.microsoft.com> wrote in message
> news:29BC33AD-8DC2-462A-89F0-B984366895B7@.microsoft.com...
>
> > hi, I wonder if anyone knows if it is possible to specify that a specific
> > stored procedure always should stay in cache or for example set that i
> > specific sp has "high cache priority"?
> > Regards
> > Juaninho- Hide quoted text -
> - Show quoted text -
If you are using SQL Server 2005 Look at USE PLAN, KEEPFIXED PLAN in
BOL
Regards
Amish Shah
http://shahamishm.tripod.com
stored procedure always should stay in cache or for example set that i
specific sp has "high cache priority"?
Regards
JuaninhoNo. You can be sneaky about it though and have a default parameter that is
used as a flag to return immediately. Then you can set up a sql agent job
to fire every so often (minutes/hours') with that flag set, which will keep
the plan in cache. This could lead to poor query plans if the OTHER
parameters for the sproc (if any) are not typical.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Juaninho" <Juaninho@.discussions.microsoft.com> wrote in message
news:29BC33AD-8DC2-462A-89F0-B984366895B7@.microsoft.com...
> hi, I wonder if anyone knows if it is possible to specify that a specific
> stored procedure always should stay in cache or for example set that i
> specific sp has "high cache priority"?
> Regards
> Juaninho|||On Apr 24, 5:41 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> No. You can be sneaky about it though and have a default parameter that is
> used as a flag to return immediately. Then you can set up a sql agent job
> to fire every so often (minutes/hours') with that flag set, which will keep
> the plan in cache. This could lead to poor query plans if the OTHER
> parameters for the sproc (if any) are not typical.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Juaninho" <Juani...@.discussions.microsoft.com> wrote in message
> news:29BC33AD-8DC2-462A-89F0-B984366895B7@.microsoft.com...
>
> > hi, I wonder if anyone knows if it is possible to specify that a specific
> > stored procedure always should stay in cache or for example set that i
> > specific sp has "high cache priority"?
> > Regards
> > Juaninho- Hide quoted text -
> - Show quoted text -
If you are using SQL Server 2005 Look at USE PLAN, KEEPFIXED PLAN in
BOL
Regards
Amish Shah
http://shahamishm.tripod.com
Keep sp in cache
hi, I wonder if anyone knows if it is possible to specify that a specific
stored procedure always should stay in cache or for example set that i
specific sp has "high cache priority"?
Regards
JuaninhoNo. You can be sneaky about it though and have a default parameter that is
used as a flag to return immediately. Then you can set up a sql agent job
to fire every so often (minutes/hours') with that flag set, which will keep
the plan in cache. This could lead to poor query plans if the OTHER
parameters for the sproc (if any) are not typical.
TheSQLGuru
President
Indicium Resources, Inc.
"Juaninho" <Juaninho@.discussions.microsoft.com> wrote in message
news:29BC33AD-8DC2-462A-89F0-B984366895B7@.microsoft.com...
> hi, I wonder if anyone knows if it is possible to specify that a specific
> stored procedure always should stay in cache or for example set that i
> specific sp has "high cache priority"?
> Regards
> Juaninho|||On Apr 24, 2:00 am, Juaninho <Juani...@.discussions.microsoft.com>
wrote:
> hi, I wonder if anyone knows if it is possible to specify that a specific
> stored procedure always should stay in cache or for example set that i
> specific sp has "high cache priority"?
> Regards
> Juaninho
If you've not read the BOL yet, here is what it says. After attending
Kalen Delaney's presentation last week I was reading about it more. I
have not seen any way or technique to keep the stored proc in the
cache. What I understand is even the execution plan is eligible for
deallocation sql server does not deallocate it till the resources is
needed. But make sure you have a lot of memory and that sql server
can use all of it.
After an execution plan is generated, it stays in the procedure cache.
SQL Server 2000 ages old, unused plans out of the cache only when
space is needed. Each query plan and execution context has an
associated cost factor that indicates how expensive the structure is
to compile. These data structures also have an age field. Each time
the object is referenced by a connection, the age field is incremented
by the compilation cost factor. For example, if a query plan has a
cost factor of 8 and is referenced twice, its age becomes 16. The
lazywriter process periodically scans the list of objects in the
procedure cache. The lazywriter decrements the age field of each
object by 1 on each scan. The age of our sample query plan is
decremented to 0 after 16 scans of the procedure cache, unless another
user references the plan. The lazywriter process deallocates an object
if these conditions are met:
The memory manager requires memory and all available memory is
currently in use.
The age field for the object is 0.
The object is not currently referenced by a connection.
Because the age field is incremented each time an object is
referenced, frequently referenced objects do not have their age fields
decremented to 0 and are not aged from the cache. Objects infrequently
referenced are soon eligible for deallocation, but are not actually
deallocated unless memory is required for other objects.
Good day,
Bulent|||On Apr 24, 5:41 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> No. You can be sneaky about it though and have a default parameter that i
s
> used as a flag to return immediately. Then you can set up a sql agent job
> to fire every so often (minutes/hours') with that flag set, which will ke
ep
> the plan in cache. This could lead to poor query plans if the OTHER
> parameters for the sproc (if any) are not typical.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Juaninho" <Juani...@.discussions.microsoft.com> wrote in message
> news:29BC33AD-8DC2-462A-89F0-B984366895B7@.microsoft.com...
>
>
>
> - Show quoted text -
If you are using SQL Server 2005 Look at USE PLAN, KEEPFIXED PLAN in
BOL
Regards
Amish Shah
http://shahamishm.tripod.com
stored procedure always should stay in cache or for example set that i
specific sp has "high cache priority"?
Regards
JuaninhoNo. You can be sneaky about it though and have a default parameter that is
used as a flag to return immediately. Then you can set up a sql agent job
to fire every so often (minutes/hours') with that flag set, which will keep
the plan in cache. This could lead to poor query plans if the OTHER
parameters for the sproc (if any) are not typical.
TheSQLGuru
President
Indicium Resources, Inc.
"Juaninho" <Juaninho@.discussions.microsoft.com> wrote in message
news:29BC33AD-8DC2-462A-89F0-B984366895B7@.microsoft.com...
> hi, I wonder if anyone knows if it is possible to specify that a specific
> stored procedure always should stay in cache or for example set that i
> specific sp has "high cache priority"?
> Regards
> Juaninho|||On Apr 24, 2:00 am, Juaninho <Juani...@.discussions.microsoft.com>
wrote:
> hi, I wonder if anyone knows if it is possible to specify that a specific
> stored procedure always should stay in cache or for example set that i
> specific sp has "high cache priority"?
> Regards
> Juaninho
If you've not read the BOL yet, here is what it says. After attending
Kalen Delaney's presentation last week I was reading about it more. I
have not seen any way or technique to keep the stored proc in the
cache. What I understand is even the execution plan is eligible for
deallocation sql server does not deallocate it till the resources is
needed. But make sure you have a lot of memory and that sql server
can use all of it.
After an execution plan is generated, it stays in the procedure cache.
SQL Server 2000 ages old, unused plans out of the cache only when
space is needed. Each query plan and execution context has an
associated cost factor that indicates how expensive the structure is
to compile. These data structures also have an age field. Each time
the object is referenced by a connection, the age field is incremented
by the compilation cost factor. For example, if a query plan has a
cost factor of 8 and is referenced twice, its age becomes 16. The
lazywriter process periodically scans the list of objects in the
procedure cache. The lazywriter decrements the age field of each
object by 1 on each scan. The age of our sample query plan is
decremented to 0 after 16 scans of the procedure cache, unless another
user references the plan. The lazywriter process deallocates an object
if these conditions are met:
The memory manager requires memory and all available memory is
currently in use.
The age field for the object is 0.
The object is not currently referenced by a connection.
Because the age field is incremented each time an object is
referenced, frequently referenced objects do not have their age fields
decremented to 0 and are not aged from the cache. Objects infrequently
referenced are soon eligible for deallocation, but are not actually
deallocated unless memory is required for other objects.
Good day,
Bulent|||On Apr 24, 5:41 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> No. You can be sneaky about it though and have a default parameter that i
s
> used as a flag to return immediately. Then you can set up a sql agent job
> to fire every so often (minutes/hours') with that flag set, which will ke
ep
> the plan in cache. This could lead to poor query plans if the OTHER
> parameters for the sproc (if any) are not typical.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Juaninho" <Juani...@.discussions.microsoft.com> wrote in message
> news:29BC33AD-8DC2-462A-89F0-B984366895B7@.microsoft.com...
>
>
>
> - Show quoted text -
If you are using SQL Server 2005 Look at USE PLAN, KEEPFIXED PLAN in
BOL
Regards
Amish Shah
http://shahamishm.tripod.com
Subscribe to:
Posts (Atom)