Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Unable to kill a session Expand / Collapse
Author
Message
Posted Friday, November 23, 2012 5:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 16, 2014 6:55 AM
Points: 54, Visits: 555
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.
Post #1388127
Posted Friday, November 23, 2012 5:08 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, March 21, 2014 9:46 AM
Points: 387, Visits: 1,078
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.
Post #1388131
Posted Friday, November 23, 2012 5:10 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 16, 2014 6:55 AM
Points: 54, Visits: 555
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.
Post #1388133
Posted Friday, November 23, 2012 5:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 178, 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
Post #1388134
Posted Friday, November 23, 2012 5:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 16, 2014 6:55 AM
Points: 54, Visits: 555
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.
Post #1388135
Posted Friday, November 23, 2012 5:29 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 1, 2013 10:17 AM
Points: 323, Visits: 984
what is the output of
select spid,db_name(dbid) from sysprocesses where db_name(dbid) ='DatabaseName'



-----------------------------------------------------------------------------
संकेत कोकणे
Post #1388139
Posted Friday, November 23, 2012 6:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 16, 2014 6:55 AM
Points: 54, Visits: 555
We are getting the below output when we trigger the query given.

55 Our DB Name
Post #1388148
Posted Friday, November 23, 2012 6:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
Points: 178, 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
Post #1388160
Posted Friday, November 23, 2012 6:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 16, 2014 6:55 AM
Points: 54, Visits: 555
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.

Post #1388164
Posted Sunday, November 25, 2012 10:10 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 16, 2014 6:55 AM
Points: 54, Visits: 555
Hi Friends,

Any update
Post #1388432
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse