Sql-Server

SQL Server 完整性檢查錯誤

  • July 3, 2020

我已經在我的一個SQL 2012 伺服器上實施了 Ola 的 SQL Server 完整性檢查，並創建了一個 SQL 代理作業來執行它。

每次我執行它都會失敗，但是當我手動執行下面的程式碼時它執行良好。

我還為作業創建了一個輸出文件，顯示完整性檢查確實在我的所有數據庫上執行。

execute [dbo].[DatabaseIntegrityCheck]
@Databases = 'ALL_DATABASES'

失敗步驟的輸出在這裡，不包括顯示我的數據庫名稱的所有文本：

Date                    22/06/2020 14:11:26
Log                     Job History (Integrity check)

Step ID                1
Server                 SQL-2012
Job Name               Integrity check
Step Name              DatabaseIntegrityCheck
Duration               00:05:37
Sql Severity           16
Sql Message ID         50000
Operator Emailed           
Operator Net sent          
Operator Paged 
Retries Attempted      0

The step failed.

有什麼想法嗎？

似乎 SQL Server 代理將報告作業失敗，其中步驟遇到嚴重性不是 0、1 或 10 的錯誤（請參見下面的展示）。我手頭沒有 2012 實例 - 使用 SQL Server 2016 進行了測試。在您的情況下，出現嚴重性為 16 的損壞錯誤 (8956)。作業執行顯示失敗應該是意料之中的。我懷疑基於快速搜尋結果（像這樣），錯誤 8956 之前會出現錯誤 8952。 我建議診斷/解決損壞原因。

額外細節：

在您的評論中，您提到記錄了錯誤 8956。這是嚴重級別 16 的錯誤：

sys.messages 查詢 message_id = 8956 的螢幕截圖

關於我之前關於 SQL Server 代理作業失敗的評論 - 這是由 DatabaseIntegrityCheck 呼叫的 CommandExecute 的相關位：

DECLARE @sp_executesql nvarchar(max) = QUOTENAME(@DatabaseContext) + '.sys.sp_executesql'

.
.
.

IF @Mode = 1 AND @Execute = 'Y'
BEGIN
  EXECUTE @sp_executesql @stmt = @Command
  SET @Error = @@ERROR
  SET @ReturnCode = @Error
END

.
.
.

展示：

因此，為了證明如果步驟遇到嚴重 16 錯誤，作業將被報告失敗，可以創建一個如下所示的儲存過程，觸發嚴重 16 錯誤並從代理作業呼叫 -

CREATE PROCEDURE sp_TestDupObject
AS
BEGIN
   DECLARE 
         @Error INT = 0
       , @sp_executesql nvarchar(max) = QUOTENAME(DB_NAME()) + '.sys.sp_executesql'
       , @SQL NVARCHAR(512) =
   'CREATE TABLE #Table(Column1 INT)

   --Will fail with Msg 2714, Level 16 (There is already an object named ''#table'' in the database)
   CREATE TABLE #Table(Column1 INT)'

   EXECUTE @sp_executesql @stmt = @SQL
   SET @Error = @@ERROR

   RETURN @Error
END

下面是使用 RAISERROR 迭代嚴重性 0-18 的更全面的展示。首先，顯示嚴重性為 0、1 和 10 的作業成功的結果截圖；所有其他人的失敗： 在此處輸入圖像描述

--Create the job using default options
USE [msdb]
GO
DECLARE @jobId BINARY(16)
   , @CurrentUser SYSNAME = SUSER_SNAME()
EXEC  msdb.dbo.sp_add_job @job_name=N'TestAgentSevTolerance', 
       @enabled=1,
       @category_name=N'[Uncategorized (Local)]', 
       @owner_login_name= @CurrentUser, @job_id = @jobId OUTPUT
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'TestAgentSevTolerance', @server_name = @@SERVERNAME
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'TestAgentSevTolerance', @step_name=N'Raiserror', 
       @step_id=1, 
       @cmdexec_success_code=0, 
       @on_success_action=1, 
       @on_fail_action=2, 
       @retry_attempts=0, 
       @retry_interval=0, 
       @os_run_priority=0, @subsystem=N'TSQL', 
       @command=N'DECLARE 
     @ErrorString NVARCHAR(50)
   , @CurrentSev TINYINT
   , @CurrentTestRun TINYINT

SELECT 
     @CurrentTestRun = MAX(TestRun)
FROM AgentTest

SELECT 
   @CurrentSev = MAX(Severity)
FROM AgentTest
WHERE TestRun = @CurrentTestRun

SET @ErrorString = ''This is a test error: Severity '' + CAST(@CurrentSev AS CHAR(2)) + '' State 1''

RAISERROR(@ErrorString, @CurrentSev, 1) -- <<<<<<< This is the heart of what is being demonstrated

UPDATE AgentTest
SET JobCompleted = 1
WHERE   TestRun = @CurrentTestRun
   AND Severity =  @CurrentSev', 
       @database_name=N'SQLAgentErrorSevToleranceTest', 
       @flags=0
GO
USE [msdb]
GO

EXEC msdb.dbo.sp_update_job @job_name=N'TestAgentSevTolerance', 
       @enabled=1, 
       @start_step_id=1
GO

USE [SQLAgentErrorSevToleranceTest]
GO
--Creating as proc so executing (in loop if desired) separately from rest of script easy
--Just iterates from MinSev to MaxSev calling job, which will in turn call RAISERROR with each sev NumberRuns times
CREATE OR ALTER PROCEDURE usp_RunTest
(@NumberRuns TINYINT = 1
, @MinSev TINYINT = 0
, @MaxSev TINYINT = 18)--(Side note - Error sev > 18 can only be specified by sysadmin and must use WITH LOG)
AS
BEGIN
   --Sanity checking parameter values since, even though this is only for demo purposes, putting online...
   IF(@MaxSev > 18 OR @MinSev > 18)
   BEGIN
       RAISERROR('Please specify int value from 0 to 18. Error sev > 18 can only be specified by sysadmin and must use WITH LOG', 0, 1)
       RETURN
   END
   IF(@MinSev < 0 OR @MaxSev < 0)
   BEGIN
       RAISERROR('Negative numbers are invalid. Please specify int value from 0 to 18.', 0, 1)
       RETURN
   END
   IF(@MinSev > @MaxSev)
   BEGIN
       RAISERROR('The @MinSev must be <= @MaxSev. Both must be between 0 to 18 inclusive.', 0, 1)
       RETURN
   END

   DECLARE 
         @Severity TINYINT = @MinSev
       , @RetCode INT = 0
       , @BusyRetry TINYINT = 0
       , @TestRun TINYINT = 0
       , @PrintString VARCHAR(128)

   WHILE(@NumberRuns > 0)
   BEGIN
       SELECT 
             @TestRun = CASE 
               WHEN EXISTS (
                   SELECT 1 
                   FROM [AgentTest]
                   WHERE TestRun IS NOT NULL) 
               THEN MAX(TestRun) + 1 
               ELSE 1 
             END 
       FROM [AgentTest]

       SELECT
           @Severity = CASE 
               WHEN EXISTS (
                   SELECT 1 
                   FROM [AgentTest] 
                   WHERE   TestRun = @TestRun
                       AND Severity IS NOT NULL) 
               THEN MAX(Severity) 
               ELSE @MinSev 
             END 
       FROM [AgentTest]
       WHERE TestRun = @TestRun

       WHILE (@Severity <= @MaxSev)
       BEGIN

           INSERT INTO [AgentTest](TestRun, Severity)
           VALUES(@TestRun, @Severity)

           SET @RetCode = 1
           SET @BusyRetry = 2

           WHILE (@RetCode <> 0 AND @BusyRetry > 0)
           BEGIN
               EXEC @RetCode = [msdb].[dbo].sp_start_job @job_name = N'TestAgentSevTolerance'
               SET @BusyRetry -= 1

               IF(@RetCode <> 0)
               BEGIN
                   
                   IF(@BusyRetry = 0)
                   BEGIN
                       SET @PrintString = 'Retried job execution twice... moving on...'
                       RAISERROR(@PrintString, 0, 1) WITH NOWAIT
                       BREAK
                   END
                   ELSE
                       --Despite below loop, still encountered error 22022 without this wait (request to run job refused because already running)
                       SET @PrintString = 'WAITING FOR PREVIOUS JOB EXECUTION TO COMPLETE... CURRENT SEVERITY ' + CAST(@Severity AS CHAR(2))
                       RAISERROR(@PrintString, 0, 1) WITH NOWAIT
                       WAITFOR DELAY '00:00:01'
               END
               ELSE
               BEGIN
                   WHILE EXISTS(
                       SELECT 1
                       FROM [msdb].[dbo].sysjobactivity activity
                           JOIN [msdb].[dbo].sysjobs jobs
                               ON activity.job_id = jobs.job_id
                       WHERE start_execution_date IS NOT NULL
                           AND stop_execution_date IS NULL
                           AND jobs.name = N'TestAgentSevTolerance'
                       )
                   BEGIN
                       SET @PrintString = 'WAITING FOR PREVIOUS JOB EXECUTION TO COMPLETE... CURRENT SEVERITY ' + CAST(@Severity AS CHAR(2))
                       RAISERROR(@PrintString, 0, 1) WITH NOWAIT
                       WAITFOR DELAY '00:00:01.00'
                   END
               END
           END

           --Despite below loop, still encountered error 22022 without this wait (request to run job refused because already running)
           --WAITFOR DELAY '00:00:01'

           UPDATE [AgentTest]
           SET JobFailedToStart = @RetCode
           WHERE Severity = @Severity
               AND TestRun = @TestRun

           SET @Severity += 1          
       END

       SET @NumberRuns -= 1
   END
END
GO

EXEC usp_RunTest @MinSev = 0, @MaxSev = 18, @NumberRuns = 3

SELECT 
   sql_message_id
   , sql_severity
   , CASE run_status
       WHEN 0 THEN 'Failed'
       WHEN 1 THEN 'Succeeded'
       WHEN 2 THEN 'Retry'
       WHEN 3 THEN 'Canceled'
       WHEN 4 THEN 'In Progress'
     END [run_status]
   , message
FROM [msdb].[dbo].[sysjobhistory] hist
   JOIN [msdb].[dbo].[sysjobs] job ON job.job_id = hist.job_id
WHERE job.name = N'TestAgentSevTolerance'
   AND step_name = N'Raiserror'
   --Uncomment below two lines to make more obvious the successful job executions
   --AND run_status = 1
   --AND sql_severity NOT IN (0,10)
ORDER BY run_time DESC

--Uncomment and execute below to clean up

   --EXEC [msdb].[dbo].sp_delete_job @job_name = N'TestAgentSevTolerance'

   --USE [master]
   --GO

   --DROP DATABASE [SQLAgentErrorSevToleranceTest]

引用自：https://dba.stackexchange.com/questions/270089

相關問答

Sql-Server

安排每周代理作業的下次執行日期

  • January 4, 2022
檢視問答
Sql-Server

SQL Server 2012 作業歷史記錄失去

  • January 11, 2021
檢視問答
Sql-Server

授予 AD 組編輯所有 MSSQL 代理作業的權限

  • July 25, 2020
檢視問答
Sql-Server

dbcc 因無法繼續執行而失敗，因為…總是有每個 db 的狀態更改事件

  • February 14, 2020
檢視問答
Sql-Server

作為訪問連結伺服器的 SQL 登錄執行的作業的“目前安全上下文不受信任”

  • November 26, 2019
檢視問答
Sql-Server

輔助非同步副本上的 DBCC CheckDb

  • November 1, 2019
檢視問答