Warning 164: Failed to delete backup entries in msdb tables

Comments

• petey Redgate › Posts: 2,358 New member

  September 15, 2008 1:17AM

  Your backups are fine. It's just a post-backup task that's failing, and a warning (164) is raised.
  
  It looks like SQL Backup formatted the cut-off date wrongly in the script to delete old entries in the msdb tables. Could you please run Profiler, and send me the script that SQL Backup is using to delete the old entries?

  Peter Yeoh
  SQL Backup Consultant Developer
  Associate, Yohz Software
  Beyond compression - SQL Backup goodies under the hood, updated for version 8

  0 · Share on Twitter
• bandito Posts: 3

  September 15, 2008 9:35AM

  Hi petey
  
  i think it's this
  
  EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = 0x4C8DACF47C251D4094E61D3F7075398E, @step_id = 1, @sql_message_id = 50000, @sql_severity = 16, @run_status = 0, @run_date = 20080915, @run_time = 152221, @run_duration = 8, @operator_id_emailed = 0, @operator_id_netsent = 0, @operator_id_paged = 0, @retries_attempted = 0, @message = N'Exécuté en tant qu''utilisateur : AUTORITE NT\SYSTEM. SQL Backup job failed with exitcode: 164 SQL error code: 0 [SQLSTATE 42000] (erreur 50000). L''étape a échoué.'
  -- or this
  UPDATE msdb.dbo.sysjobservers SET last_run_date = 20080915, last_run_time = 152221, last_run_outcome = 0, last_outcome_message = N'Le travail a échoué. Le travail a été appelé par Utilisateur DOMAINE\administrateur. La dernière étape exécutée est l''étape 1 (Step 1).', last_run_duration = 8 WHERE (job_id = 0x4C8DACF47C251D4094E61D3F7075398E) AND (server_id = 0)
  -- or this
  EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = 0x4C8DACF47C251D4094E61D3F7075398E, @step_id = 0, @sql_message_id = 0, @sql_severity = 0, @run_status = 0, @run_date = 20080915, @run_time = 152221, @run_duration = 8, @operator_id_emailed = 0, @operator_id_netsent = 0, @operator_id_paged = 0, @retries_attempted = 0, @message = N'Le travail a échoué. Le travail a été appelé par Utilisateur  DOMAINE\administrateur. La dernière étape exécutée est l''étape 1 (Step 1).'
  --
  
  => script : msdb.dbo.sp_sqlagent_log_jobhistory
  
  CREATE PROCEDURE sp_sqlagent_log_jobhistory
  @job_id UNIQUEIDENTIFIER,
  @step_id INT,
  @sql_message_id INT = 0,
  @sql_severity INT = 0,
  @message NVARCHAR(1024) = NULL,
  @run_status INT, -- SQLAGENT_EXEC_X code
  @run_date INT,
  @run_time INT,
  @run_duration INT,
  @operator_id_emailed INT = 0,
  @operator_id_netsent INT = 0,
  @operator_id_paged INT = 0,
  @retries_attempted INT,
  @server NVARCHAR(30) = NULL
  AS
  BEGIN
  DECLARE @retval INT
  DECLARE @job_id_as_char VARCHAR(36)
  DECLARE @step_id_as_char VARCHAR(10)
  DECLARE @operator_id_as_char VARCHAR(10)
  DECLARE @step_name sysname
  DECLARE @error_severity INT
  
  SET NOCOUNT ON
  
  IF (@server IS NULL) OR (UPPER(@server) = '(LOCAL)')
  SELECT @server = UPPER(CONVERT(NVARCHAR(30), SERVERPROPERTY('ServerName')))
  
  -- Check authority (only SQLServerAgent can add a history entry for a job)
  EXECUTE @retval = sp_verify_jobproc_caller @job_id = @job_id, @program_name = N'SQLAgent%'
  IF (@retval <> 0)
  RETURN(@retval)
  
  -- NOTE: We raise all errors as informational (sev 0) to prevent SQLServerAgent from caching
  -- the operation (if it fails) since if the operation will never run successfully we
  -- don't want it to hang around in the operation cache.
  SELECT @error_severity = 0
  
  -- Check job_id
  IF (NOT EXISTS (SELECT *
  FROM msdb.dbo.sysjobs_view
  WHERE (job_id = @job_id)))
  BEGIN
  SELECT @job_id_as_char = CONVERT(VARCHAR(36), @job_id)
  RAISERROR(14262, @error_severity, -1, 'Job', @job_id_as_char)
  RETURN(1) -- Failure
  END
  
  -- Check step id
  IF (@step_id <> 0) -- 0 means 'for the whole job'
  BEGIN
  SELECT @step_name = step_name
  FROM msdb.dbo.sysjobsteps
  WHERE (job_id = @job_id)
  AND (step_id = @step_id)
  IF (@step_name IS NULL)
  BEGIN
  SELECT @step_id_as_char = CONVERT(VARCHAR, @step_id)
  RAISERROR(14262, @error_severity, -1, '@step_id', @step_id_as_char)
  RETURN(1) -- Failure
  END
  END
  ELSE
  SELECT @step_name = FORMATMESSAGE(14570)
  
  -- Check run_status
  IF (@run_status NOT IN (0, 1, 2, 3, 4, 5)) -- SQLAGENT_EXEC_X code
  BEGIN
  RAISERROR(14266, @error_severity, -1, '@run_status', '0, 1, 2, 3, 4, 5')
  RETURN(1) -- Failure
  END
  
  -- Check run_date
  EXECUTE @retval = sp_verify_job_date @run_date, '@run_date', 10
  IF (@retval <> 0)
  RETURN(1) -- Failure
  
  -- Check run_time
  EXECUTE @retval = sp_verify_job_time @run_time, '@run_time', 10
  IF (@retval <> 0)
  RETURN(1) -- Failure
  
  -- Check operator_id_emailed
  IF (@operator_id_emailed <> 0)
  BEGIN
  IF (NOT EXISTS (SELECT *
  FROM msdb.dbo.sysoperators
  WHERE (id = @operator_id_emailed)))
  BEGIN
  SELECT @operator_id_as_char = CONVERT(VARCHAR, @operator_id_emailed)
  RAISERROR(14262, @error_severity, -1, '@operator_id_emailed', @operator_id_as_char)
  RETURN(1) -- Failure
  END
  END
  
  -- Check operator_id_netsent
  IF (@operator_id_netsent <> 0)
  BEGIN
  IF (NOT EXISTS (SELECT *
  FROM msdb.dbo.sysoperators
  WHERE (id = @operator_id_netsent)))
  BEGIN
  SELECT @operator_id_as_char = CONVERT(VARCHAR, @operator_id_netsent)
  RAISERROR(14262, @error_severity, -1, '@operator_id_netsent', @operator_id_as_char)
  RETURN(1) -- Failure
  END
  END
  
  -- Check operator_id_paged
  IF (@operator_id_paged <> 0)
  BEGIN
  IF (NOT EXISTS (SELECT *
  FROM msdb.dbo.sysoperators
  WHERE (id = @operator_id_paged)))
  BEGIN
  SELECT @operator_id_as_char = CONVERT(VARCHAR, @operator_id_paged)
  RAISERROR(14262, @error_severity, -1, '@operator_id_paged', @operator_id_as_char)
  RETURN(1) -- Failure
  END
  END
  
  -- Insert the history row
  INSERT INTO msdb.dbo.sysjobhistory
  (job_id,
  step_id,
  step_name,
  sql_message_id,
  sql_severity,
  message,
  run_status,
  run_date,
  run_time,
  run_duration,
  operator_id_emailed,
  operator_id_netsent,
  operator_id_paged,
  retries_attempted,
  server)
  VALUES (@job_id,
  @step_id,
  @step_name,
  @sql_message_id,
  @sql_severity,
  @message,
  @run_status,
  @run_date,
  @run_time,
  @run_duration,
  @operator_id_emailed,
  @operator_id_netsent,
  @operator_id_paged,
  @retries_attempted,
  @server)
  
  -- Special handling of replication jobs
  DECLARE @job_name sysname
  DECLARE @category_id int
  SELECT @job_name = name, @category_id = category_id from msdb.dbo.sysjobs
  where job_id = @job_id
  -- If misc. replication job, then update global replication status table
  IF @category_id IN (11, 12, 16, 17, 18)
  BEGIN
  -- Nothing can be done if this fails, so don't worry about the return code
  EXECUTE master.dbo.sp_MSupdate_replication_status
  @publisher = '',
  @publisher_db = '',
  @publication = '',
  @publication_type = -1,
  @agent_type = 5,
  @agent_name = @job_name,
  @status = @run_status
  END
  -- If replicatio agents (snapshot, logreader, distribution, merge, and queuereader
  -- and the step has been canceled and if we are at the distributor.
  IF @category_id in (10,13,14,15,19) and @run_status = 3 and
  object_id('MSdistributiondbs') is not null
  BEGIN
  -- Get the database
  DECLARE @database sysname
  SELECT @database = database_name from sysjobsteps where job_id = @job_id and
  lower(subsystem) in (N'distribution', N'logreader','snapshot',N'merge',
  N'queuereader')
  -- If the database is a distribution database
  IF EXISTS (select * from MSdistributiondbs where name = @database)
  BEGIN
  DECLARE @proc nvarchar(500)
  SELECT @proc = quotename(@database) + N'.dbo.sp_MSlog_agent_cancel'
  EXEC @proc @job_id = @job_id, @category_id = @category_id,
  @message = @message
  END
  END
  
  -- Delete any history rows that are over the registry-defined limits
  EXECUTE msdb.dbo.sp_jobhistory_row_limiter @job_id
  
  @error) -- 0 means success
  END
  
  GO
  --
  
  Thanx

  0 · Share on Twitter
• petey Redgate › Posts: 2,358 New member

  September 16, 2008 4:52AM

  That's not the cause of the error.
  
  Could you please run Profiler, filter on the application name SQBCoreService, and run a test backup e.g.
  

  EXEC master..sqlbackup '-sql "BACKUP DATABASE model TO DISK = [<AUTO>]"'
  

  
  and send the trace output to me. Thanks.

  Peter Yeoh
  SQL Backup Consultant Developer
  Associate, Yohz Software
  Beyond compression - SQL Backup goodies under the hood, updated for version 8

  0 · Share on Twitter