Sql-Server
還原期間的事務死鎖
我正在使用 PS 腳本來恢復數據庫備份，並且我有以下從 PS Invoke-SQLcmd 命令執行的 SQL 腳本：
USE master GO ALTER DATABASE mydatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO ALTER DATABASE mydatabase SET OFFLINE WITH ROLLBACK IMMEDIATE GO RESTORE DATABASE mydatabase FROM DISK = 'Z:\bak\mydatabase.bak' WITH REPLACE GO ALTER DATABASE mydatabase SET RECOVERY SIMPLE GO ALTER DATABASE mydatabase SET ONLINE GO ALTER DATABASE mydatabase SET MULTI_USER WITH ROLLBACK IMMEDIATE GO如您所見，我正在將數據庫切換到單一模式，甚至在還原操作之前使其離線。但是有時我會收到如下錯誤：
Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.我不是數據庫死鎖方面的專家，也不知道如何保護我的腳本以防止此類錯誤。您知道當 db 處於單模式和離線狀態時，某些東西會阻止恢復操作嗎？
更新更改的腳本如下：
USE master GO ALTER DATABASE mydatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO RESTORE DATABASE mydatabase FROM DISK = 'Z:\bak\mydatabase.bak' WITH REPLACE, RECOVERY GO ALTER DATABASE mydatabase SET RECOVERY SIMPLE GO ALTER DATABASE mydatabase SET MULTI_USER WITH ROLLBACK IMMEDIATE GO我打開
dbcc trace並得到以下日誌：Date,Source,Severity,Message 02/05/2019 07:34:06,spid37s,Unknown,inputbuf 02/05/2019 07:34:06,spid37s,Unknown,executionStack 02/05/2019 07:34:06,spid37s,Unknown,process id=process2bdbf8ca8 taskpriority=0 logused=10000 waitresource=DATABASE: 6:0 waittime=1317 schedulerid=3 kpid=10904 status=suspended spid=53 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=1900-01-01T00:00:00 lastbatchcompleted=1900-01-01T00:00:00 lastattention=1900-01-01T00:00:00 clientapp=Microsoft JDBC Driver for SQL Server hostname=APPSERVER hostpid=0 loginname=testuser isolationlevel=read committed (2) xactid=0 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056 02/05/2019 07:34:06,spid37s,Unknown,process-list 02/05/2019 07:34:06,spid37s,Unknown,deadlock victim=process2bdbf8ca8 02/05/2019 07:34:06,spid37s,Unknown,deadlock-list 02/05/2019 07:34:06,spid7s,Unknown,waiter id=process2bdef24e8 mode=X requestType=wait 02/05/2019 07:34:06,spid7s,Unknown,waiter-list 02/05/2019 07:34:06,spid7s,Unknown,owner id=process2f64604e8 mode=X requestType=wait 02/05/2019 07:34:06,spid7s,Unknown,owner id=process2f64604e8 mode=S 02/05/2019 07:34:06,spid7s,Unknown,owner-list 02/05/2019 07:34:06,spid7s,Unknown,databaselock subresource=FULL dbid=6 dbname=unknown lockPartition=0 id=lock2bcae4680 mode=S 02/05/2019 07:34:06,spid7s,Unknown,waiter id=process2ff031848 mode=X requestType=wait 02/05/2019 07:34:06,spid7s,Unknown,waiter-list 02/05/2019 07:34:06,spid7s,Unknown,owner id=process2f64604e8 mode=X requestType=wait 02/05/2019 07:34:06,spid7s,Unknown,owner id=process2f64604e8 mode=S 02/05/2019 07:34:06,spid7s,Unknown,owner-list 02/05/2019 07:34:06,spid7s,Unknown,databaselock subresource=FULL dbid=6 dbname=unknown lockPartition=0 id=lock2bcae4680 mode=S 02/05/2019 07:34:06,spid7s,Unknown,waiter id=process2f4f5eca8 mode=X requestType=wait 02/05/2019 07:34:06,spid7s,Unknown,waiter-list 02/05/2019 07:34:06,spid7s,Unknown,owner id=process2f64604e8 mode=X requestType=wait 02/05/2019 07:34:06,spid7s,Unknown,owner id=process2f64604e8 mode=S 02/05/2019 07:34:06,spid7s,Unknown,owner-list 02/05/2019 07:34:06,spid7s,Unknown,databaselock subresource=FULL dbid=6 dbname=unknown lockPartition=0 id=lock2bcae4680 mode=S 02/05/2019 07:34:06,spid7s,Unknown,waiter id=process2bdbf8ca8 mode=X requestType=wait 02/05/2019 07:34:06,spid7s,Unknown,waiter-list 02/05/2019 07:34:06,spid7s,Unknown,owner id=process2f64604e8 mode=X requestType=wait 02/05/2019 07:34:06,spid7s,Unknown,owner id=process2f64604e8 mode=S 02/05/2019 07:34:06,spid7s,Unknown,owner-list 02/05/2019 07:34:06,spid7s,Unknown,databaselock subresource=FULL dbid=6 dbname=unknown lockPartition=0 id=lock2bcae4680 mode=S 02/05/2019 07:34:06,spid7s,Unknown,waiter id=process2bb01a8c8 mode=X requestType=wait 02/05/2019 07:34:06,spid7s,Unknown,waiter-list 02/05/2019 07:34:06,spid7s,Unknown,owner id=process2f64604e8 mode=X requestType=wait 02/05/2019 07:34:06,spid7s,Unknown,owner id=process2f64604e8 mode=S 02/05/2019 07:34:06,spid7s,Unknown,owner-list 02/05/2019 07:34:06,spid7s,Unknown,databaselock subresource=FULL dbid=6 dbname=unknown lockPartition=0 id=lock2bcae4680 mode=S 02/05/2019 07:34:06,spid7s,Unknown,waiter id=process2f4f0c8c8 mode=X requestType=wait 02/05/2019 07:34:06,spid7s,Unknown,waiter-list 02/05/2019 07:34:06,spid7s,Unknown,owner id=process2f64604e8 mode=X requestType=wait 02/05/2019 07:34:06,spid7s,Unknown,owner id=process2f64604e8 mode=S 02/05/2019 07:34:06,spid7s,Unknown,owner-list 02/05/2019 07:34:06,spid7s,Unknown,databaselock subresource=FULL dbid=6 dbname=unknown lockPartition=0 id=lock2bcae4680 mode=S 02/05/2019 07:34:06,spid7s,Unknown,waiter id=process2f64604e8 mode=X requestType=wait 02/05/2019 07:34:06,spid7s,Unknown,waiter-list 02/05/2019 07:34:06,spid7s,Unknown,owner id=process2f4f0c8c8 mode=X requestType=wait 02/05/2019 07:34:06,spid7s,Unknown,owner id=process2bdbf8ca8 mode=X requestType=wait 02/05/2019 07:34:06,spid7s,Unknown,owner id=process2bdbf8ca8 mode=S 02/05/2019 07:34:06,spid7s,Unknown,owner id=process2f4f0c8c8 mode=S 02/05/2019 07:34:06,spid7s,Unknown,owner-list 02/05/2019 07:34:06,spid7s,Unknown,databaselock subresource=FULL dbid=6 dbname=unknown lockPartition=0 id=lock2bcae4680 mode=S 02/05/2019 07:34:06,spid7s,Unknown,resource-list 02/05/2019 07:34:06,spid7s,Unknown,WITH REPLACE 02/05/2019 07:34:06,spid7s,Unknown,FROM DISK = 'Z:\bak\mydatabase.bak' 02/05/2019 07:34:06,spid7s,Unknown,RESTORE DATABASE mydatabase 02/05/2019 07:34:06,spid7s,Unknown,inputbuf 02/05/2019 07:34:06,spid7s,Unknown,unknown 02/05/2019 07:34:06,spid7s,Unknown,frame procname=unknown line=2 stmtstart=4 stmtend=326 sqlhandle=0x010001006bf7d11c709f1b7f0200000000000000000000000000000000000000000000000000000000000000 02/05/2019 07:34:06,spid7s,Unknown,executionStack 02/05/2019 07:34:06,spid7s,Unknown,process id=process2bdef24e8 taskpriority=0 logused=10000 waitresource=DATABASE: 6:0 waittime=128 schedulerid=1 kpid=10208 status=suspended spid=58 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2019-02-05T07:34:06.133 lastbatchcompleted=2019-02-05T07:34:06.107 lastattention=1900-01-01T00:00:00.107 clientapp=.Net SqlClient Data Provider hostname=BUILD_SERVER hostpid=3268 loginname=mydomain\admin isolationlevel=read committed (2) xactid=0 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056 02/05/2019 07:34:06,spid7s,Unknown,inputbuf 02/05/2019 07:34:06,spid7s,Unknown,executionStack 02/05/2019 07:34:06,spid7s,Unknown,process id=process2ff031848 taskpriority=0 logused=10000 waitresource=DATABASE: 6:0 waittime=1875 schedulerid=4 kpid=7928 status=background spid=37 sbid=0 ecid=0 priority=0 trancount=0 02/05/2019 07:34:06,spid7s,Unknown,inputbuf 02/05/2019 07:34:06,spid7s,Unknown,executionStack 02/05/2019 07:34:06,spid7s,Unknown,process id=process2f4f5eca8 taskpriority=0 logused=10000 waitresource=DATABASE: 6:0 waittime=625 schedulerid=4 kpid=14180 status=background spid=39 sbid=0 ecid=0 priority=0 trancount=0 02/05/2019 07:34:06,spid7s,Unknown,inputbuf 02/05/2019 07:34:06,spid7s,Unknown,executionStack 02/05/2019 07:34:06,spid7s,Unknown,process id=process2bdbf8ca8 taskpriority=0 logused=10000 waitresource=DATABASE: 6:0 waittime=1809 schedulerid=3 kpid=10904 status=suspended spid=53 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=1900-01-01T00:00:00 lastbatchcompleted=1900-01-01T00:00:00 lastattention=1900-01-01T00:00:00 clientapp=Microsoft JDBC Driver for SQL Server hostname=APPSERVER hostpid=0 loginname=testuser isolationlevel=read committed (2) xactid=0 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056 02/05/2019 07:34:06,spid7s,Unknown,inputbuf 02/05/2019 07:34:06,spid7s,Unknown,executionStack 02/05/2019 07:34:06,spid7s,Unknown,process id=process2bb01a8c8 taskpriority=0 logused=10000 waitresource=DATABASE: 6:0 waittime=625 schedulerid=3 kpid=6816 status=background spid=31 sbid=0 ecid=0 priority=0 trancount=0 02/05/2019 07:34:06,spid7s,Unknown,inputbuf 02/05/2019 07:34:06,spid7s,Unknown,executionStack 02/05/2019 07:34:06,spid7s,Unknown,process id=process2f4f0c8c8 taskpriority=0 logused=10000 waitresource=DATABASE: 6:0 waittime=273 schedulerid=2 kpid=9004 status=suspended spid=57 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=1900-01-01T00:00:00 lastbatchcompleted=1900-01-01T00:00:00 lastattention=1900-01-01T00:00:00 clientapp=Microsoft JDBC Driver for SQL Server hostname=APPSERVER hostpid=0 loginname=testuser isolationlevel=read committed (2) xactid=0 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056 02/05/2019 07:34:06,spid7s,Unknown,inputbuf 02/05/2019 07:34:06,spid7s,Unknown,executionStack 02/05/2019 07:34:06,spid7s,Unknown,process id=process2f64604e8 taskpriority=0 logused=10000 waitresource=DATABASE: 6:0 waittime=1875 schedulerid=2 kpid=11588 status=background spid=36 sbid=0 ecid=0 priority=0 trancount=0 02/05/2019 07:34:06,spid7s,Unknown,process-list 02/05/2019 07:34:06,spid7s,Unknown,deadlock victim=process2f64604e8 02/05/2019 07:34:06,spid7s,Unknown,deadlock-list 02/05/2019 07:34:06,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:34:06,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:34:04,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:34:04,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:34:04,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:34:04,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:34:04,spid22s,Unknown,waiter id=process2f64604e8 mode=X requestType=wait 02/05/2019 07:34:04,spid22s,Unknown,waiter-list 02/05/2019 07:34:04,spid22s,Unknown,owner id=process2bb01a8c8 mode=X requestType=wait 02/05/2019 07:34:04,spid22s,Unknown,owner id=process2bb01a8c8 mode=S 02/05/2019 07:34:04,spid22s,Unknown,owner-list 02/05/2019 07:34:04,spid22s,Unknown,databaselock subresource=FULL dbid=6 dbname=unknown lockPartition=0 id=lock2bcae4680 mode=U 02/05/2019 07:34:04,spid22s,Unknown,waiter id=process2ff031c28 mode=X requestType=convert 02/05/2019 07:34:04,spid22s,Unknown,waiter-list 02/05/2019 07:34:04,spid22s,Unknown,owner id=process2f64604e8 mode=X requestType=wait 02/05/2019 07:34:04,spid22s,Unknown,owner id=process2bb01a8c8 mode=X requestType=wait 02/05/2019 07:34:04,spid22s,Unknown,owner id=process2bb01a8c8 mode=S 02/05/2019 07:34:04,spid22s,Unknown,owner id=process2f64604e8 mode=S 02/05/2019 07:34:04,spid22s,Unknown,owner-list 02/05/2019 07:34:04,spid22s,Unknown,databaselock subresource=FULL dbid=6 dbname=unknown lockPartition=0 id=lock2bcae4680 mode=U 02/05/2019 07:34:04,spid22s,Unknown,waiter id=process2bb01a8c8 mode=X requestType=wait 02/05/2019 07:34:04,spid22s,Unknown,waiter-list 02/05/2019 07:34:04,spid22s,Unknown,owner id=process2ff031c28 mode=X requestType=convert 02/05/2019 07:34:04,spid22s,Unknown,owner id=process2ff031c28 mode=S 02/05/2019 07:34:04,spid22s,Unknown,owner id=process2ff031c28 mode=U 02/05/2019 07:34:04,spid22s,Unknown,owner id=process2ff031c28 mode=S 02/05/2019 07:34:04,spid22s,Unknown,owner-list 02/05/2019 07:34:04,spid22s,Unknown,databaselock subresource=FULL dbid=6 dbname=unknown lockPartition=0 id=lock2bcae4680 mode=U 02/05/2019 07:34:04,spid22s,Unknown,resource-list 02/05/2019 07:34:04,spid22s,Unknown,inputbuf 02/05/2019 07:34:04,spid22s,Unknown,executionStack 02/05/2019 07:34:04,spid22s,Unknown,process id=process2f64604e8 taskpriority=0 logused=10000 waitresource=DATABASE: 6:0 waittime=840 schedulerid=2 kpid=11588 status=suspended spid=53 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=1900-01-01T00:00:00 lastbatchcompleted=1900-01-01T00:00:00 lastattention=1900-01-01T00:00:00 clientapp=Microsoft JDBC Driver for SQL Server hostname=APPSERVER hostpid=0 loginname=testuser isolationlevel=read committed (2) xactid=0 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056 02/05/2019 07:34:04,spid22s,Unknown,WITH REPLACE 02/05/2019 07:34:04,spid22s,Unknown,FROM DISK = 'Z:\bak\mydatabase.bak' 02/05/2019 07:34:04,spid22s,Unknown,RESTORE DATABASE mydatabase 02/05/2019 07:34:04,spid22s,Unknown,inputbuf 02/05/2019 07:34:04,spid22s,Unknown,unknown 02/05/2019 07:34:04,spid22s,Unknown,frame procname=unknown line=2 stmtstart=4 stmtend=326 sqlhandle=0x010001006bf7d11c709f1b7f0200000000000000000000000000000000000000000000000000000000000000 02/05/2019 07:34:04,spid22s,Unknown,executionStack 02/05/2019 07:34:04,spid22s,Unknown,process id=process2ff031c28 taskpriority=0 logused=10000 waitresource=DATABASE: 6:0 waittime=2222 schedulerid=4 kpid=9444 status=suspended spid=76 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2019-02-05T07:33:56.687 lastbatchcompleted=2019-02-05T07:33:56.657 lastattention=1900-01-01T00:00:00.657 clientapp=.Net SqlClient Data Provider hostname=BUILD_SERVER hostpid=3268 loginname=mydomain\admin isolationlevel=read committed (2) xactid=0 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056 02/05/2019 07:34:04,spid22s,Unknown,inputbuf 02/05/2019 07:34:04,spid22s,Unknown,executionStack 02/05/2019 07:34:04,spid22s,Unknown,process id=process2bb01a8c8 taskpriority=0 logused=10000 waitresource=DATABASE: 6:0 waittime=1364 schedulerid=3 kpid=6816 status=suspended spid=57 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=1900-01-01T00:00:00 lastbatchcompleted=1900-01-01T00:00:00 lastattention=1900-01-01T00:00:00 clientapp=Microsoft JDBC Driver for SQL Server hostname=APPSERVER hostpid=0 loginname=testuser isolationlevel=read committed (2) xactid=0 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056 02/05/2019 07:34:04,spid22s,Unknown,process-list 02/05/2019 07:34:04,spid22s,Unknown,deadlock victim=process2bb01a8c8 02/05/2019 07:34:04,spid22s,Unknown,deadlock-list 02/05/2019 07:34:03,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:34:03,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:34:02,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:34:02,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:34:01,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:34:01,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:34:01,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:34:01,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:34:00,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:34:00,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:33:59,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:33:59,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:33:59,spid35s,Unknown,waiter id=process2f4f0c8c8 mode=X requestType=wait 02/05/2019 07:33:59,spid35s,Unknown,waiter-list 02/05/2019 07:33:59,spid35s,Unknown,owner id=process2bdbf8ca8 mode=X requestType=wait 02/05/2019 07:33:59,spid35s,Unknown,owner id=process2bdbf8ca8 mode=S 02/05/2019 07:33:59,spid35s,Unknown,owner-list 02/05/2019 07:33:59,spid35s,Unknown,databaselock subresource=FULL dbid=6 dbname=unknown lockPartition=0 id=lock2bcae4680 mode=S 02/05/2019 07:33:59,spid35s,Unknown,waiter id=process2bdbf8ca8 mode=X requestType=wait 02/05/2019 07:33:59,spid35s,Unknown,waiter-list 02/05/2019 07:33:59,spid35s,Unknown,owner id=process2f4f0c8c8 mode=X requestType=wait 02/05/2019 07:33:59,spid35s,Unknown,owner id=process2f4f0c8c8 mode=S 02/05/2019 07:33:59,spid35s,Unknown,owner-list 02/05/2019 07:33:59,spid35s,Unknown,databaselock subresource=FULL dbid=6 dbname=unknown lockPartition=0 id=lock2bcae4680 mode=S 02/05/2019 07:33:59,spid35s,Unknown,resource-list 02/05/2019 07:33:59,spid35s,Unknown,inputbuf 02/05/2019 07:33:59,spid35s,Unknown,executionStack 02/05/2019 07:33:59,spid35s,Unknown,process id=process2f4f0c8c8 taskpriority=0 logused=10000 waitresource=DATABASE: 6:0 waittime=461 schedulerid=2 kpid=9004 status=suspended spid=57 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=1900-01-01T00:00:00 lastbatchcompleted=1900-01-01T00:00:00 lastattention=1900-01-01T00:00:00 clientapp=Microsoft JDBC Driver for SQL Server hostname=APPSERVER hostpid=0 loginname=testuser isolationlevel=read committed (2) xactid=0 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056 02/05/2019 07:33:59,spid35s,Unknown,inputbuf 02/05/2019 07:33:59,spid35s,Unknown,executionStack 02/05/2019 07:33:59,spid35s,Unknown,process id=process2bdbf8ca8 taskpriority=0 logused=10000 waitresource=DATABASE: 6:0 waittime=461 schedulerid=3 kpid=10904 status=suspended spid=53 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=1900-01-01T00:00:00 lastbatchcompleted=1900-01-01T00:00:00 lastattention=1900-01-01T00:00:00 clientapp=Microsoft JDBC Driver for SQL Server hostname=APPSERVER hostpid=0 loginname=testuser isolationlevel=read committed (2) xactid=0 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056 02/05/2019 07:33:59,spid35s,Unknown,process-list 02/05/2019 07:33:59,spid35s,Unknown,deadlock victim=process2bdbf8ca8 02/05/2019 07:33:59,spid35s,Unknown,deadlock-list 02/05/2019 07:33:58,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:33:58,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:33:57,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:33:57,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:33:56,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:33:56,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:33:56,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:33:56,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:33:56,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:33:56,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:33:56,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:33:56,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:33:56,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:33:56,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:33:56,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:33:56,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:33:56,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:33:56,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:33:56,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:33:56,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:33:55,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:33:55,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:33:55,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:33:55,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:33:55,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:33:55,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:33:55,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:33:55,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:33:55,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:33:55,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:33:55,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:33:55,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:33:55,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:33:55,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:33:55,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:33:55,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:33:55,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:33:55,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:33:55,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:33:55,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:33:55,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:33:55,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:33:55,Logon,Unknown,Login failed for user 'testuser'. Reason: Failed to open the explicitly specified database 'mydatabase'. [CLIENT: 11.11.11.11] 02/05/2019 07:33:55,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38. 02/05/2019 07:33:53,spid76,Unknown,Setting database option SINGLE_USER to ON for database 'mydatabase'. 02/05/2019 07:33:03,spid65,Unknown,DBCC TRACEON 1222<c/> server process ID (SPID) 65. This is an informational message only; no user action is required.我粘貼了一些日誌，但我不知道實際上死鎖在哪裡。我將不勝感激任何提示。
更新 2
如果我在恢復之前重新啟動 SQL 服務，那麼問題就會消失。但幾分鐘後，我得到了同樣的錯誤和以下查詢：
SELECT [er].[session_id], [es].[program_name], 'xxx', --[est].text, DB_NAME([er].[database_id]), 'xxx', --[eqp].[query_plan], [er].[cpu_time] FROM sys.dm_exec_requests [er] INNER JOIN sys.dm_exec_sessions [es] ON [es].[session_id] = [er].[session_id] OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est] OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp] WHERE [es].[is_user_process] = 1 AND [er].[last_Wait_type] = N'SOS_SCHEDULER_YIELD' ORDER BY [er].[session_id]; GO給我：
+------------+--------------------------------------+------------------+------------------+------------------+----------+ | session_id | program_name | (No column name) | (No column name) | (No column name) | cpu_time | +------------+--------------------------------------+------------------+------------------+------------------+----------+ | 63 | Microsoft JDBC Driver for SQL Server | xxx | mydatabase | xxx | 17 | | 74 | Microsoft JDBC Driver for SQL Server | xxx | mydatabase | xxx | 316 | +------------+--------------------------------------+------------------+------------------+------------------+----------+即使我將 db 切換到單模式，一些程序怎麼可能阻塞它？
更新 3
根據評論，我向數據庫伺服器添加了以下觸發器：
USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create trigger [ddl_tr_alter_database] on all server with execute as 'sa' for alter_database as set nocount on; insert dbo.ddl_hist (tsql_) select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') GO ENABLE TRIGGER [ddl_tr_alter_database] ON ALL SERVER GO該表在兩次失敗的還原後如下所示：
+----+-------------------------+----------------+-------------------------------------------------------------------+ | id | dt | user_ | tsql_ | +----+-------------------------+----------------+-------------------------------------------------------------------+ | 1 | 2019-02-13 09:23:39.443 | mydomain\admin | ALTER DATABASE mydatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE | | 2 | 2019-02-13 09:30:45.080 | mydomain\admin | ALTER DATABASE mydatabase SET MULTI_USER WITH ROLLBACK IMMEDIATE | | 3 | 2019-02-13 10:14:52.740 | mydomain\admin | ALTER DATABASE mydatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE | | 4 | 2019-02-13 10:20:19.337 | mydomain\admin | ALTER DATABASE mydatabase SET RECOVERY SIMPLE | | 5 | 2019-02-13 10:20:19.370 | mydomain\admin | ALTER DATABASE mydatabase SET MULTI_USER WITH ROLLBACK IMMEDIATE | | 6 | 2019-02-13 10:51:34.380 | mydomain\admin | ALTER DATABASE mydatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE | | 7 | 2019-02-13 10:55:57.597 | mydomain\admin | ALTER DATABASE mydatabase SET RECOVERY SIMPLE | | 8 | 2019-02-13 10:55:57.630 | mydomain\admin | ALTER DATABASE mydatabase SET MULTI_USER WITH ROLLBACK IMMEDIATE | | 9 | 2019-02-13 12:32:55.430 | mydomain\admin | ALTER DATABASE mydatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE | +----+-------------------------+----------------+-------------------------------------------------------------------+看看發生了什麼：
║ ID = 1, 2 ║ Started script to restore database, so at first switching db to the single_mode. Script failed because of restore operation. By next 7 minutes I was trying to restart SQL Service, switch back to the multi_user mode etc. And finaly I could switch to multi_user at 2019-02-13 09:30:45.080 ║ ID = 3, 4, 5 ║ Started and finished successfully script for restoring db ║ ID = 6, 7, 8 ║ Started and finished successfully script for restoring db ║ ID = 9, 10 ║ Issue occurrs again as in ID = 1, 2更新 4
@@版本是
Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Apr 20 2015 17:29:27 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
將 db 帶入單使用者模式然後使其離線是沒有意義的。像 sql 代理這樣的後台服務可能會嘗試連接到數據庫。
如果您想獲得更多關於什麼程序陷入死鎖的詳細資訊，那麼您可以啟用
TF 1222(dbcc traceon (1222, -1)) 然後禁用它 (dbcc traceoff (1222, -1)) 或使用事件通知（連結到我的腳本）只是
ALTER DATABASE mydatabase SET OFFLINE WITH ROLLBACK IMMEDIATE，通過恢復來恢復數據庫。編輯：
我正在使用 PS 腳本來恢復數據庫備份
查看dbatools（用於自動化數據庫開發和管理的社區驅動的 PowerShell 模組）。你可以在github上查看程式碼。通過這種方式，您可以學習並做出回饋。
您可以使用以下程式碼來擷取
ALTER DATABASE應用程序伺服器啟動的語句：use master; create table dbo.ddl_hist( [id] [int] identity(1,1) not null, [dt] [datetime] null, [user_] [varchar](40) null, [tsql_] [varchar](1000) null, constraint [pk_ddl_hist] primary key clustered ([id])) go alter table dbo.ddl_hist add default (getdate()) for [dt] go alter table dbo.ddl_hist add default (original_login()) for [user_] go alter trigger ddl_tr_alter_database on all server with execute as 'sa' for alter_database as set nocount on; insert dbo.ddl_hist (tsql_) select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') go更新
如果 ddl-trigger 沒有捕捉到我們想要的東西，你可以設置
event session它會捕捉waits：CREATE EVENT SESSION [TestSession] ON SERVER ADD EVENT sqlos.wait_info( ACTION(sqlserver.client_app_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack) WHERE ([sqlserver].[client_app_name]=N'MYAPP') ) ADD TARGET package0.event_file(SET filename=N'C:\tmp1\TestSession') WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)您可以啟動/停止會話
SSMS，您可以解析跟踪以找出 X 鎖：with t as ( select cast(event_data as xml) as event_data from sys.fn_xe_file_target_read_file(N'C:\tmp1\TestSession*.xel', null, null, null) ) select e.n.value('(action[@name = "sql_text"]/value)[1]', 'nvarchar(max)'), e.n.query('.') from t cross apply t.event_data.nodes('/event[data[@name = "wait_type"]/text/text() = "LCK_M_X"]') e(n)