I cannot seem to get my subscription to push. It's status is being
shown as "Never Started". How do I get this back in operation?
http://www.chuckchamblee.com/tcifiles/Image1.jpg
Can you check the owner of the job. If it is a domain user, please change to
sa and then see if you can manually start the job.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Showing posts with label status. Show all posts
Showing posts with label status. Show all posts
Sunday, March 25, 2012
Thursday, March 8, 2012
Cannot Kill Process stays in KILLED/ROLLBACK status
I've just try to kill a process running for almost 10days now. It is a differential backup on which the SQLServer apparently choked. After killing the process it's in KILLED/ROLLBACK for quite sometime when I try to get the status with KILL 57 WITH STATUSONLY I get the following result:
SPID 57: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.
So I would infer that the process is indeed finished but it stays there. How can I get rid of a process that has a 100% Rollback completion and an estimate time remaining of 0 seconds?
I'm moving this thread to DB Engine. They may be able to answer or point you in the right direction.|||Thanks Samuel, It's sometimes difficult to find the right forum. Although I didn't like it I saw no other way than to do a restart of the server (I had some other maintenance at hand as well). Obviously after a restart the session was gone and I did not see adverse effects that could specifically be pinned down to this aborted session. Hopefully we did not loose any data, which is hard to track as daily about a 150'000 new records are created on the various databases on this particular server.
|||I'm having the same problem. You could have just restarted the SQL Service without restarting the server. I had this problem once before and it happened again today. Restarting the service fixed the problem.|||Hi Guys,
if the process that is running is external to SQL server i.e. OSQL, sqlmaint, and if you do the KILL SPID on it, it might not finish the rollback.
To avoid the server restart of the SQL Service, do not run the KILL SPID within the SQL Server instead kill the process at the OS level and the SPID from within the SQL will disappear.
regards
Jag
|||Hi Guys,
I am facing the same problem.
My job corresponding to distribution agent failed. But its spid still exists with WaitType = EC and in Killed/Rollback status.
I dont see any distrib.exe on the OS side in the task mgr which i can kill. And because of this spid the Sql agent wont execute the distribution agent saying that there is already one instance of same running.
And i dont want to restart the service :(
Thanks
|||
Top post Jag
I just had this problem when running xp_cmdshell, I found cmd.exe under windows processes and stopped this and this cleared the SPID without restarting the SQL services.
Cannot Kill Process stays in KILLED/ROLLBACK status
I've just try to kill a process running for almost 10days now. It is a differential backup on which the SQLServer apparently choked. After killing the process it's in KILLED/ROLLBACK for quite sometime when I try to get the status with KILL 57 WITH STATUSONLY I get the following result:
SPID 57: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.
So I would infer that the process is indeed finished but it stays there. How can I get rid of a process that has a 100% Rollback completion and an estimate time remaining of 0 seconds?
I'm moving this thread to DB Engine. They may be able to answer or point you in the right direction.|||Thanks Samuel, It's sometimes difficult to find the right forum. Although I didn't like it I saw no other way than to do a restart of the server (I had some other maintenance at hand as well). Obviously after a restart the session was gone and I did not see adverse effects that could specifically be pinned down to this aborted session. Hopefully we did not loose any data, which is hard to track as daily about a 150'000 new records are created on the various databases on this particular server.
|||I'm having the same problem. You could have just restarted the SQL Service without restarting the server. I had this problem once before and it happened again today. Restarting the service fixed the problem.|||Hi Guys,
if the process that is running is external to SQL server i.e. OSQL, sqlmaint, and if you do the KILL SPID on it, it might not finish the rollback.
To avoid the server restart of the SQL Service, do not run the KILL SPID within the SQL Server instead kill the process at the OS level and the SPID from within the SQL will disappear.
regards
Jag
|||Hi Guys,
I am facing the same problem.
My job corresponding to distribution agent failed. But its spid still exists with WaitType = EC and in Killed/Rollback status.
I dont see any distrib.exe on the OS side in the task mgr which i can kill. And because of this spid the Sql agent wont execute the distribution agent saying that there is already one instance of same running.
And i dont want to restart the service :(
Thanks
|||Top post Jag
I just had this problem when running xp_cmdshell, I found cmd.exe under windows processes and stopped this and this cleared the SPID without restarting the SQL services.
Cannot Kill Process stays in KILLED/ROLLBACK status
I've just try to kill a process running for almost 10days now. It is a differential backup on which the SQLServer apparently choked. After killing the process it's in KILLED/ROLLBACK for quite sometime when I try to get the status with KILL 57 WITH STATUSONLY I get the following result:
SPID 57: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.
So I would infer that the process is indeed finished but it stays there. How can I get rid of a process that has a 100% Rollback completion and an estimate time remaining of 0 seconds?
I'm moving this thread to DB Engine. They may be able to answer or point you in the right direction.|||Thanks Samuel, It's sometimes difficult to find the right forum. Although I didn't like it I saw no other way than to do a restart of the server (I had some other maintenance at hand as well). Obviously after a restart the session was gone and I did not see adverse effects that could specifically be pinned down to this aborted session. Hopefully we did not loose any data, which is hard to track as daily about a 150'000 new records are created on the various databases on this particular server.
|||I'm having the same problem. You could have just restarted the SQL Service without restarting the server. I had this problem once before and it happened again today. Restarting the service fixed the problem.|||Hi Guys,
if the process that is running is external to SQL server i.e. OSQL, sqlmaint, and if you do the KILL SPID on it, it might not finish the rollback.
To avoid the server restart of the SQL Service, do not run the KILL SPID within the SQL Server instead kill the process at the OS level and the SPID from within the SQL will disappear.
regards
Jag
|||Hi Guys,
I am facing the same problem.
My job corresponding to distribution agent failed. But its spid still exists with WaitType = EC and in Killed/Rollback status.
I dont see any distrib.exe on the OS side in the task mgr which i can kill. And because of this spid the Sql agent wont execute the distribution agent saying that there is already one instance of same running.
And i dont want to restart the service :(
Thanks
|||Top post Jag
I just had this problem when running xp_cmdshell, I found cmd.exe under windows processes and stopped this and this cleared the SPID without restarting the SQL services.
Cannot Kill Process stays in KILLED/ROLLBACK status
I've just try to kill a process running for almost 10days now. It is a differential backup on which the SQLServer apparently choked. After killing the process it's in KILLED/ROLLBACK for quite sometime when I try to get the status with KILL 57 WITH STATUSONLY I get the following result:
SPID 57: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.
So I would infer that the process is indeed finished but it stays there. How can I get rid of a process that has a 100% Rollback completion and an estimate time remaining of 0 seconds?
I'm moving this thread to DB Engine. They may be able to answer or point you in the right direction.|||Thanks Samuel, It's sometimes difficult to find the right forum. Although I didn't like it I saw no other way than to do a restart of the server (I had some other maintenance at hand as well). Obviously after a restart the session was gone and I did not see adverse effects that could specifically be pinned down to this aborted session. Hopefully we did not loose any data, which is hard to track as daily about a 150'000 new records are created on the various databases on this particular server.
|||I'm having the same problem. You could have just restarted the SQL Service without restarting the server. I had this problem once before and it happened again today. Restarting the service fixed the problem.|||Hi Guys,
if the process that is running is external to SQL server i.e. OSQL, sqlmaint, and if you do the KILL SPID on it, it might not finish the rollback.
To avoid the server restart of the SQL Service, do not run the KILL SPID within the SQL Server instead kill the process at the OS level and the SPID from within the SQL will disappear.
regards
Jag
|||Hi Guys,
I am facing the same problem.
My job corresponding to distribution agent failed. But its spid still exists with WaitType = EC and in Killed/Rollback status.
I dont see any distrib.exe on the OS side in the task mgr which i can kill. And because of this spid the Sql agent wont execute the distribution agent saying that there is already one instance of same running.
And i dont want to restart the service :(
Thanks
|||Top post Jag
I just had this problem when running xp_cmdshell, I found cmd.exe under windows processes and stopped this and this cleared the SPID without restarting the SQL services.
Cannot Kill Process stays in KILLED/ROLLBACK status
I've just try to kill a process running for almost 10days now. It is a differential backup on which the SQLServer apparently choked. After killing the process it's in KILLED/ROLLBACK for quite sometime when I try to get the status with KILL 57 WITH STATUSONLY I get the following result:
SPID 57: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.
So I would infer that the process is indeed finished but it stays there. How can I get rid of a process that has a 100% Rollback completion and an estimate time remaining of 0 seconds?
I'm moving this thread to DB Engine. They may be able to answer or point you in the right direction.|||Thanks Samuel, It's sometimes difficult to find the right forum. Although I didn't like it I saw no other way than to do a restart of the server (I had some other maintenance at hand as well). Obviously after a restart the session was gone and I did not see adverse effects that could specifically be pinned down to this aborted session. Hopefully we did not loose any data, which is hard to track as daily about a 150'000 new records are created on the various databases on this particular server.
|||I'm having the same problem. You could have just restarted the SQL Service without restarting the server. I had this problem once before and it happened again today. Restarting the service fixed the problem.|||Hi Guys,
if the process that is running is external to SQL server i.e. OSQL, sqlmaint, and if you do the KILL SPID on it, it might not finish the rollback.
To avoid the server restart of the SQL Service, do not run the KILL SPID within the SQL Server instead kill the process at the OS level and the SPID from within the SQL will disappear.
regards
Jag
|||Hi Guys,
I am facing the same problem.
My job corresponding to distribution agent failed. But its spid still exists with WaitType = EC and in Killed/Rollback status.
I dont see any distrib.exe on the OS side in the task mgr which i can kill. And because of this spid the Sql agent wont execute the distribution agent saying that there is already one instance of same running.
And i dont want to restart the service :(
Thanks
|||
Top post Jag
I just had this problem when running xp_cmdshell, I found cmd.exe under windows processes and stopped this and this cleared the SPID without restarting the SQL services.
Cannot kill process
process with status runnable in MASTER db (waiting for lock shown in mmc).
as consequence, SQL Server agent is not starting anymore!
how can I kill this process?
master..sysobjects shows this process as non-blocked (blocked=0).
in tempdb the entry is shown with SPID #51 correponding to the running process
but it is not terminating since several days.
Thanks a lot in advance for help!Urban,
Try:
KILL <SPID> WITH STATUSONLY.
HTH
Jerry
"Urban" <urban@.nospamplease> wrote in message
news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running
> process
> but it is not terminating since several days.
> Thanks a lot in advance for help!
>|||process with status runnable in MASTER db (waiting for lock shown in mmc).
as consequence, SQL Server agent is not starting anymore!
how can I kill this process?
master..sysobjects shows this process as non-blocked (blocked=0).
in tempdb the entry is shown with SPID #51 correponding to the running process
but it is not terminating since several days.
SORRY, forgot to mention, that I tryed KILL 51, of course.
Thanks a lot in advance for help!|||Urban,
STATUSONLY will show you the estimated rollback completion. Is this a test
box, you could try stopping the SQL Server service and then starting the SQL
Server service then the SQL Server Agent service. Any errors reported in
either the SQL logs or the app event logs?
HTH
Jerry
"Urban" <urban@.nospamplease> wrote in message
news:CDE764AB-E274-4480-A61B-3A31C9BC0426@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running
> process
> but it is not terminating since several days.
> SORRY, forgot to mention, that I tryed KILL 51, of course.
>
> Thanks a lot in advance for help!
>|||Query analyser returns:
Status report cannot be obtained. Rollback operation for Process ID 51 is
not in progress.
Kill 51 is making the entries in tempd, but not killing the process.
"Jerry Spivey" wrote:
> Urban,
> Try:
> KILL <SPID> WITH STATUSONLY.
> HTH
> Jerry
> "Urban" <urban@.nospamplease> wrote in message
> news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
> > process with status runnable in MASTER db (waiting for lock shown in mmc).
> > as consequence, SQL Server agent is not starting anymore!
> > how can I kill this process?
> > master..sysobjects shows this process as non-blocked (blocked=0).
> > in tempdb the entry is shown with SPID #51 correponding to the running
> > process
> > but it is not terminating since several days.
> >
> > Thanks a lot in advance for help!
> >
>
>|||Restart tried several times, even restatred machine.
No errors reported, Just that Process 51 has been killed, but is a true lie;-)
the process is still alive!
"Jerry Spivey" wrote:
> Urban,
> STATUSONLY will show you the estimated rollback completion. Is this a test
> box, you could try stopping the SQL Server service and then starting the SQL
> Server service then the SQL Server Agent service. Any errors reported in
> either the SQL logs or the app event logs?
> HTH
> Jerry
> "Urban" <urban@.nospamplease> wrote in message
> news:CDE764AB-E274-4480-A61B-3A31C9BC0426@.microsoft.com...
> > process with status runnable in MASTER db (waiting for lock shown in mmc).
> > as consequence, SQL Server agent is not starting anymore!
> > how can I kill this process?
> > master..sysobjects shows this process as non-blocked (blocked=0).
> > in tempdb the entry is shown with SPID #51 correponding to the running
> > process
> > but it is not terminating since several days.
> > SORRY, forgot to mention, that I tryed KILL 51, of course.
> >
> >
> > Thanks a lot in advance for help!
> >
>
>|||How are you looking that this? EM, current activity? Perhaps this is your connection that EM is
using to show you the information?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Urban" <urban@.nospamplease> wrote in message
news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running process
> but it is not terminating since several days.
> Thanks a lot in advance for help!
>|||Thanks a lot! I'm ashamed, you're right!!!!
It is indeed my own process. I didn't recognize, because I used the run as
command for mmc with the same user that is running SQL-Server, and not my
common user.
I understand, that the prozess is not allowed to kill itself. - Strange,
that it was alive for more than 10 days.
Meanwile, SQL Server Agent was able to start, after about 10 days. This I
don't understand. But even after restarting the databse it works fine.
Propably a problem when patching the server.
Anyway, you have solved my Problem, Tibor. Thanks a lot!
"Tibor Karaszi" wrote:
> How are you looking that this? EM, current activity? Perhaps this is your connection that EM is
> using to show you the information?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Urban" <urban@.nospamplease> wrote in message
> news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
> > process with status runnable in MASTER db (waiting for lock shown in mmc).
> > as consequence, SQL Server agent is not starting anymore!
> > how can I kill this process?
> > master..sysobjects shows this process as non-blocked (blocked=0).
> > in tempdb the entry is shown with SPID #51 correponding to the running process
> > but it is not terminating since several days.
> >
> > Thanks a lot in advance for help!
> >
>
as consequence, SQL Server agent is not starting anymore!
how can I kill this process?
master..sysobjects shows this process as non-blocked (blocked=0).
in tempdb the entry is shown with SPID #51 correponding to the running process
but it is not terminating since several days.
Thanks a lot in advance for help!Urban,
Try:
KILL <SPID> WITH STATUSONLY.
HTH
Jerry
"Urban" <urban@.nospamplease> wrote in message
news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running
> process
> but it is not terminating since several days.
> Thanks a lot in advance for help!
>|||process with status runnable in MASTER db (waiting for lock shown in mmc).
as consequence, SQL Server agent is not starting anymore!
how can I kill this process?
master..sysobjects shows this process as non-blocked (blocked=0).
in tempdb the entry is shown with SPID #51 correponding to the running process
but it is not terminating since several days.
SORRY, forgot to mention, that I tryed KILL 51, of course.
Thanks a lot in advance for help!|||Urban,
STATUSONLY will show you the estimated rollback completion. Is this a test
box, you could try stopping the SQL Server service and then starting the SQL
Server service then the SQL Server Agent service. Any errors reported in
either the SQL logs or the app event logs?
HTH
Jerry
"Urban" <urban@.nospamplease> wrote in message
news:CDE764AB-E274-4480-A61B-3A31C9BC0426@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running
> process
> but it is not terminating since several days.
> SORRY, forgot to mention, that I tryed KILL 51, of course.
>
> Thanks a lot in advance for help!
>|||Query analyser returns:
Status report cannot be obtained. Rollback operation for Process ID 51 is
not in progress.
Kill 51 is making the entries in tempd, but not killing the process.
"Jerry Spivey" wrote:
> Urban,
> Try:
> KILL <SPID> WITH STATUSONLY.
> HTH
> Jerry
> "Urban" <urban@.nospamplease> wrote in message
> news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
> > process with status runnable in MASTER db (waiting for lock shown in mmc).
> > as consequence, SQL Server agent is not starting anymore!
> > how can I kill this process?
> > master..sysobjects shows this process as non-blocked (blocked=0).
> > in tempdb the entry is shown with SPID #51 correponding to the running
> > process
> > but it is not terminating since several days.
> >
> > Thanks a lot in advance for help!
> >
>
>|||Restart tried several times, even restatred machine.
No errors reported, Just that Process 51 has been killed, but is a true lie;-)
the process is still alive!
"Jerry Spivey" wrote:
> Urban,
> STATUSONLY will show you the estimated rollback completion. Is this a test
> box, you could try stopping the SQL Server service and then starting the SQL
> Server service then the SQL Server Agent service. Any errors reported in
> either the SQL logs or the app event logs?
> HTH
> Jerry
> "Urban" <urban@.nospamplease> wrote in message
> news:CDE764AB-E274-4480-A61B-3A31C9BC0426@.microsoft.com...
> > process with status runnable in MASTER db (waiting for lock shown in mmc).
> > as consequence, SQL Server agent is not starting anymore!
> > how can I kill this process?
> > master..sysobjects shows this process as non-blocked (blocked=0).
> > in tempdb the entry is shown with SPID #51 correponding to the running
> > process
> > but it is not terminating since several days.
> > SORRY, forgot to mention, that I tryed KILL 51, of course.
> >
> >
> > Thanks a lot in advance for help!
> >
>
>|||How are you looking that this? EM, current activity? Perhaps this is your connection that EM is
using to show you the information?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Urban" <urban@.nospamplease> wrote in message
news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running process
> but it is not terminating since several days.
> Thanks a lot in advance for help!
>|||Thanks a lot! I'm ashamed, you're right!!!!
It is indeed my own process. I didn't recognize, because I used the run as
command for mmc with the same user that is running SQL-Server, and not my
common user.
I understand, that the prozess is not allowed to kill itself. - Strange,
that it was alive for more than 10 days.
Meanwile, SQL Server Agent was able to start, after about 10 days. This I
don't understand. But even after restarting the databse it works fine.
Propably a problem when patching the server.
Anyway, you have solved my Problem, Tibor. Thanks a lot!
"Tibor Karaszi" wrote:
> How are you looking that this? EM, current activity? Perhaps this is your connection that EM is
> using to show you the information?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Urban" <urban@.nospamplease> wrote in message
> news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
> > process with status runnable in MASTER db (waiting for lock shown in mmc).
> > as consequence, SQL Server agent is not starting anymore!
> > how can I kill this process?
> > master..sysobjects shows this process as non-blocked (blocked=0).
> > in tempdb the entry is shown with SPID #51 correponding to the running process
> > but it is not terminating since several days.
> >
> > Thanks a lot in advance for help!
> >
>
Cannot kill process
process with status runnable in MASTER db (waiting for lock shown in mmc).
as consequence, SQL Server agent is not starting anymore!
how can I kill this process?
master..sysobjects shows this process as non-blocked (blocked=0).
in tempdb the entry is shown with SPID #51 correponding to the running process
but it is not terminating since several days.
Thanks a lot in advance for help!
Urban,
Try:
KILL <SPID> WITH STATUSONLY.
HTH
Jerry
"Urban" <urban@.nospamplease> wrote in message
news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running
> process
> but it is not terminating since several days.
> Thanks a lot in advance for help!
>
|||process with status runnable in MASTER db (waiting for lock shown in mmc).
as consequence, SQL Server agent is not starting anymore!
how can I kill this process?
master..sysobjects shows this process as non-blocked (blocked=0).
in tempdb the entry is shown with SPID #51 correponding to the running process
but it is not terminating since several days.
SORRY, forgot to mention, that I tryed KILL 51, of course.
Thanks a lot in advance for help!
|||Urban,
STATUSONLY will show you the estimated rollback completion. Is this a test
box, you could try stopping the SQL Server service and then starting the SQL
Server service then the SQL Server Agent service. Any errors reported in
either the SQL logs or the app event logs?
HTH
Jerry
"Urban" <urban@.nospamplease> wrote in message
news:CDE764AB-E274-4480-A61B-3A31C9BC0426@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running
> process
> but it is not terminating since several days.
> SORRY, forgot to mention, that I tryed KILL 51, of course.
>
> Thanks a lot in advance for help!
>
|||Query analyser returns:
Status report cannot be obtained. Rollback operation for Process ID 51 is
not in progress.
Kill 51 is making the entries in tempd, but not killing the process.
"Jerry Spivey" wrote:
> Urban,
> Try:
> KILL <SPID> WITH STATUSONLY.
> HTH
> Jerry
> "Urban" <urban@.nospamplease> wrote in message
> news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
>
>
|||Restart tried several times, even restatred machine.
No errors reported, Just that Process 51 has been killed, but is a true lie;-)
the process is still alive!
"Jerry Spivey" wrote:
> Urban,
> STATUSONLY will show you the estimated rollback completion. Is this a test
> box, you could try stopping the SQL Server service and then starting the SQL
> Server service then the SQL Server Agent service. Any errors reported in
> either the SQL logs or the app event logs?
> HTH
> Jerry
> "Urban" <urban@.nospamplease> wrote in message
> news:CDE764AB-E274-4480-A61B-3A31C9BC0426@.microsoft.com...
>
>
|||How are you looking that this? EM, current activity? Perhaps this is your connection that EM is
using to show you the information?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Urban" <urban@.nospamplease> wrote in message
news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running process
> but it is not terminating since several days.
> Thanks a lot in advance for help!
>
|||Thanks a lot! I'm ashamed, you're right!!!!
It is indeed my own process. I didn't recognize, because I used the run as
command for mmc with the same user that is running SQL-Server, and not my
common user.
I understand, that the prozess is not allowed to kill itself. - Strange,
that it was alive for more than 10 days.
Meanwile, SQL Server Agent was able to start, after about 10 days. This I
don't understand. But even after restarting the databse it works fine.
Propably a problem when patching the server.
Anyway, you have solved my Problem, Tibor. Thanks a lot!
"Tibor Karaszi" wrote:
> How are you looking that this? EM, current activity? Perhaps this is your connection that EM is
> using to show you the information?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Urban" <urban@.nospamplease> wrote in message
> news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
>
as consequence, SQL Server agent is not starting anymore!
how can I kill this process?
master..sysobjects shows this process as non-blocked (blocked=0).
in tempdb the entry is shown with SPID #51 correponding to the running process
but it is not terminating since several days.
Thanks a lot in advance for help!
Urban,
Try:
KILL <SPID> WITH STATUSONLY.
HTH
Jerry
"Urban" <urban@.nospamplease> wrote in message
news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running
> process
> but it is not terminating since several days.
> Thanks a lot in advance for help!
>
|||process with status runnable in MASTER db (waiting for lock shown in mmc).
as consequence, SQL Server agent is not starting anymore!
how can I kill this process?
master..sysobjects shows this process as non-blocked (blocked=0).
in tempdb the entry is shown with SPID #51 correponding to the running process
but it is not terminating since several days.
SORRY, forgot to mention, that I tryed KILL 51, of course.
Thanks a lot in advance for help!
|||Urban,
STATUSONLY will show you the estimated rollback completion. Is this a test
box, you could try stopping the SQL Server service and then starting the SQL
Server service then the SQL Server Agent service. Any errors reported in
either the SQL logs or the app event logs?
HTH
Jerry
"Urban" <urban@.nospamplease> wrote in message
news:CDE764AB-E274-4480-A61B-3A31C9BC0426@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running
> process
> but it is not terminating since several days.
> SORRY, forgot to mention, that I tryed KILL 51, of course.
>
> Thanks a lot in advance for help!
>
|||Query analyser returns:
Status report cannot be obtained. Rollback operation for Process ID 51 is
not in progress.
Kill 51 is making the entries in tempd, but not killing the process.
"Jerry Spivey" wrote:
> Urban,
> Try:
> KILL <SPID> WITH STATUSONLY.
> HTH
> Jerry
> "Urban" <urban@.nospamplease> wrote in message
> news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
>
>
|||Restart tried several times, even restatred machine.
No errors reported, Just that Process 51 has been killed, but is a true lie;-)
the process is still alive!
"Jerry Spivey" wrote:
> Urban,
> STATUSONLY will show you the estimated rollback completion. Is this a test
> box, you could try stopping the SQL Server service and then starting the SQL
> Server service then the SQL Server Agent service. Any errors reported in
> either the SQL logs or the app event logs?
> HTH
> Jerry
> "Urban" <urban@.nospamplease> wrote in message
> news:CDE764AB-E274-4480-A61B-3A31C9BC0426@.microsoft.com...
>
>
|||How are you looking that this? EM, current activity? Perhaps this is your connection that EM is
using to show you the information?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Urban" <urban@.nospamplease> wrote in message
news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running process
> but it is not terminating since several days.
> Thanks a lot in advance for help!
>
|||Thanks a lot! I'm ashamed, you're right!!!!
It is indeed my own process. I didn't recognize, because I used the run as
command for mmc with the same user that is running SQL-Server, and not my
common user.
I understand, that the prozess is not allowed to kill itself. - Strange,
that it was alive for more than 10 days.
Meanwile, SQL Server Agent was able to start, after about 10 days. This I
don't understand. But even after restarting the databse it works fine.
Propably a problem when patching the server.
Anyway, you have solved my Problem, Tibor. Thanks a lot!
"Tibor Karaszi" wrote:
> How are you looking that this? EM, current activity? Perhaps this is your connection that EM is
> using to show you the information?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Urban" <urban@.nospamplease> wrote in message
> news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
>
Cannot kill process
process with status runnable in MASTER db (waiting for lock shown in mmc).
as consequence, SQL Server agent is not starting anymore!
how can I kill this process?
master..sysobjects shows this process as non-blocked (blocked=0).
in tempdb the entry is shown with SPID #51 correponding to the running proce
ss
but it is not terminating since several days.
Thanks a lot in advance for help!Urban,
Try:
KILL <SPID> WITH STATUSONLY.
HTH
Jerry
"Urban" <urban@.nospamplease> wrote in message
news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running
> process
> but it is not terminating since several days.
> Thanks a lot in advance for help!
>|||process with status runnable in MASTER db (waiting for lock shown in mmc).
as consequence, SQL Server agent is not starting anymore!
how can I kill this process?
master..sysobjects shows this process as non-blocked (blocked=0).
in tempdb the entry is shown with SPID #51 correponding to the running proce
ss
but it is not terminating since several days.
SORRY, forgot to mention, that I tryed KILL 51, of course.
Thanks a lot in advance for help!|||Urban,
STATUSONLY will show you the estimated rollback completion. Is this a test
box, you could try stopping the SQL Server service and then starting the SQL
Server service then the SQL Server Agent service. Any errors reported in
either the SQL logs or the app event logs?
HTH
Jerry
"Urban" <urban@.nospamplease> wrote in message
news:CDE764AB-E274-4480-A61B-3A31C9BC0426@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running
> process
> but it is not terminating since several days.
> SORRY, forgot to mention, that I tryed KILL 51, of course.
>
> Thanks a lot in advance for help!
>|||Query analyser returns:
Status report cannot be obtained. Rollback operation for Process ID 51 is
not in progress.
Kill 51 is making the entries in tempd, but not killing the process.
"Jerry Spivey" wrote:
> Urban,
> Try:
> KILL <SPID> WITH STATUSONLY.
> HTH
> Jerry
> "Urban" <urban@.nospamplease> wrote in message
> news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
>
>|||Restart tried several times, even restatred machine.
No errors reported, Just that Process 51 has been killed, but is a true lie;
-)
the process is still alive!
"Jerry Spivey" wrote:
> Urban,
> STATUSONLY will show you the estimated rollback completion. Is this a tes
t
> box, you could try stopping the SQL Server service and then starting the S
QL
> Server service then the SQL Server Agent service. Any errors reported in
> either the SQL logs or the app event logs?
> HTH
> Jerry
> "Urban" <urban@.nospamplease> wrote in message
> news:CDE764AB-E274-4480-A61B-3A31C9BC0426@.microsoft.com...
>
>|||How are you looking that this? EM, current activity? Perhaps this is your co
nnection that EM is
using to show you the information?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Urban" <urban@.nospamplease> wrote in message
news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running pro
cess
> but it is not terminating since several days.
> Thanks a lot in advance for help!
>|||Thanks a lot! I'm ashamed, you're right!!!!
It is indeed my own process. I didn't recognize, because I used the run as
command for mmc with the same user that is running SQL-Server, and not my
common user.
I understand, that the prozess is not allowed to kill itself. - Strange,
that it was alive for more than 10 days.
Meanwile, SQL Server Agent was able to start, after about 10 days. This I
don't understand. But even after restarting the databse it works fine.
Propably a problem when patching the server.
Anyway, you have solved my Problem, Tibor. Thanks a lot!
"Tibor Karaszi" wrote:
> How are you looking that this? EM, current activity? Perhaps this is your
connection that EM is
> using to show you the information?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Urban" <urban@.nospamplease> wrote in message
> news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
>
as consequence, SQL Server agent is not starting anymore!
how can I kill this process?
master..sysobjects shows this process as non-blocked (blocked=0).
in tempdb the entry is shown with SPID #51 correponding to the running proce
ss
but it is not terminating since several days.
Thanks a lot in advance for help!Urban,
Try:
KILL <SPID> WITH STATUSONLY.
HTH
Jerry
"Urban" <urban@.nospamplease> wrote in message
news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running
> process
> but it is not terminating since several days.
> Thanks a lot in advance for help!
>|||process with status runnable in MASTER db (waiting for lock shown in mmc).
as consequence, SQL Server agent is not starting anymore!
how can I kill this process?
master..sysobjects shows this process as non-blocked (blocked=0).
in tempdb the entry is shown with SPID #51 correponding to the running proce
ss
but it is not terminating since several days.
SORRY, forgot to mention, that I tryed KILL 51, of course.
Thanks a lot in advance for help!|||Urban,
STATUSONLY will show you the estimated rollback completion. Is this a test
box, you could try stopping the SQL Server service and then starting the SQL
Server service then the SQL Server Agent service. Any errors reported in
either the SQL logs or the app event logs?
HTH
Jerry
"Urban" <urban@.nospamplease> wrote in message
news:CDE764AB-E274-4480-A61B-3A31C9BC0426@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running
> process
> but it is not terminating since several days.
> SORRY, forgot to mention, that I tryed KILL 51, of course.
>
> Thanks a lot in advance for help!
>|||Query analyser returns:
Status report cannot be obtained. Rollback operation for Process ID 51 is
not in progress.
Kill 51 is making the entries in tempd, but not killing the process.
"Jerry Spivey" wrote:
> Urban,
> Try:
> KILL <SPID> WITH STATUSONLY.
> HTH
> Jerry
> "Urban" <urban@.nospamplease> wrote in message
> news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
>
>|||Restart tried several times, even restatred machine.
No errors reported, Just that Process 51 has been killed, but is a true lie;
-)
the process is still alive!
"Jerry Spivey" wrote:
> Urban,
> STATUSONLY will show you the estimated rollback completion. Is this a tes
t
> box, you could try stopping the SQL Server service and then starting the S
QL
> Server service then the SQL Server Agent service. Any errors reported in
> either the SQL logs or the app event logs?
> HTH
> Jerry
> "Urban" <urban@.nospamplease> wrote in message
> news:CDE764AB-E274-4480-A61B-3A31C9BC0426@.microsoft.com...
>
>|||How are you looking that this? EM, current activity? Perhaps this is your co
nnection that EM is
using to show you the information?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Urban" <urban@.nospamplease> wrote in message
news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running pro
cess
> but it is not terminating since several days.
> Thanks a lot in advance for help!
>|||Thanks a lot! I'm ashamed, you're right!!!!
It is indeed my own process. I didn't recognize, because I used the run as
command for mmc with the same user that is running SQL-Server, and not my
common user.
I understand, that the prozess is not allowed to kill itself. - Strange,
that it was alive for more than 10 days.
Meanwile, SQL Server Agent was able to start, after about 10 days. This I
don't understand. But even after restarting the databse it works fine.
Propably a problem when patching the server.
Anyway, you have solved my Problem, Tibor. Thanks a lot!
"Tibor Karaszi" wrote:
> How are you looking that this? EM, current activity? Perhaps this is your
connection that EM is
> using to show you the information?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Urban" <urban@.nospamplease> wrote in message
> news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
>
Thursday, February 16, 2012
Cannot get Server Status using DMO on SQL 2005 : Access Denied
Hi everybody,
I'm facing a small problem using SQL-DMO in a vbs script. This script was written for SQL 2000 and 2005 so using SMO is not really an option for me.
In SQL 2000 and SQL 2005 SP1, it looks like I have no problem doing the following from a client computer:
Dim oServer
Const cServerName = "(local)"
Const cUserName = "sa"
Const cPassword = "sa"
Set oServer = CreateObject("SQLDmo.SqlServer")
oServer.Name = cServerName
MsgBox "Status (before connection): " & oServer.Status
note that this happens *before* I connect to the server. This is because I want to start the service if is not started or wait until it is started if it is currently starting.
In SQL 2005 SP2, I get the following "Service Control Error: Access Denied", no matter what user I use...
I can only query the server's status *after* I am connected, which well, does not do much for me in that particular case ;-).
I was wondering if anybody had encountered the same problem before and if so, how they went around it. I have to confirm this, but doing this from the server itself looks like it is working... Could it then be due to a mismatch between client and server service packs or sql server backward compatibility package being required on both client and server?
Thanks,
Greg
Well, in case anybody's interested, here's what I found that proved to be the problem. It's a 2000 sp4/2003 sp1 problem. The ACL has changed and remote queries without prior connection are not allowed by default anymore. There is a fix to allow them by changing the ACL to give the required rights. In my case the fix is to try to connect anyways and if the server is not up then it will simply fail since many companies will simply not allow to do such things to get the previous behavior back 
kb Artice #: 907460
Greg
Cannot get Server Status using DMO on SQL 2005 : Access Denied
Hi everybody,
I'm facing a small problem using SQL-DMO in a vbs script. This script was written for SQL 2000 and 2005 so using SMO is not really an option for me.
In SQL 2000 and SQL 2005 SP1, it looks like I have no problem doing the following from a client computer:
Dim oServer
Const cServerName = "(local)"
Const cUserName = "sa"
Const cPassword = "sa"
Set oServer = CreateObject("SQLDmo.SqlServer")
oServer.Name = cServerName
MsgBox "Status (before connection): " & oServer.Status
note that this happens *before* I connect to the server. This is because I want to start the service if is not started or wait until it is started if it is currently starting.
In SQL 2005 SP2, I get the following "Service Control Error: Access Denied", no matter what user I use...
I can only query the server's status *after* I am connected, which well, does not do much for me in that particular case ;-).
I was wondering if anybody had encountered the same problem before and if so, how they went around it. I have to confirm this, but doing this from the server itself looks like it is working... Could it then be due to a mismatch between client and server service packs or sql server backward compatibility package being required on both client and server?
Thanks,
Greg
Well, in case anybody's interested, here's what I found that proved to be the problem. It's a 2000 sp4/2003 sp1 problem. The ACL has changed and remote queries without prior connection are not allowed by default anymore. There is a fix to allow them by changing the ACL to give the required rights. In my case the fix is to try to connect anyways and if the server is not up then it will simply fail since many companies will simply not allow to do such things to get the previous behavior back
kb Artice #: 907460
Greg
Subscribe to:
Posts (Atom)