Sunday, March 11, 2012

error 6107

I tried to run detach on a database, I get an error message error 6107, "only user processes can be killed" I also tried to rename and delete the database but would not allow me to do either, it was coming up with the same error message. I sometimes get error 3701 as well " cannot detach databse because it is in use" I found out this error is also because of this system user in the databse. I looked in the processes and I can see spid 6 used by system user in the database, I cannot kill the user. Option to kill is greyed out. Running stope procedure did not also help. I did not create this system user, it was self generated or maybe as a result of fuction added to the databse, I have no problem killing all the users in the database except this system user which I did not know how it got there.
This database is in a cluster and I have over 40 databases that serves as backend to a lot of websites, so stopping and starting sqlserver is not an option, also as this is a cluster, I am not sure of what will happen if I try to stop and start sql server on the active node.
I am running clustered sql server 2000 ent edition on windows 2000Your problem is that you may have a user or a process using that DataBase.
Try to kill all the SPID ID for that DataBase.

______________
Paulo Gonalves|||You MAY need to reqd microsoft article 275661 reproduced below - I know its for SQL 7 but you never know, might be useful to you

FIX: Error Message 6107 Occurs with Transact-SQL KILL Command After Pooled Connection is Reset
The information in this article applies to:
Microsoft SQL Server 7.0

This article was previously published under Q275661
BUG #: 58170 (SQLBUG_70)

SYMPTOMS: When SQL Server performs a remote stored procedure call (RPC) or a linked server query to another SQL Server server, after the query completes, a request is submitted to the remote server to reset the connection before the connection is put back into a connection pool for a period of time. An ODBC or OLE DB-based application that is using the driver or provider shipped with Microsoft SQL Server 2000 may also reset the connection when using connection or session pooling.

After the connection has been reset, an attempt to terminate a system process ID (SPID) with the Transact-SQL KILL command causes the following error message to occur:

Server: Msg 6107, Level 14, State 1, Line 1 Only user processes can be killed.
STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For information about how to download and install the latest SQL Server Service Pack, see the following Microsoft Web site:
http://support.microsoft.com/highlights/sql.asp

For more information, contact your primary support provider.

MORE INFORMATION
After the connection has been reset, the SPID indicates that it is in a "dormant" state in the sysprocesses system table. If you do not use the connection for a period of time a background process closes the connection. Such a SPID should not be holding on to any locks or resources that would affect other users.

However, if the pooled connection is reused and starts a command that ties up resources or that causes blocking that needs to be terminated (with the KILL command), you are unable to terminate the SPID with the KILL command.

No comments:

Post a Comment