Warning 164: Failed to delete backup entries in msdb tables

bandito

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:
\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: \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

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?

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

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 = [<AUTO>]"'

and send the trace output to me. Thanks.