0

I have a SQL Server with several databases. Yesterday I started a backup one of the databases. It is still running. And I can not kill the backup process using Activity Monitor.

It has state KILLED/ROLLBACK? What does that mean?

Then I try to restart SQL Server using services, and it is stopping many hours. But it does not restart.

Is there some decision restart the server, but not physically? And what reasons of this freezing?

1

1 Answer 1

0

It has state KILLED/ROLLBACK? What does that mean?

It means the process for the backup has already been killed and it's in the process of undoing (rolling back) any changes it's made so far. Unfortunately rollbacks are mostly single threaded and generally take longer than the original process has taken so far. If you are using SQL Server 2019 or newer, it's recommended to enable Accelerated Database Recovery which is a new implementation of how rollbacks occur, making them a lot faster.

Then I try to restart SQL Server using services, and it is stopping many hours. But it does not restart.

It's likely waiting on the backup process to finish rolling back, so that it can gracefully shutdown. If it shutdown ungracefully, interrupting the rollback from putting your database back to the way it was before the process began, then your database could end up in a recovery pending state, because the changes weren't don't atomically.

Is there some decision restart the server, but not physically?

At this point all you can do is either wait for the rollback to finish and the service to restart gracefully, or you can risk putting your database in a recovery required state by restarting the server "physically".

And what reasons of this freezing?

No one can answer that question without more information from your server, such as the output of sp_WhoIsActive like Ronaldo mentioned in the comments. That tool will actually tell you how far along in the process a database backup is. I would bet if you ran it, you would see the backup isn't actually freezing, it's progressing and just taking a while for one reason or another. But without more information, I couldn't tell you what that reason is.

3
  • I'm curious, though. What is there that can be rolled back for a backup command? In general, we'll wait it out, but for a backup command, it isn't like it can UNDO the initial checkpoint, the backup file creation, reading/writing the backup data, etc. I'm all with you, J.D., but the fact that it is a backup command makes me a bit confused... Dec 11, 2022 at 10:26
  • @TiborKaraszi I agree with you, that part confuses me a little as well. I'm honestly not sure, and would have to do more research. But I agree, I don't know of anything it would actually need to rollback that's internal to the database.
    – J.D.
    Dec 11, 2022 at 13:11
  • @TiborKaraszi, as an idea it might be about a backup flag on pages which is used for a differential backup. By I'm not sure. Dec 12, 2022 at 14:27

Your Answer

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