Background...

We recently came across an interesting issue when performing some planned work for a client. In order to move the databases' data files to a different disk, we had to temporarily remove the databases from the availability group (AG) but had an issue when trying to join the secondary databases back after the file move was complete...

The switcheroo plan

Due to size of the databases and the limited maintenance window, the plan was to effectively swap the databases' files using pre-staged standby databases: ahead of the work we created the pre-staged databases, restored from the live databases' backups, with the data files in the new disks and left them in stand-by mode. On the day, we just had to offline the live databases by taking a tail log backup and restore all remaining backups to the stand-by databases. Then take the instance offline, swap the drive letters, bring the databases online and add them back to the AG.

The work as it happened...

For this demo I used the [AdventureWorks2019] database on my own test environment. There was no need to pre-stage the databases or do any disk swap (just going to pretend I did) as the below was sufficient enough to replicate the issue:

1. First thing first, I removed the database from the AG
   
2. Then I tried to take a tail log backup and bring the database offline but failed as the database was in use (oops!).
   
   
   
   During the work for the client, it was impossible for us to stop all the sessions coming from the applications. The only way was by stopping the APP servers, which was something out of our control. 
   
3. The workaround was to set the used databases into single user mode and take the tail log backup again. So I did with my demo database:
   
   
   
   
   
   
   
4. Let's pretend at this point I've already done the restores and the disk switcheroo. So, I brought the instance and the database database back online.
   
   
   
   The database came back in single user mode as it was before I took the tail log backup. Therefore I had to set it back to multi-user before adding it into the AG.
   
   
   
   
5. The beefiest part of the work was behind me and everything I had left was to join the secondary database back into the AG. 
   
   In order to do so, I have simply restored the tail log on the secondary and tried to join the DB back into the AG but something unexpected happened:
   
   
   
   
   
   
   And below is the error:
   

   Database "AdventureWorks2019" database is in single user mode which is incompatible with participating in database mirroring or in an availability group. Set database to multi-user mode, and retry the operation.

   
   That error took me a bit by surprise as I could not figure out how a database in the restoring state could also be in single user mode. I could not add the database in the AG because, according to SQL Server, it was in single and at the same time I could not change the database accessibility mode to multi user as it was in the restoring state! So, I checked the status of the database:
   
   
   
   And yes! It was indeed in single user mode, even if it was in the restoring state. I then had a look the log backup header and noticed the "IsSingleUser" property of the last log backup taken/restored:
   
   
   
   Because the tail log was taken whilst the primary database was in single user mode, the secondary database went straight into single user mode once I restored that log backup, exactly has it happened on the primary, even though the database is left in the restoring state.
   
   
6. Once the problem was clear, fixing the issue was pretty straightforward: all I had to do was to take another log backup on the primary and restore it on the other side:
   
   
   
   The ‘IsSingleUser’ column in the backup header was 0 
   
   
   
   I restored that last log
   
   
   
   
7. Before joining the database back into the AG, I checked the status of database again and it was in multi user mode:
   
   
   
   
   
8. Then joining the database back into the AG worked just fine:
   
   

Conclusions

If you can, try to avoid taking a tail log backup when the database is in the single user mode: where possible stop your application services and ensure there is no activity at all on the databases before you start. Then if you really have to set them to single user, remember to take another log backup after the database is back online in multi user mode and restore that log before joining the secondary database into the AG.