Recently I came across a situation where dbmirror system process running for a database for which mirroring is unconfigured.
The requirement was to restore a mirrored database with a backup from another server. So we first removed mirroring on the target database, then when we restored the database it failed with
Exclusive access could not be obtained because the database is in use
error. Running sp_who revealed that dbmirror process was using the database. I tried to kill that process but SQL Server will not do that saying "System Process cannot be killed". Strange thing was the spid for this was 70, that threw my understanding of system process having spid < 50.
Next I tried dropping the database but it also failed because the database is in use. Luckily Detach with kill existing connection option worked. Now sp_who did not show any dbmirror process using the detached database. However when I restored the database it failed again with the same error.
Exclusive access could not be obtained because the database is in use
Running sp_who showed dbmirror back again. This really got stranger and stranger. I then detached the database again and restored to a different name, this was successful but dbmirror process came back again on the new database with new name.
I queried database_mirroring table, it did not list the target database as mirrored. So for some reason SQL Server thought I still had a mirrored database. Now I restored the same backup file again to the original database name and this time it was successful.
I can't say what kind of bug it is, but it seems SQL Server remembered the database_id for the mirrored database and maintained dbmirror process for that. When I dropped and created a new database, it reused the database_id and it showed up in dbmirror process.
At least I could restore my database even if I had to create a dummy database to reuse original database_id. I hope this zombie dbmirror process disappears after SQL Server restart and I can drop the dummy database.
The requirement was to restore a mirrored database with a backup from another server. So we first removed mirroring on the target database, then when we restored the database it failed with
Exclusive access could not be obtained because the database is in use
error. Running sp_who revealed that dbmirror process was using the database. I tried to kill that process but SQL Server will not do that saying "System Process cannot be killed". Strange thing was the spid for this was 70, that threw my understanding of system process having spid < 50.
Next I tried dropping the database but it also failed because the database is in use. Luckily Detach with kill existing connection option worked. Now sp_who did not show any dbmirror process using the detached database. However when I restored the database it failed again with the same error.
Exclusive access could not be obtained because the database is in use
Running sp_who showed dbmirror back again. This really got stranger and stranger. I then detached the database again and restored to a different name, this was successful but dbmirror process came back again on the new database with new name.
I queried database_mirroring table, it did not list the target database as mirrored. So for some reason SQL Server thought I still had a mirrored database. Now I restored the same backup file again to the original database name and this time it was successful.
I can't say what kind of bug it is, but it seems SQL Server remembered the database_id for the mirrored database and maintained dbmirror process for that. When I dropped and created a new database, it reused the database_id and it showed up in dbmirror process.
At least I could restore my database even if I had to create a dummy database to reuse original database_id. I hope this zombie dbmirror process disappears after SQL Server restart and I can drop the dummy database.