SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unable to kill a session


Unable to kill a session

Author
Message
rj529
rj529
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 557
Hi,

When we kill a session, we get the below error. What is the fix for this? In the backend(sp_who2) it is showing this SPID is running. Restore log is running. I just need to drop this database. Give me suggesstions on how to drop this database. We use sql server 2000


Server: Msg 6101, Level 16, State 1, Line 1
Process ID 55 is not a valid process ID. Choose a number between 1 and 32817.
SQL Show
SQL Show
SSChasing Mays
SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)

Group: General Forum Members
Points: 653 Visits: 1078
Did you try to kill restore log operation? It may take time to "rollback" this restore if log backup has large number of transactin. You need to wait until your restore operation completes.
rj529
rj529
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 557
We tried to kill the restore log operation. But we get the below error msg:

Server: Msg 6101, Level 16, State 1, Line 1
Process ID 55 is not a valid process ID. Choose a number between 1 and 32817.
Dennis Post
Dennis Post
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 547
Hi,

If you're 100% sure that process can be killed and that there's nothing else going on that's important.

You can try.
USE master
GO
ALTER DATABASE <DBName>
SET OFFLINE WITH ROLLBACK IMMEDIATE



I don't have SQL 2000 anymore and am just assuming this works.

If the sql service can be restarted then that'll do the trick as well. Ofcourse caution is sensible.



For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
rj529
rj529
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 557
When i execute i get the below error:

Server: Msg 5061, Level 16, State 1, Line 2
ALTER DATABASE failed because a lock could not be placed on database 'DBNAME'. Try again later.
Server: Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.
sanket kokane
sanket kokane
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 1022
what is the output of
select spid,db_name(dbid) from sysprocesses where db_name(dbid) ='DatabaseName'



-----------------------------------------------------------------------------
संकेत कोकणे
rj529
rj529
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 557
We are getting the below output when we trigger the query given.

55 Our DB Name
Dennis Post
Dennis Post
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 547
Is it save for you to restart the sqlserver service?



For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
rj529
rj529
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 557
Restarting the sql server services is not possible in this server. Let me explain clearly what is the exact issue.

This is the log shipping server. The logshipping is failed for 2 days. When we cross-checked the log with size of 389KB is restoring for more than 2 days. The session id which is restoring the log is 55. We are unable to kill this session. When we kill this session, we get error as:
Server: Msg 6101, Level 16, State 1, Line 1
Process ID 55 is not a valid process ID. Choose a number between 1 and 32817.
We are having full backup in production server. It is easy for us to configure the log shipping with this backup. But we are unable to drop the existing DB in log shipping server. The session which I have mentioned has taken 129163145 CPU time and 63200955 DISK IO. Restoring log of 389 kb should not take this much. Please suggest us a solution without restarting the sql services.

Note: We have already planned of restarting the sql services by coming 3rd. But till that we should not be without logshipping for this DB.

Thanks in advance.
rj529
rj529
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 557
Hi Friends,

Any update
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search