search

olahallengren / sql-server-maintenance-solution

SQL Server Maintenance Solution
https://ola.hallengren.com
MIT License
2.91k stars 756 forks source link

Default Backup Jobs Are Not Marked As Completed on SQL Server 2019 on Linux #471

Closed marshallwp closed 3 years ago

marshallwp commented 3 years ago

Description of the issue Following a fresh system reboot, the first backup operation initiated by the SQL Agent succeeds. All subsequent runs complete all parts of the backup operation, but do not return upon completion, making the SQL Agent believe the backup operation is running endlessly.

This issue can be seen on the SQL Agent's sqlagent.out logfile. Affected Jobs start, but are never marked as completed via a 184 message, such as example below. 2021-01-05 15:52:41 - ? [184] Job completion for DatabaseBackup - USER_DATABASES - LOG is being logged to sysjobhistory The only workaround I've found is to manually stop the SQL Agent Job, after which the job is marked as completed.

As only one instance of a job can be running at a time, this blocks backup jobs as they are 'already running'.

All other operations included in the Maintenance Solution do not suffer from this issue.

SQL Server version and edition Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64) Sep 23 2020 16:03:08 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Linux (Oracle Linux Server 7.9)

Version of the script SchemaName ObjectName Version Checksum
dbo CommandExecute 2020-12-31 18:58:56 415773187
dbo DatabaseBackup 2020-12-31 18:58:56 763543791
dbo DatabaseIntegrityCheck 2020-12-31 18:58:56 -1311663888
dbo IndexOptimize 2020-12-31 18:58:56 -1087133113

What command are you executing? Any of the default backup Jobs included in the MaintenanceSolution.sql script.

marshallwp commented 3 years ago

Further research shows this was caused by a low CPU core count. Increasing the number of CPU cores available to SQL Server resolved the issue.