0

When I run this statement in a query window as my own user account, the restore completes in 40 minutes. When I run it as a step in an agent job it runs in over 60 minutes. In the agent job, the step executes as NT SERVICE\SQLSERVERAGENT. What could cause the time difference?

ALTER DATABASE [Example] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [Example] FROM  DISK = N'D:\Example.bak' WITH  FILE = 1, 
MOVE N'Example' TO N'D:\Example.MDF',  
MOVE N'Example_log' TO N'D:\Example_log.LDF',  
REPLACE, STATS = 10

ALTER DATABASE [Example] SET MULTI_USER
GO
Improve this question
2
  • 1
    Are you testing the run times at the same time of day? Hypothesis that your agent job is on a schedule to run at night and it's competing for resources at that time.
    – Jonathan Fite
    Nov 20 at 19:23
  • 1
    @JonathanFite Or perhaps even waiting on rolling back another process's query because of the SET SINGLE_USER WITH ROLLBACK IMMEDIATE, depending on what's running at that time.
    – J.D.
    Nov 20 at 23:03

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.