Nonqualified transactions are being rolled back.

_{Monday, February 9, 2009 4:12 PM | 1 vote}

We have a nightly SQL job that have the following commands.

ALTER DATABASE [MyDB]SET SINGLE_USER
WITH ROLLBACK IMMEDIATE 

Most of the time, it runs without any problem. However, sometimes, we got the following error:
 
Executed as user: domain\domainuser.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%. [SQLSTATE 01000] (Message 5060)  Database 'MyDB' is already open and can only have one user at a time. [SQLSTATE 42000] (Error 924).  The step failed.

Is there anything we can do?

Thanks,

_{Wednesday, February 11, 2009 2:25 AM ✅Answered}

It really seems like some part of this had been issued as part of a new session. I don't know if beginning a transaction would help here or even work because of what we are asking SQL to do.

Do all of those tasks happen in one step or multiple steps?

Also do you have a similar job running at the same time?

Basically it sounds like one of the steps had a new spid or session_id and that session was blocked from doing anything because the database was in single user mode and the spid that put it in single_user mode is different than the spid accessing it when that error is thrown.

I wonder if there was some hiccup in a step or if a prior execution had been interrupted and that caused an issue.

Also in your steps, what happens after you rename the database? Do you have users use that "MyTgtDB" while the data load happens on the restored copy?

When the process ran the other day and gave that error mesage was it run differently? Did it run manually, run at a time it normally wouldn't run at, etc?
Thanks! Mike Walsh www.straightpathsql.com/blog

_{Monday, February 9, 2009 5:40 PM}

I am a bit confused. Where do you get that error?

The command you are showing is a command that makes a database revert to single user mode, rolls back any in progress work regardless of where it is and the only session allowed to connect is that same session that issued the alter.

What else is happening in the job? Which connection are you getting that error message in?

And most importantly, why are you doing this to your database each night? It seems like a brute force way to attempt to do whatever it is that you are trying to do and it means that any work in progress in a transaction at that time is rolled back and lost. Just a bit confused and hoping there is more to the job?
Thanks! Mike Walsh www.straightpathsql.com/blog

_{Monday, February 9, 2009 7:28 PM}

Thanks Mike,
Let me try to explain to you.
Every night, we copy the backup file from Prod and restore it to a staging SQL server. We also create some customized objects on the restored database. Then is will be used as a read-only database to feed a complicated and time consuming ETL process. This staging server datebase is also a data source for couple of reports.
Some times, we need to move the data during business hours. However, we wanted to eliminate the down time during database restore. So we use restore --> reneme technique to reduce the down time.

Here are all the SQL statements in one of the tasks in our SQL Job.
--========================
        ALTER DATABASE [MyDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

        ALTER DATABASE [MyDB] MODIFY NAME = [MyTgtDB]

        ALTER DATABASE [MyTgtDB] SET MULTI_USER
--========================

This job has been running for more than two years without problem. However, it failed last night and I don't know why. Maybe I should add begin tran and commit tran.

Here is the error message for that task
--========================
        Executed as user: domain\domainuser. 
        Nonqualified transactions are being rolled back. Estimated rollback completion: 100%. [SQLSTATE 01000] (Message 5060)
        Database 'MyDB' is already open and can only have one user at a time. [SQLSTATE 42000] (Error 924).  The step failed.
--========================

A side note: Because our Prod database doesn't have primary key, we can't do transaction replication, log shipping...

_{Wednesday, July 3, 2019 3:40 AM}

Try to restart the server first before attempt to fix the database