Kill spid?? Kill spid?? DroopyA (Programmer) (OP) 7 Aug 03 15:35 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 RE: Kill spid?? DroopyA (Programmer) (OP) 7 Aug 03 17:00 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 WRITER2 | sleeping | LOG WRITER3 | background | SIGNAL HANDLER4 | background | LOCK MONITOR5 | background | TASK MANAGER6 | background | TASK MANAGER7 | sleeping | CHECKPOINT SLEEP8 | background | TASK MANAGER9 | background | TASK MANAGER10 | background | TASK MANAGER51 | runnable | SELECTWhich 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 RE: Kill spid?? gny (Programmer) 7 Aug 03 18:20 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 RE: Kill spid?? ks2000 (Programmer) 8 Aug 03 07:29 hi try like this Go to Enterprise managergo to respective database right clikc on that database then select propertiesthen select OptionsThen there is a select box Restrict access, select this option ane select single user(Radio button option).Then try your back up now Good LuckGopala Krishna Kakani RE: Kill spid?? DroopyA (Programmer) (OP) 8 Aug 03 09:17 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") ' 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"))) Case "runnable" [color] ' Kill this user based on the spid m_cn.Execute ("KILL " & rsUsers.Fields("spid")) 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") 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") MsgBox "Restore Completed successfully", vbOKOnly, "TESTING" End IfThank you-N473 RE: Kill spid?? pweegar (Programmer) 8 Aug 03 13:43 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.