InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 19:05:33 GMT20http://www.sqlservercentral.com/Forums/Topic1404778-391-1.aspxEnsure you do what you do in the same job step, no telling what may happen if it moves step and gets a different spid while its in single user mode not allowing you to access the DB as someone else beat you to it.SoALTER DATABASE myDB SET SINGLE_USER WITH ROLLBACK IMMEDIATEGORESTORE DATABASE myDB FROM DISK = 'xxxxxxxxxxxxxxxxxxxxxx'GOIn the same job step.Wed, 09 Jan 2013 08:16:31 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1404778-391-1.aspx[quote][b]anthony.green (1/9/2013)[/b][hr]Yep that is correct.ROLLBACK IMMEDIATE, will force any connections in that databases to stop executing and rollback what they where doing if they where in the middle of a transactionSINGLE_USER does just what it says, after rollback, change DB to single user so that only 1 SPID can connect to it[/quote]That's AwesomeThanksso Now the process will finish in only one step, I don't need to do in step-1 and step-2 for kill all connections and set database to single user mode.Wed, 09 Jan 2013 08:14:24 GMTyogi123http://www.sqlservercentral.com/Forums/Topic1404778-391-1.aspxYep that is correct.ROLLBACK IMMEDIATE, will force any connections in that databases to stop executing and rollback what they where doing if they where in the middle of a transactionSINGLE_USER does just what it says, after rollback, change DB to single user so that only 1 SPID can connect to itWed, 09 Jan 2013 08:11:50 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1404778-391-1.aspx[quote][b]anthony.green (1/9/2013)[/b][hr]The above will do exactly the same as what you are trying to do, just it is the new way of doing it.[/quote]so it meansALTER DATABASE myDB SET SINGLE_USER WITH ROLLBACK IMMEDIATEcommand will kill all spids as well as set database into SINGLE_USER ?Wed, 09 Jan 2013 08:09:23 GMTyogi123http://www.sqlservercentral.com/Forums/Topic1404778-391-1.aspxThe above will do exactly the same as what you are trying to do, just it is the new way of doing it.Wed, 09 Jan 2013 07:58:41 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1404778-391-1.aspx[quote][b]anthony.green (1/9/2013)[/b][hr]ALTER DATABASE myDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE[/quote]Thank for Your replybut job is failed to step-1 as killed spidsI need to resolve that.Wed, 09 Jan 2013 07:57:31 GMTyogi123http://www.sqlservercentral.com/Forums/Topic1404778-391-1.aspxALTER DATABASE myDB SET SINGLE_USER WITH ROLLBACK IMMEDIATEWed, 09 Jan 2013 07:55:36 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1404778-391-1.aspx[quote][b]anthony.green (1/9/2013)[/b][hr]What version of SQL are you running?What is the purpose of the task at hand, do you just want to boot people out or are you setting the DB into single user mode?sysprocesses and sysdatabases are also depreciated[/quote]The purpose of task is , restore databaseso first need to kill all spids for that database and thenchange database into single user modeso right now, job is failed to step-1(kill all spids)Wed, 09 Jan 2013 07:53:12 GMTyogi123http://www.sqlservercentral.com/Forums/Topic1404778-391-1.aspxWhat version of SQL are you running?What is the purpose of the task at hand, do you just want to boot people out or are you setting the DB into single user mode?sysprocesses and sysdatabases are also depreciatedWed, 09 Jan 2013 07:33:16 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1404778-391-1.aspx[quote][b]anthony.green (1/9/2013)[/b][hr]sp_dboption is a depreciated feature, user alter database insteadALTER DATABASE myDB SET SINGLE_USER[/quote]Thanks for replyIt was my bad actual script is as below[code="sql"]declare @spid varchar(2)declare @execstat nvarchar(10)declare kill_cursor cursorfor select spid from sysprocesses sp, sysdatabases sdb where sp.dbid=sdb.dbid and name='database_name'open kill_cursor Fetch next from kill_cursor into @spid while (@@fetch_status =0) Begin set @execstat ='kill'+' '+@spid exec sp_executesql @execstat fetch next from kill_cursor into @spid EndClose kill_cursordeallocate kill_cursor[/code]Please help me into thisWed, 09 Jan 2013 07:30:27 GMTyogi123http://www.sqlservercentral.com/Forums/Topic1404778-391-1.aspxsp_dboption is a depreciated feature, user alter database insteadALTER DATABASE myDB SET SINGLE_USERWed, 09 Jan 2013 07:27:37 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1404778-391-1.aspxHelloI have one up and running job since few years but today i am facing one strange error.here is error logExecuted as user: ABC\XYZ. Incorrect syntax near '*'. [SQLSTATE 42000] (Error 102). The step failed.In that step there is no where '*'here is command in the step[code="sql"]declare @spid varchar(2)declare @execstat nvarchar(10)declare kill_cursor cursorfor select spid from sysprocesses sp, sysdatabases sdb where sp.dbid=sdb.dbid and name='database_name'open kill_cursor Fetch next from kill_cursor into @spid while (@@fetch_status =0) Begin set @execstat ='kill'+' '+@spid exec sp_executesql @execstat fetch next from kill_cursor into @spid EndClose kill_cursordeallocate kill_cursor[/code]Please help me how to resolve.ThanksWed, 09 Jan 2013 07:24:27 GMTyogi123