Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

sql job fails --- need help Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2013 8:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 1, 2014 12:13 PM
Points: 115, Visits: 342
anthony.green (1/9/2013)
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 transaction

SINGLE_USER does just what it says, after rollback, change DB to single user so that only 1 SPID can connect to it




That's Awesome

Thanks

so 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.

Post #1404819
Posted Wednesday, January 9, 2013 8:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 5,218, Visits: 5,072
Ensure 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.

So


ALTER DATABASE myDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE myDB FROM DISK = 'xxxxxxxxxxxxxxxxxxxxxx'
GO

In the same job step.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1404822
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse