I logged in through query anayzer as system admin and then logged in a second session from a different user ID. From the sa window I issued "sp_who" and found the session id of 55 for the second logged in session. I then issued "KILL 55". When I reissued
"sp_who" it showed the session id was gone, but when I opened the user query analyzer window, I was still allowed to issue SELECT statements. Shouldnt the user session window close or leave some type of message to show that the session was logged out and
the window is no longer active?
> Shouldnt the user session window close or leave some type of message
> to show that the session was logged out and the window is no longer
active?
The actual behavior is that Query Analyzer will try to reestablish the
connection when you try to execute a query against a closed connection. You
can see this with a Profiler trace. Whether or not QA should notify the
user when this occurs is debatable.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jack Wachtler" <jack_wachtler@.comcast.net> wrote in message
news:AA7FD110-6FD0-44D5-A011-EE320063ABC8@.microsoft.com...
> I logged in through query anayzer as system admin and then logged in a
second session from a different user ID. From the sa window I issued
"sp_who" and found the session id of 55 for the second logged in session. I
then issued "KILL 55". When I reissued "sp_who" it showed the session id was
gone, but when I opened the user query analyzer window, I was still allowed
to issue SELECT statements. Shouldnt the user session window close or leave
some type of message to show that the session was logged out and the window
is no longer active?
Showing posts with label session. Show all posts
Showing posts with label session. Show all posts
Friday, March 23, 2012
Kill Session statement does not work
I logged in through query anayzer as system admin and then logged in a secon
d session from a different user ID. From the sa window I issued "sp_who" and
found the session id of 55 for the second logged in session. I then issued
"KILL 55". When I reissued
"sp_who" it showed the session id was gone, but when I opened the user query
analyzer window, I was still allowed to issue SELECT statements. Shouldnt t
he user session window close or leave some type of message to show that the
session was logged out and
the window is no longer active?Hi,
Query analyzer will establish back the connection to SQL server on issuing
any SQL / DML statements.
Thanks
Hari
MCDBA
"Jack Wachtler" <jack_wachtler@.comcast.net> wrote in message
news:AA7FD110-6FD0-44D5-A011-EE320063ABC8@.microsoft.com...
> I logged in through query anayzer as system admin and then logged in a
second session from a different user ID. From the sa window I issued
"sp_who" and found the session id of 55 for the second logged in session. I
then issued "KILL 55". When I reissued "sp_who" it showed the session id was
gone, but when I opened the user query analyzer window, I was still allowed
to issue SELECT statements. Shouldnt the user session window close or leave
some type of message to show that the session was logged out and the window
is no longer active?|||> Shouldnt the user session window close or leave some type of message
> to show that the session was logged out and the window is no longer
active?
The actual behavior is that Query Analyzer will try to reestablish the
connection when you try to execute a query against a closed connection. You
can see this with a Profiler trace. Whether or not QA should notify the
user when this occurs is debatable.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jack Wachtler" <jack_wachtler@.comcast.net> wrote in message
news:AA7FD110-6FD0-44D5-A011-EE320063ABC8@.microsoft.com...
> I logged in through query anayzer as system admin and then logged in a
second session from a different user ID. From the sa window I issued
"sp_who" and found the session id of 55 for the second logged in session. I
then issued "KILL 55". When I reissued "sp_who" it showed the session id was
gone, but when I opened the user query analyzer window, I was still allowed
to issue SELECT statements. Shouldnt the user session window close or leave
some type of message to show that the session was logged out and the window
is no longer active?
d session from a different user ID. From the sa window I issued "sp_who" and
found the session id of 55 for the second logged in session. I then issued
"KILL 55". When I reissued
"sp_who" it showed the session id was gone, but when I opened the user query
analyzer window, I was still allowed to issue SELECT statements. Shouldnt t
he user session window close or leave some type of message to show that the
session was logged out and
the window is no longer active?Hi,
Query analyzer will establish back the connection to SQL server on issuing
any SQL / DML statements.
Thanks
Hari
MCDBA
"Jack Wachtler" <jack_wachtler@.comcast.net> wrote in message
news:AA7FD110-6FD0-44D5-A011-EE320063ABC8@.microsoft.com...
> I logged in through query anayzer as system admin and then logged in a
second session from a different user ID. From the sa window I issued
"sp_who" and found the session id of 55 for the second logged in session. I
then issued "KILL 55". When I reissued "sp_who" it showed the session id was
gone, but when I opened the user query analyzer window, I was still allowed
to issue SELECT statements. Shouldnt the user session window close or leave
some type of message to show that the session was logged out and the window
is no longer active?|||> Shouldnt the user session window close or leave some type of message
> to show that the session was logged out and the window is no longer
active?
The actual behavior is that Query Analyzer will try to reestablish the
connection when you try to execute a query against a closed connection. You
can see this with a Profiler trace. Whether or not QA should notify the
user when this occurs is debatable.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jack Wachtler" <jack_wachtler@.comcast.net> wrote in message
news:AA7FD110-6FD0-44D5-A011-EE320063ABC8@.microsoft.com...
> I logged in through query anayzer as system admin and then logged in a
second session from a different user ID. From the sa window I issued
"sp_who" and found the session id of 55 for the second logged in session. I
then issued "KILL 55". When I reissued "sp_who" it showed the session id was
gone, but when I opened the user query analyzer window, I was still allowed
to issue SELECT statements. Shouldnt the user session window close or leave
some type of message to show that the session was logged out and the window
is no longer active?
Kill session
I've made a kill to a session that is stucked in SQL Server 2000 Enterprise +
SP3 with the status KILLED/ROLLBACK.
How can i force the kill ?
SQL> kill 567
SPID 567: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
Thanks in advance
CCCC
Take your coffee , it takes time
"CC" <CC@.discussions.microsoft.com> wrote in message
news:CB29FBB8-0772-4315-9DAB-3D898D39C354@.microsoft.com...
> I've made a kill to a session that is stucked in SQL Server 2000
> Enterprise +
> SP3 with the status KILLED/ROLLBACK.
> How can i force the kill ?
> SQL> kill 567
> SPID 567: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> Thanks in advance
> CC|||Thanks Uri but:
-Estimated time remaining: 0 seconds
-Estimated rollback completion:100%
-Spid last_batch in sysprocesses 2005-09-12 00:52:15.380 (7 Days)
This session is from a backup:
sp_lock:
567 14 0 0 DB [BULK-OP-LOG] X GRANT
567 14 0 0 DB [BULK-OP-DB] X GRANT
567 14 0 0 DB S GRANT
My full database backup don't start because of this session and i do not
want to restart SQL Server service on a production database because of a
zombie spid.
I think i will need more than a coffe : ) Can you help ?
The spid was killed but does not release locks held.
CC
"Uri Dimant" wrote:
> CC
> Take your coffee , it takes time
>
> "CC" <CC@.discussions.microsoft.com> wrote in message
> news:CB29FBB8-0772-4315-9DAB-3D898D39C354@.microsoft.com...
> > I've made a kill to a session that is stucked in SQL Server 2000
> > Enterprise +
> > SP3 with the status KILLED/ROLLBACK.
> > How can i force the kill ?
> >
> > SQL> kill 567
> > SPID 567: transaction rollback in progress. Estimated rollback completion:
> > 100%. Estimated time remaining: 0 seconds.
> >
> > Thanks in advance
> > CC
>
>|||CC
Well, one option is to restart MS Service , another is if the first one does
not help ,in Task Manager to kill the process.
"CC" <CC@.discussions.microsoft.com> wrote in message
news:131B1221-349A-4260-93F9-2FF6BAF63BBD@.microsoft.com...
> Thanks Uri but:
> -Estimated time remaining: 0 seconds
> -Estimated rollback completion:100%
> -Spid last_batch in sysprocesses 2005-09-12 00:52:15.380 (7 Days)
> This session is from a backup:
> sp_lock:
> 567 14 0 0 DB [BULK-OP-LOG] X GRANT
> 567 14 0 0 DB [BULK-OP-DB] X GRANT
> 567 14 0 0 DB S GRANT
> My full database backup don't start because of this session and i do not
> want to restart SQL Server service on a production database because of a
> zombie spid.
> I think i will need more than a coffe : ) Can you help ?
> The spid was killed but does not release locks held.
> CC
> "Uri Dimant" wrote:
>> CC
>> Take your coffee , it takes time
>>
>> "CC" <CC@.discussions.microsoft.com> wrote in message
>> news:CB29FBB8-0772-4315-9DAB-3D898D39C354@.microsoft.com...
>> > I've made a kill to a session that is stucked in SQL Server 2000
>> > Enterprise +
>> > SP3 with the status KILLED/ROLLBACK.
>> > How can i force the kill ?
>> >
>> > SQL> kill 567
>> > SPID 567: transaction rollback in progress. Estimated rollback
>> > completion:
>> > 100%. Estimated time remaining: 0 seconds.
>> >
>> > Thanks in advance
>> > CC
>>|||So is BULK-OP-DB and BULK-OP-LOG used for any file growth operations? I see
it holding locks on backups?
"Uri Dimant" wrote:
> CC
> Well, one option is to restart MS Service , another is if the first one does
> not help ,in Task Manager to kill the process.
>
>
> "CC" <CC@.discussions.microsoft.com> wrote in message
> news:131B1221-349A-4260-93F9-2FF6BAF63BBD@.microsoft.com...
> > Thanks Uri but:
> > -Estimated time remaining: 0 seconds
> > -Estimated rollback completion:100%
> > -Spid last_batch in sysprocesses 2005-09-12 00:52:15.380 (7 Days)
> > This session is from a backup:
> > sp_lock:
> > 567 14 0 0 DB [BULK-OP-LOG] X GRANT
> > 567 14 0 0 DB [BULK-OP-DB] X GRANT
> > 567 14 0 0 DB S GRANT
> >
> > My full database backup don't start because of this session and i do not
> > want to restart SQL Server service on a production database because of a
> > zombie spid.
> >
> > I think i will need more than a coffe : ) Can you help ?
> > The spid was killed but does not release locks held.
> >
> > CC
> >
> > "Uri Dimant" wrote:
> >
> >> CC
> >> Take your coffee , it takes time
> >>
> >>
> >> "CC" <CC@.discussions.microsoft.com> wrote in message
> >> news:CB29FBB8-0772-4315-9DAB-3D898D39C354@.microsoft.com...
> >> > I've made a kill to a session that is stucked in SQL Server 2000
> >> > Enterprise +
> >> > SP3 with the status KILLED/ROLLBACK.
> >> > How can i force the kill ?
> >> >
> >> > SQL> kill 567
> >> > SPID 567: transaction rollback in progress. Estimated rollback
> >> > completion:
> >> > 100%. Estimated time remaining: 0 seconds.
> >> >
> >> > Thanks in advance
> >> > CC
> >>
> >>
> >>
>
>sql
SP3 with the status KILLED/ROLLBACK.
How can i force the kill ?
SQL> kill 567
SPID 567: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
Thanks in advance
CCCC
Take your coffee , it takes time
"CC" <CC@.discussions.microsoft.com> wrote in message
news:CB29FBB8-0772-4315-9DAB-3D898D39C354@.microsoft.com...
> I've made a kill to a session that is stucked in SQL Server 2000
> Enterprise +
> SP3 with the status KILLED/ROLLBACK.
> How can i force the kill ?
> SQL> kill 567
> SPID 567: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> Thanks in advance
> CC|||Thanks Uri but:
-Estimated time remaining: 0 seconds
-Estimated rollback completion:100%
-Spid last_batch in sysprocesses 2005-09-12 00:52:15.380 (7 Days)
This session is from a backup:
sp_lock:
567 14 0 0 DB [BULK-OP-LOG] X GRANT
567 14 0 0 DB [BULK-OP-DB] X GRANT
567 14 0 0 DB S GRANT
My full database backup don't start because of this session and i do not
want to restart SQL Server service on a production database because of a
zombie spid.
I think i will need more than a coffe : ) Can you help ?
The spid was killed but does not release locks held.
CC
"Uri Dimant" wrote:
> CC
> Take your coffee , it takes time
>
> "CC" <CC@.discussions.microsoft.com> wrote in message
> news:CB29FBB8-0772-4315-9DAB-3D898D39C354@.microsoft.com...
> > I've made a kill to a session that is stucked in SQL Server 2000
> > Enterprise +
> > SP3 with the status KILLED/ROLLBACK.
> > How can i force the kill ?
> >
> > SQL> kill 567
> > SPID 567: transaction rollback in progress. Estimated rollback completion:
> > 100%. Estimated time remaining: 0 seconds.
> >
> > Thanks in advance
> > CC
>
>|||CC
Well, one option is to restart MS Service , another is if the first one does
not help ,in Task Manager to kill the process.
"CC" <CC@.discussions.microsoft.com> wrote in message
news:131B1221-349A-4260-93F9-2FF6BAF63BBD@.microsoft.com...
> Thanks Uri but:
> -Estimated time remaining: 0 seconds
> -Estimated rollback completion:100%
> -Spid last_batch in sysprocesses 2005-09-12 00:52:15.380 (7 Days)
> This session is from a backup:
> sp_lock:
> 567 14 0 0 DB [BULK-OP-LOG] X GRANT
> 567 14 0 0 DB [BULK-OP-DB] X GRANT
> 567 14 0 0 DB S GRANT
> My full database backup don't start because of this session and i do not
> want to restart SQL Server service on a production database because of a
> zombie spid.
> I think i will need more than a coffe : ) Can you help ?
> The spid was killed but does not release locks held.
> CC
> "Uri Dimant" wrote:
>> CC
>> Take your coffee , it takes time
>>
>> "CC" <CC@.discussions.microsoft.com> wrote in message
>> news:CB29FBB8-0772-4315-9DAB-3D898D39C354@.microsoft.com...
>> > I've made a kill to a session that is stucked in SQL Server 2000
>> > Enterprise +
>> > SP3 with the status KILLED/ROLLBACK.
>> > How can i force the kill ?
>> >
>> > SQL> kill 567
>> > SPID 567: transaction rollback in progress. Estimated rollback
>> > completion:
>> > 100%. Estimated time remaining: 0 seconds.
>> >
>> > Thanks in advance
>> > CC
>>|||So is BULK-OP-DB and BULK-OP-LOG used for any file growth operations? I see
it holding locks on backups?
"Uri Dimant" wrote:
> CC
> Well, one option is to restart MS Service , another is if the first one does
> not help ,in Task Manager to kill the process.
>
>
> "CC" <CC@.discussions.microsoft.com> wrote in message
> news:131B1221-349A-4260-93F9-2FF6BAF63BBD@.microsoft.com...
> > Thanks Uri but:
> > -Estimated time remaining: 0 seconds
> > -Estimated rollback completion:100%
> > -Spid last_batch in sysprocesses 2005-09-12 00:52:15.380 (7 Days)
> > This session is from a backup:
> > sp_lock:
> > 567 14 0 0 DB [BULK-OP-LOG] X GRANT
> > 567 14 0 0 DB [BULK-OP-DB] X GRANT
> > 567 14 0 0 DB S GRANT
> >
> > My full database backup don't start because of this session and i do not
> > want to restart SQL Server service on a production database because of a
> > zombie spid.
> >
> > I think i will need more than a coffe : ) Can you help ?
> > The spid was killed but does not release locks held.
> >
> > CC
> >
> > "Uri Dimant" wrote:
> >
> >> CC
> >> Take your coffee , it takes time
> >>
> >>
> >> "CC" <CC@.discussions.microsoft.com> wrote in message
> >> news:CB29FBB8-0772-4315-9DAB-3D898D39C354@.microsoft.com...
> >> > I've made a kill to a session that is stucked in SQL Server 2000
> >> > Enterprise +
> >> > SP3 with the status KILLED/ROLLBACK.
> >> > How can i force the kill ?
> >> >
> >> > SQL> kill 567
> >> > SPID 567: transaction rollback in progress. Estimated rollback
> >> > completion:
> >> > 100%. Estimated time remaining: 0 seconds.
> >> >
> >> > Thanks in advance
> >> > CC
> >>
> >>
> >>
>
>sql
Kill session
I've made a kill to a session that is stucked in SQL Server 2000 Enterprise +
SP3 with the status KILLED/ROLLBACK.
How can i force the kill ?
SQL> kill 567
SPID 567: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
Thanks in advance
CC
CC
Take your coffee , it takes time
"CC" <CC@.discussions.microsoft.com> wrote in message
news:CB29FBB8-0772-4315-9DAB-3D898D39C354@.microsoft.com...
> I've made a kill to a session that is stucked in SQL Server 2000
> Enterprise +
> SP3 with the status KILLED/ROLLBACK.
> How can i force the kill ?
> SQL> kill 567
> SPID 567: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> Thanks in advance
> CC
|||Thanks Uri but:
-Estimated time remaining: 0 seconds
-Estimated rollback completion:100%
-Spid last_batch in sysprocesses 2005-09-12 00:52:15.380 (7 Days)
This session is from a backup:
sp_lock:
5671400DB[BULK-OP-LOG] XGRANT
5671400DB[BULK-OP-DB] XGRANT
5671400DB SGRANT
My full database backup don't start because of this session and i do not
want to restart SQL Server service on a production database because of a
zombie spid.
I think i will need more than a coffe : ) Can you help ?
The spid was killed but does not release locks held.
CC
"Uri Dimant" wrote:
> CC
> Take your coffee , it takes time
>
> "CC" <CC@.discussions.microsoft.com> wrote in message
> news:CB29FBB8-0772-4315-9DAB-3D898D39C354@.microsoft.com...
>
>
|||CC
Well, one option is to restart MS Service , another is if the first one does
not help ,in Task Manager to kill the process.
"CC" <CC@.discussions.microsoft.com> wrote in message
news:131B1221-349A-4260-93F9-2FF6BAF63BBD@.microsoft.com...[vbcol=seagreen]
> Thanks Uri but:
> -Estimated time remaining: 0 seconds
> -Estimated rollback completion:100%
> -Spid last_batch in sysprocesses 2005-09-12 00:52:15.380 (7 Days)
> This session is from a backup:
> sp_lock:
> 567 14 0 0 DB [BULK-OP-LOG] X GRANT
> 567 14 0 0 DB [BULK-OP-DB] X GRANT
> 567 14 0 0 DB S GRANT
> My full database backup don't start because of this session and i do not
> want to restart SQL Server service on a production database because of a
> zombie spid.
> I think i will need more than a coffe : ) Can you help ?
> The spid was killed but does not release locks held.
> CC
> "Uri Dimant" wrote:
|||So is BULK-OP-DB and BULK-OP-LOG used for any file growth operations? I see
it holding locks on backups?
"Uri Dimant" wrote:
> CC
> Well, one option is to restart MS Service , another is if the first one does
> not help ,in Task Manager to kill the process.
>
>
> "CC" <CC@.discussions.microsoft.com> wrote in message
> news:131B1221-349A-4260-93F9-2FF6BAF63BBD@.microsoft.com...
>
>
SP3 with the status KILLED/ROLLBACK.
How can i force the kill ?
SQL> kill 567
SPID 567: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
Thanks in advance
CC
CC
Take your coffee , it takes time
"CC" <CC@.discussions.microsoft.com> wrote in message
news:CB29FBB8-0772-4315-9DAB-3D898D39C354@.microsoft.com...
> I've made a kill to a session that is stucked in SQL Server 2000
> Enterprise +
> SP3 with the status KILLED/ROLLBACK.
> How can i force the kill ?
> SQL> kill 567
> SPID 567: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> Thanks in advance
> CC
|||Thanks Uri but:
-Estimated time remaining: 0 seconds
-Estimated rollback completion:100%
-Spid last_batch in sysprocesses 2005-09-12 00:52:15.380 (7 Days)
This session is from a backup:
sp_lock:
5671400DB[BULK-OP-LOG] XGRANT
5671400DB[BULK-OP-DB] XGRANT
5671400DB SGRANT
My full database backup don't start because of this session and i do not
want to restart SQL Server service on a production database because of a
zombie spid.
I think i will need more than a coffe : ) Can you help ?
The spid was killed but does not release locks held.
CC
"Uri Dimant" wrote:
> CC
> Take your coffee , it takes time
>
> "CC" <CC@.discussions.microsoft.com> wrote in message
> news:CB29FBB8-0772-4315-9DAB-3D898D39C354@.microsoft.com...
>
>
|||CC
Well, one option is to restart MS Service , another is if the first one does
not help ,in Task Manager to kill the process.
"CC" <CC@.discussions.microsoft.com> wrote in message
news:131B1221-349A-4260-93F9-2FF6BAF63BBD@.microsoft.com...[vbcol=seagreen]
> Thanks Uri but:
> -Estimated time remaining: 0 seconds
> -Estimated rollback completion:100%
> -Spid last_batch in sysprocesses 2005-09-12 00:52:15.380 (7 Days)
> This session is from a backup:
> sp_lock:
> 567 14 0 0 DB [BULK-OP-LOG] X GRANT
> 567 14 0 0 DB [BULK-OP-DB] X GRANT
> 567 14 0 0 DB S GRANT
> My full database backup don't start because of this session and i do not
> want to restart SQL Server service on a production database because of a
> zombie spid.
> I think i will need more than a coffe : ) Can you help ?
> The spid was killed but does not release locks held.
> CC
> "Uri Dimant" wrote:
|||So is BULK-OP-DB and BULK-OP-LOG used for any file growth operations? I see
it holding locks on backups?
"Uri Dimant" wrote:
> CC
> Well, one option is to restart MS Service , another is if the first one does
> not help ,in Task Manager to kill the process.
>
>
> "CC" <CC@.discussions.microsoft.com> wrote in message
> news:131B1221-349A-4260-93F9-2FF6BAF63BBD@.microsoft.com...
>
>
Kill session
I've made a kill to a session that is stucked in SQL Server 2000 Enterprise
+
SP3 with the status KILLED/ROLLBACK.
How can i force the kill ?
SQL> kill 567
SPID 567: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
Thanks in advance
CCCC
Take your coffee , it takes time
"CC" <CC@.discussions.microsoft.com> wrote in message
news:CB29FBB8-0772-4315-9DAB-3D898D39C354@.microsoft.com...
> I've made a kill to a session that is stucked in SQL Server 2000
> Enterprise +
> SP3 with the status KILLED/ROLLBACK.
> How can i force the kill ?
> SQL> kill 567
> SPID 567: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> Thanks in advance
> CC|||Thanks Uri but:
-Estimated time remaining: 0 seconds
-Estimated rollback completion:100%
-Spid last_batch in sysprocesses 2005-09-12 00:52:15.380 (7 Days)
This session is from a backup:
sp_lock:
567 14 0 0 DB [BULK-OP-LOG] X GRANT
567 14 0 0 DB [BULK-OP-DB] X GRANT
567 14 0 0 DB S GRANT
My full database backup don't start because of this session and i do not
want to restart SQL Server service on a production database because of a
zombie spid.
I think i will need more than a coffe : ) Can you help ?
The spid was killed but does not release locks held.
CC
"Uri Dimant" wrote:
> CC
> Take your coffee , it takes time
>
> "CC" <CC@.discussions.microsoft.com> wrote in message
> news:CB29FBB8-0772-4315-9DAB-3D898D39C354@.microsoft.com...
>
>|||CC
Well, one option is to restart MS Service , another is if the first one does
not help ,in Task Manager to kill the process.
"CC" <CC@.discussions.microsoft.com> wrote in message
news:131B1221-349A-4260-93F9-2FF6BAF63BBD@.microsoft.com...[vbcol=seagreen]
> Thanks Uri but:
> -Estimated time remaining: 0 seconds
> -Estimated rollback completion:100%
> -Spid last_batch in sysprocesses 2005-09-12 00:52:15.380 (7 Days)
> This session is from a backup:
> sp_lock:
> 567 14 0 0 DB [BULK-OP-LOG] X GRANT
> 567 14 0 0 DB [BULK-OP-DB] X GRANT
> 567 14 0 0 DB S GRANT
> My full database backup don't start because of this session and i do not
> want to restart SQL Server service on a production database because of a
> zombie spid.
> I think i will need more than a coffe : ) Can you help ?
> The spid was killed but does not release locks held.
> CC
> "Uri Dimant" wrote:
>|||So is BULK-OP-DB and BULK-OP-LOG used for any file growth operations? I see
it holding locks on backups?
"Uri Dimant" wrote:
> CC
> Well, one option is to restart MS Service , another is if the first one do
es
> not help ,in Task Manager to kill the process.
>
>
> "CC" <CC@.discussions.microsoft.com> wrote in message
> news:131B1221-349A-4260-93F9-2FF6BAF63BBD@.microsoft.com...
>
>
+
SP3 with the status KILLED/ROLLBACK.
How can i force the kill ?
SQL> kill 567
SPID 567: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
Thanks in advance
CCCC
Take your coffee , it takes time
"CC" <CC@.discussions.microsoft.com> wrote in message
news:CB29FBB8-0772-4315-9DAB-3D898D39C354@.microsoft.com...
> I've made a kill to a session that is stucked in SQL Server 2000
> Enterprise +
> SP3 with the status KILLED/ROLLBACK.
> How can i force the kill ?
> SQL> kill 567
> SPID 567: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> Thanks in advance
> CC|||Thanks Uri but:
-Estimated time remaining: 0 seconds
-Estimated rollback completion:100%
-Spid last_batch in sysprocesses 2005-09-12 00:52:15.380 (7 Days)
This session is from a backup:
sp_lock:
567 14 0 0 DB [BULK-OP-LOG] X GRANT
567 14 0 0 DB [BULK-OP-DB] X GRANT
567 14 0 0 DB S GRANT
My full database backup don't start because of this session and i do not
want to restart SQL Server service on a production database because of a
zombie spid.
I think i will need more than a coffe : ) Can you help ?
The spid was killed but does not release locks held.
CC
"Uri Dimant" wrote:
> CC
> Take your coffee , it takes time
>
> "CC" <CC@.discussions.microsoft.com> wrote in message
> news:CB29FBB8-0772-4315-9DAB-3D898D39C354@.microsoft.com...
>
>|||CC
Well, one option is to restart MS Service , another is if the first one does
not help ,in Task Manager to kill the process.
"CC" <CC@.discussions.microsoft.com> wrote in message
news:131B1221-349A-4260-93F9-2FF6BAF63BBD@.microsoft.com...[vbcol=seagreen]
> Thanks Uri but:
> -Estimated time remaining: 0 seconds
> -Estimated rollback completion:100%
> -Spid last_batch in sysprocesses 2005-09-12 00:52:15.380 (7 Days)
> This session is from a backup:
> sp_lock:
> 567 14 0 0 DB [BULK-OP-LOG] X GRANT
> 567 14 0 0 DB [BULK-OP-DB] X GRANT
> 567 14 0 0 DB S GRANT
> My full database backup don't start because of this session and i do not
> want to restart SQL Server service on a production database because of a
> zombie spid.
> I think i will need more than a coffe : ) Can you help ?
> The spid was killed but does not release locks held.
> CC
> "Uri Dimant" wrote:
>|||So is BULK-OP-DB and BULK-OP-LOG used for any file growth operations? I see
it holding locks on backups?
"Uri Dimant" wrote:
> CC
> Well, one option is to restart MS Service , another is if the first one do
es
> not help ,in Task Manager to kill the process.
>
>
> "CC" <CC@.discussions.microsoft.com> wrote in message
> news:131B1221-349A-4260-93F9-2FF6BAF63BBD@.microsoft.com...
>
>
Wednesday, March 21, 2012
Kill 142,
Kill 142, I was trying to kill one of the session and found the following
error.
SPID 142: transaction rollback in progress. Estimated rollback completion:
0%. Estimated time remaining: 0 seconds.
when I use : dbcc opentran, it gave me following result
Oldest active transaction:
SPID (server process ID) : 142
UID (user ID) : 1
Name : user_transaction
LSN : (535:511:1)
Start time : May 3 2007 10:31:54:390AM
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
when I used sp_who2, it gave me following entry
142 RUNNABLE WebSp WEBEDA2 . master KILLED/ROLLBACK 16 1 01/01
00:00:00 .Net SqlClient Data Provider 142
when I used sp_lock, it gave me following entries.
63 1 (master) 85575343 0 TAB IS GRANT
142 11 (myDB) 354100302 1 KEY (9500ee50c84e) X GRANT
142 11 (myDB) 354100302 1 PAG 1:2660 IX GRANT
142 11 (myDB) 354100302 0 TAB IX GRANT
142 11 (myDB) 0 0 DB S GRANT
I would appreciate if anyone help me out... its the production box and I
can't restart the SQL Server Services.
Thanks
Rogers,
If you repeatedly do "KILL 142 WITH STATUSONLY" it will probably continually
repeat the same status. If so, then this kind only gets eliminated by
restarting the SQL Server service.
As long as it is not locking anything anyone else needs, it can be ignored,
but it looks like that may not be the case with the MyDB locks.
Time to figure out the nearest window for a restart.
RLF
"Rogers" <naissani@.hotmail.com> wrote in message
news:%23qvet%23njHHA.1624@.TK2MSFTNGP02.phx.gbl...
> Kill 142, I was trying to kill one of the session and found the following
> error.
> SPID 142: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
> when I use : dbcc opentran, it gave me following result
> Oldest active transaction:
> SPID (server process ID) : 142
> UID (user ID) : 1
> Name : user_transaction
> LSN : (535:511:1)
> Start time : May 3 2007 10:31:54:390AM
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> when I used sp_who2, it gave me following entry
> 142 RUNNABLE WebSp WEBEDA2 . master KILLED/ROLLBACK 16 1 01/01
> 00:00:00 .Net SqlClient Data Provider 142
> when I used sp_lock, it gave me following entries.
> 63 1 (master) 85575343 0 TAB IS GRANT
> 142 11 (myDB) 354100302 1 KEY (9500ee50c84e) X GRANT
> 142 11 (myDB) 354100302 1 PAG 1:2660 IX GRANT
> 142 11 (myDB) 354100302 0 TAB IX GRANT
> 142 11 (myDB) 0 0 DB S GRANT
> I would appreciate if anyone help me out... its the production box and I
> can't restart the SQL Server Services.
> Thanks
>
error.
SPID 142: transaction rollback in progress. Estimated rollback completion:
0%. Estimated time remaining: 0 seconds.
when I use : dbcc opentran, it gave me following result
Oldest active transaction:
SPID (server process ID) : 142
UID (user ID) : 1
Name : user_transaction
LSN : (535:511:1)
Start time : May 3 2007 10:31:54:390AM
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
when I used sp_who2, it gave me following entry
142 RUNNABLE WebSp WEBEDA2 . master KILLED/ROLLBACK 16 1 01/01
00:00:00 .Net SqlClient Data Provider 142
when I used sp_lock, it gave me following entries.
63 1 (master) 85575343 0 TAB IS GRANT
142 11 (myDB) 354100302 1 KEY (9500ee50c84e) X GRANT
142 11 (myDB) 354100302 1 PAG 1:2660 IX GRANT
142 11 (myDB) 354100302 0 TAB IX GRANT
142 11 (myDB) 0 0 DB S GRANT
I would appreciate if anyone help me out... its the production box and I
can't restart the SQL Server Services.
Thanks
Rogers,
If you repeatedly do "KILL 142 WITH STATUSONLY" it will probably continually
repeat the same status. If so, then this kind only gets eliminated by
restarting the SQL Server service.
As long as it is not locking anything anyone else needs, it can be ignored,
but it looks like that may not be the case with the MyDB locks.
Time to figure out the nearest window for a restart.
RLF
"Rogers" <naissani@.hotmail.com> wrote in message
news:%23qvet%23njHHA.1624@.TK2MSFTNGP02.phx.gbl...
> Kill 142, I was trying to kill one of the session and found the following
> error.
> SPID 142: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds.
> when I use : dbcc opentran, it gave me following result
> Oldest active transaction:
> SPID (server process ID) : 142
> UID (user ID) : 1
> Name : user_transaction
> LSN : (535:511:1)
> Start time : May 3 2007 10:31:54:390AM
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> when I used sp_who2, it gave me following entry
> 142 RUNNABLE WebSp WEBEDA2 . master KILLED/ROLLBACK 16 1 01/01
> 00:00:00 .Net SqlClient Data Provider 142
> when I used sp_lock, it gave me following entries.
> 63 1 (master) 85575343 0 TAB IS GRANT
> 142 11 (myDB) 354100302 1 KEY (9500ee50c84e) X GRANT
> 142 11 (myDB) 354100302 1 PAG 1:2660 IX GRANT
> 142 11 (myDB) 354100302 0 TAB IX GRANT
> 142 11 (myDB) 0 0 DB S GRANT
> I would appreciate if anyone help me out... its the production box and I
> can't restart the SQL Server Services.
> Thanks
>
Subscribe to:
Posts (Atom)