olahallengren
commented
2 years ago Could you confirm that you have "Disconnect Users in the Database when restoring backups" for the Log Shipping database enabled?
Could you check the SQL Server error log, when it happens the next time? Do you see something about "Disconnecting users."?
Looking at the code in DatabaseBackup, I see that it is doing some queries against system - tables for a database, even when the database is in Standby mode (and before checking if a database is configured for log shipping). I will look into improving the code here.
Brianc-DBA
Description of the issue On this server, some databases exist as a log shipping (standby) destination. Log backups go every hour from 06:05-23:05. Log shipping restores start at 21:00 and end at 03:00. Most nights the 21:05 log backup only fails with the error below. It has been failing for a long time; I added the @Updateability = 'READ_WRITE' parameter in previously to try and correct that, but it still failed. I added in @ExcludeLogShippedFromLogBackup = 'Y' when that version of the script became available, with no change to the result.
SQL Server version and edition Microsoft SQL Server 2016 (SP2-CU15) (KB4577775) - 13.0.5850.14 (X64) Sep 17 2020 22:12:45 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)
Version of the script 20201231
What command are you executing? EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'H:\MSSQLBackups', @BackupType = 'LOG', @Updateability = 'READ_WRITE', @ExcludeLogShippedFromLogBackup = 'Y', @Verify = 'Y', @CleanupTime = 72, @CheckSum = 'Y', @LogToTable = 'Y'
What output are you getting? -- last bit of SQL Agent history: [SQLSTATE 01000] (Message 50000) Source: https://ola.hallengren.com [SQLSTATE 01000] (Message 50000) [SQLSTATE 01000] (Message 50000) Date and time: 2021-02-09 21:05:01 [SQLSTATE 01000] (Message 50000) Database: [name removed] [SQLSTATE 01000] (Message 50000) State: RESTORING [SQLSTATE 01000] (Message 50000) Standby: No [SQLSTATE 01000] (Message 50000) Updateability: READ_WRITE [SQLSTATE 01000] (Message 50000) User access: SINGLE_USER [SQLSTATE 01000] (Message 50000) Recovery model: FULL [SQLSTATE 01000] (Message 50000) Encrypted: No [SQLSTATE 01000] (Message 50000) Log shipping role: SECONDARY [SQLSTATE 01000] (Message 50000) Differential base LSN: 161560002373282000251 [SQLSTATE 01000] (Message 50000) Last log backup LSN: N/A [SQLSTATE 01000] (Message 50000) Last log backup: N/A [SQLSTATE 01000] (Message 50000) Log size since last log backup (MB): N/A [SQLSTATE 01000] (Message 50000) [SQLSTATE 01000] (Message 50000) Date and time: 2021-02-09 21:05:01 [SQLSTATE 01000] (Message 50000) Database: [name removed] [SQLSTATE 01000] (Message 50000) State: ONLINE [SQLSTATE 01000] (Message 50000) Standby: No [SQLSTATE 01000] (Message 50000) Updateability: READ_ONLY [SQLSTATE 01000] (Message 50000) User access: MULTI_USER [SQLSTATE 01000] (Message 50000) Recovery model: FULL [SQLSTATE 01000] (Message 50000) Encrypted: No [SQLSTATE 01000] (Message 50000) Is accessible: Yes [SQLSTATE 01000] (Message 50000) Differential base LSN: 55292000000269800038 [SQLSTATE 01000] (Message 50000) ... The step failed.
-- last part from the log file on the server: -- looks like the log shipping restore kills the backup even though we specifically exclude log shipping databases. -- seems to kill the job entirely as non-LS databases also did not get log backups. Msg 0, Sev 0, State 1: Unspecified error occurred on SQL Server. Connection may have been terminated by the server. [SQLSTATE HY000] Msg 596, Sev 21, State 1: Cannot continue the execution because the session is in the kill state. [SQLSTATE HY000]