Hi. SQL Server Expert, I have a issue when I backup/restore a database from production server to dev server, it is hung on single mode and a deadlock occur the restore process cannot finished. The database stay in single user mode and spid <50 cannot to disconnect it. Any one know what is happen? This sql agent job run for long time didn't have any issue until this month, it happen for couple times. I need restart service to bring database back to multiple user mode and finished restore.
when i try to bring it to multiple user mode it give this error message:
Msg 1205, Level 13, State 68, Line 1
Transaction (Process ID xxx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.