還原期間的事務死鎖
我正在使用 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)