Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Kill spid??

Kill spid??

Kill spid??

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?


RE: Kill spid??

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:

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.


RE: Kill spid??

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
        SELECT spid
            dbid = @nDR_ID

    OPEN curProcesses

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

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

RE: Kill spid??

 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

RE: Kill spid??

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

    ' 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
            ' 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
        ' 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 If

Thank you

RE: Kill spid??

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close