November 23, 2012 at 5:01 am
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.
November 23, 2012 at 5:08 am
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.
November 23, 2012 at 5:10 am
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.
November 23, 2012 at 5:11 am
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.
November 23, 2012 at 5:15 am
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.
November 23, 2012 at 5:29 am
what is the output of
select spid,db_name(dbid) from sysprocesses where db_name(dbid) ='DatabaseName'
-----------------------------------------------------------------------------
संकेत कोकणे
November 23, 2012 at 6:06 am
We are getting the below output when we trigger the query given.
55Our DB Name
November 23, 2012 at 6:38 am
November 23, 2012 at 6:49 am
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.
November 25, 2012 at 10:10 pm
Hi Friends,
Any update
November 26, 2012 at 12:58 am
rajkiran.panchagiri (11/25/2012)
Hi Friends,Any update
have you checked with Sp_who2 and sp_lock that which seesions are got blocked for that particular database ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply