Kill spid??

[DroopyA]

Ok, I'm new to SQL server in case you couldn't tell by now.. but when trying to execute KILL 1 I get the error that says:

"Only user processes can be killed"

How do I know of the spid is a User Process?

Thanks
-N473

 

[DroopyA]

Well let me clear up my situation a little. I'm trying to do a Restore on a good backup of a SQL database. But whenever I run the RESTORE command I get an error saying that their are other users loged onto the server. So I run sp_who, find out the IDs of those loged on and now I'm trying to log them off automatically so the Restore can begin it's operations. However, I was told "KILL" would log the users off but now I'm getting an error saying that only "user processes" can be killed. When I run sp_who in the Query Analize this is what I get:

SPID | STAUS | COMMAND
----------------------
1 | background | LAZY WRITER
2 | sleeping | LOG WRITER
3 | background | SIGNAL HANDLER
4 | background | LOCK MONITOR
5 | background | TASK MANAGER
6 | background | TASK MANAGER
7 | sleeping | CHECKPOINT SLEEP
8 | background | TASK MANAGER
9 | background | TASK MANAGER
10 | background | TASK MANAGER
51 | runnable | SELECT

Which one of these needs to be killed before I can restore? what is all of this? According to Microsoft I can kill everything except AWAITING COMMAND, CHECKPOINT SLEEP, LAZY WRITER, LOCK MONITOR, SELECT, SIGNAL HANDLER. But even when I tried Killing the IDs that wasn't one of the above mentioned commands I still got the "User Processes" error. Any help would be greatly appreciated, for I've been stuck on this backup/restore thing for 3 days now.

Thanks
-N473

 

[gny]

Here's a little snippet from an SP i wrote a couple of years ago when i wanted to drop a db. Given the id of the database (@DR_ID), killed my processes just fine...
Get the dbid using the db_id() function.

	-- Get a cursor with the processes that have to die in order to be able to drop db
	DECLARE curProcesses CURSOR
		LOCAL
		FAST_FORWARD
		READ_ONLY
	FOR
		SELECT spid
		FROM
			Master..sysprocesses
		WHERE
			dbid = @nDR_ID

	OPEN curProcesses

	FETCH NEXT FROM curProcesses INTO --Gets the first process
		@nKillProcess
	SET @nFetchStatus = @@FETCH_STATUS

	--Kill the processes
	WHILE @nFetchStatus = 0
	BEGIN
		SET @sTemp ='KILL ' + CAST(@nKillProcess as varchar(5))
		EXEC(@sTemp)
		FETCH NEXT FROM curProcesses INTO --Gets the next process
			@nKillProcess
		SET @nFetchStatus = @@FETCH_STATUS
	END
	CLOSE curProcesses
	DEALLOCATE curProcesses

 

[ks2000]

hi
try like this
Go to Enterprise manager
go to respective database
right clikc on that database
then select properties
then select Options
Then there is a select box Restrict access, select this option ane select single user(Radio button option).

Then try your back up now

Good Luck
Gopala Krishna Kakani

 

[DroopyA]

The problem is, my company makes a program that helps keep track of maintance on heavy machinery (like generators at the hoover damn, or Air Craft carriers) and we are currently migrating our database from Access to SQL Server (hence why I know nothing about SQL). To insure that no history is lost in case OSHA comes to check on our customers, we allow for a Backup/Restore option. Because most of our users know nothing about computers, this should be a one click operation and only a small percentage of our clients will actually have SQL Server installed on their machine. Therefore, the restore needs to be done through code.

The problem I'm having is that when I click restore I get an error saying that there are users currently logged on to the server. So I've decided to ask the user, if they would like to automatically log everybody off of the system and continue with the restore. I was told the way to log everybody off, was with the Kill command. But when I execute command, I get the "User Process" error. My above post is what comes up when you run sp_who in the Query Analyzer, but I have no idea what that means. Which one of those needs to be logged off, and which ones are allowed to stay logged in before I can run a restore? How am I suppose to know who is ok, and who needs to be killed? Is there another way to get everybody off besides kill? If you don't know VB that's fine, just the logic would be a great help (although a code example would be nice if you could provide one).

Here is what I have so far:

' Open the SQL Connection
Set m_cn = New ADODB.Connection
m_cn.ConnectionString = "Provider = SQLOLEDB.1; Integrated Security=SSPI; Initial Catalog = *******SQLCS; Data Source = " & txtComputerName.Text & ";Initial File Name = C:\PROGRA~1\*******\DATA\*******SQL.mdf"
m_cn.CursorLocation = adUseClient
m_cn.Open

' Get the results from sp_who
Set rsUsers = New ADODB.Recordset
rsUsers.ActiveConnection = m_cn
rsUsers.Open m_cn.Execute("sp_who&quot

' Are their any users loged on to the System
If rsUsers.RecordCount > 1 Then
intResponse = MsgBox("There are " & rsUsers.RecordCount & " users loged on to the server at this time. Would you like to kick them off?", vbYesNo, app.Title)

' Kick users off the Server?
If intResponse = vbYes Then
Do While rsUsers.EOF = False
' is 'runnable' the only logons I can kill?
Select Case LCase(Trim(rsUsers.Fields("status&quot))
Case "runnable"
[color] ' Kill this user based on the spid[/color]
m_cn.Execute ("KILL " & rsUsers.Fields("spid&quot)
End Select

rsUsers.MoveNext
Loop

' Restore the Backup
m_cn.Execute ("RESTORE DATABASE [*******SQLCS] FROM DISK = N'C:\Program Files\*******\Data\*******Backup.dat' WITH FILE = 12, NOUNLOAD , STATS = 10, RECOVERY&quot
MsgBox "Restore Completed successfully", vbOKOnly, "TESTING"
End If
Else
' Nobody logged on, just restore the Database
m_cn.Execute ("RESTORE DATABASE [*******SQLCS] FROM DISK = N'C:\Program Files\*******\Data\*******Backup.dat' WITH FILE = 12, NOUNLOAD , STATS = 10, RECOVERY&quot
MsgBox "Restore Completed successfully", vbOKOnly, "TESTING"
End If

Thank you
-N473

 

[pweegar]

Well, what you COULD do (and I admit it would be a bit drastic) is to run SHUTDOWN NO WAIT inside our SQL. This will disconnect ALL users, no checkpoints are taken and no new logins are allowed. Then inside your program (lloks like your using VB?) shell out and do a net start sqlserveragent to restart your sql. THAT should kill all user processes and restart SQL so that you can then restore your db's. And in order to do a restore you must be logged on as the system administrator, with exclusive use of the db.