How can we help you today? How can we help you today?

Warning 164: Failed to delete backup entries in msdb tables

Hi
all my jobs on TRN are in failure and all for the same reason.
Warning 164: Failed to delete backup entries in msdb tables: Error of conversion of the type of data varchar in datetime. it is the first time that I have this problem since the installation.
I have sqlbackup 4.6 pro

the log of a job:
SQL Backup log file
12/09/2008 10:03:01: Backing up Bo (transaction log) to:
D:\Backup\TRN\Bo\Bo_20080912_100301.sqb

12/09/2008 10:03:01: BACKUP LOG [Bo] TO DISK = 'D:\Backup\TRN\Bo\Bo_20080912_100301.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', ERASEFILES = 1, MAILTO_ONERROR = 'systeme.reseau@domaine.com', COMPRESSION = 3, THREADS = 1

12/09/2008 10:03:06: Backup data size : 120,688 MB
12/09/2008 10:03:06: Compressed data size: 28,604 MB
12/09/2008 10:03:06: Compression rate : 76,30%

15217 pages traitées pour la base de données 'Bo', fichier 'Bo_Journal' sur le fichier 1.
BACKUP LOG a traité avec succès 15217 pages en 5.100 secondes (24.441 Mo/s).

12/09/2008 10:03:24: Deleting old backup file: D:\Backup\TRN\Bo\Bo_20080911_100301.sqb
12/09/2008 10:03:24: SQL Backup process ended.

12/09/2008 10:03:25: Warning 164: Failed to delete backup entries in msdb tables: Erreur de conversion du type de données varchar en datetime.
.
12/09/2008 10:03:25: Mail sent successfully to: systeme.reseau@domaine.com

Thanks
bandito
0

Comments

3 comments

  • petey
    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?
    petey
    0
  • bandito
    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
    bandito
    0
  • petey
    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 = &#91;&lt;AUTO&gt;&#93;"'
    

    and send the trace output to me. Thanks.
    petey
    0

Add comment

Please sign in to leave a comment.