1

We have a weird problem on one of our SQL Servers (2016 standard). At about 4 am we have a couple of restore jobs starting. Let's say, that databases A, B and C are in a "restoring state". More or less at the same time there is a job, that executes a stored procedure in the database D. D is not in the process of restoration. Databases A, B, C and D are totally independent from each other. The stored procedure in D does not depend on any objects from A, B nor C.

The problem is, the job executing in the D database fails after about 3 seconds with the following message:

Database 'xxx' cannot be opened. It is in the middle of a restore. [SQLSTATE 42000] (Error 927). The step failed.

where 'xxx' is more or less randomly A, B or C. Again the procedure running in the D database has (or should have!) absolutely nothing in common with A, B, C.

I'm totally stumped by this. Does anybody know how to pin down the problem?

-- EDIT --

Addresing your questions:

Did you double/triple checked that their is no dependency ?

Quadruplechecked! There are no direct dependencies.

Job runs as "sa". "sa" account is disabled. I enclose the job script below. @database_name=N'D' is not in the process of a restore (anyways the error message never points to "D"). EXECUTE [job].[xxx] executes a prcedure in a [job] schema. The procedure does nothing fancy. I'm 100% sure it does not connect to any of A,B and C databases.

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'xxx', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'***', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'xxx', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=3, 
        @retry_interval=10, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'EXECUTE [job].[xxx] ', 
        @database_name=N'D', 
        @flags=0
  • 4
    Did you double/triple checked that their is no dependency ? – Shanky Oct 3 at 8:53
  • 4
    Can you add the stored procedure, a picture of the job step context, .... As much information as possible regarding the proc & proc call. – Randi Vertongen Oct 3 at 9:09
  • I would set up a statement level extended event trace and run the stored procedure to see what statement is failing. That might a give a clue if it is the same statement each time. – SqlWorldWide Oct 3 at 12:29
  • What default database does the user connecting to the server running this statement use? What does the code look like that executes the stored procedure? – John Eisbrener Oct 3 at 16:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.