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
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. Commented Nov 20, 2023 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.
    Commented Nov 20, 2023 at 23:03

0

Your Answer

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

Browse other questions tagged or ask your own question.