Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2006
    Location
    DC
    Posts
    123

    Unanswered: Remotely Ending Service in 2003 to Restore database

    Morning Guys,
    I have a database where I need to kill a remote connection.
    If I was manually logged into the box i would you a netstat command to stop the service.
    I would like to end a process from another machine before starting a restore process

    Does anyone have any suggestions?

    thanks,
    Jonathan

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    do you know the SPID? If so use the KILL command in SQL to kill the SPID.

    or you can do a ALTER DATABASE MyDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE.

    You may have to use SINGLE_USER instead of restricted depending on who has the SPID open.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2006
    Location
    DC
    Posts
    123
    I use a script to kill all db processes.
    I thought that the connection would use the same spid everytime but it doesn't after it is killed.
    When killed it seems as if the connection reconnects before the database is put into single user mode or restricted mode.

    Sometimes it gets to single user mode successfully most of the time it doesn't.
    When killing the processes manullay in query analyzer and then putting the database in single user mode the user gets connected back before also....
    this is a first.... so I'm thinking I have to go to the root and stop the application user connection before starting the process.....
    Does that answer your question... ?
    jonathan

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    what script do you use?

    When I do something like...

    ALTER DATABASE MyDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
    GO
    RESTORE DATABASE MyDB FROM DISK = 'etc......'

    All in one one push of the F5 button, no one is that fast to get connected again.

    BTW, in the query analyzer, are you logged into the database you are trying to restore? That's always funny when I point that out to someone here at the shop.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •