Sql-Server

還原期間的事務死鎖

  • February 15, 2019

我正在使用 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)

引用自:https://dba.stackexchange.com/questions/228845