Error 166 Failed to delete old entries in local history

jgoemaat

I just received the following warning during backups:

2/2/2017 7:03:12 PM: Warning 166: Failed to delete old entries in local history tables: Error running ExecFast command: DELETE FROM restorehistory WHERE id < 25 (Floating point division by zero).

I saw another post for Error 166, so I ran the following query per support:

EXEC master..sqbdata 'SELECT COUNT(*) FROM backuphistory'
EXEC master..sqbdata 'SELECT COUNT(*) FROM restorehistory'

I got:
129245
1

I tried to run the command manually, but still get the same "Floating point division by zero" error.

EXEC master..sqbdata 'DELETE FROM restorehistory WHERE id <= 25'

What steps do I need to do to fix this? Thanks!

Dan B

Hi jgoemaat,

Thanks for posting on the Redgate forums. You can try running the velow script to clear this out manually, please let me know if you need anything further.

----------------------------------------------------------------------------------------------------
-- Delete backup history from SQL Backup Pro's SQL Server Compact database
----------------------------------------------------------------------------------------------------
SET NOCOUNT ON
DECLARE @backup_id INT ,
    @command NVARCHAR(4000) ,
    @retain_date NVARCHAR(64) ,
    @IDList VARCHAR(2000)
 
-- number of days of history that you want to retain (currently 30 days)
SET @retain_date = GETDATE() - 30
 
-- temp table for ids we're deleting.
IF OBJECT_ID('tempdb..#DeleteTheseIDs') IS NOT NULL
    DROP TABLE #DeleteTheseIDs
CREATE TABLE #DeleteTheseIDs ( id INT )
 
-- get total from the SQL Server Compact database (this will also show an error if its corrupted)
EXEC [master]..sqbdata N'select COUNT(*) AS TotalBackupRows from backuphistory'
EXEC [master]..sqbdata N'select COUNT(*) AS TotalRestoreRows from restorehistory '
 
----------------------------------------------------------------------------------------------------
-- Delete backup history from SQL Backup Pro GUI activity cache
----------------------------------------------------------------------------------------------------
 
 --get all of the ids that we want to delete
SET @command = 'select id from backuphistory where backup_end < '''
    + @retain_date + ''''
TRUNCATE TABLE #DeleteTheseIDs
INSERT  INTO #DeleteTheseIDs
        EXEC [master]..sqbdata @command
SELECT  'Deleteing # Backup ids ' = COUNT(*)
FROM    #DeleteTheseIDs
 
 --loop until they're all deleted
WHILE EXISTS ( SELECT   1
               FROM     #DeleteTheseIDs )
    BEGIN
 
  --get next set of ids (do not get too many at a time, otherwise the list will be truncated)
        SET @IDList = ''
        SELECT TOP 150
                @IDList = @IDList + CASE WHEN @IDList = '' THEN ''
                                         ELSE ','
                                    END + CONVERT(VARCHAR(10), id)
        FROM    #DeleteTheseIDs
        ORDER BY id
 
				--delete history
				SET @command = 'delete from backupfiles where backup_id IN ('
				+ @IDList + ')'
				EXEC [master]..sqbdata @command
				SET @command = 'delete from backuplog where backup_id IN (' + @IDList
				+ ')'
				EXEC [master]..sqbdata @command
				SET @command = 'delete from backupfiles_copylist_log where copylist_id IN (SELECT id FROM backupfiles_copylist WHERE backup_id IN (' + @IDList
				+ '))'
				EXEC [master]..sqbdata @command
				SET @command = 'delete from backupfiles_copylist where backup_id IN (' + @IDList
				+ ')'
				EXEC [master]..sqbdata @command
				SET @command = 'delete from backuphistory where id IN (' + @IDList
				+ ')'
				EXEC [master]..sqbdata @command

 
  --delete from temp file
        SET @Command = 'DELETE FROM #DeleteTheseIDs where id IN (' + @IDList
            + ')'
        EXEC (@Command)   
    END
 
----------------------------------------------------------------------------------------------------
-- Delete restore history from SQL Backup Pro GUI activity cache
----------------------------------------------------------------------------------------------------
 --get all of the ids that we want to delete
SET @command = 'select id from restorehistory where restore_end < '''
    + @retain_date + ''''
TRUNCATE TABLE #DeleteTheseIDs
INSERT  INTO #DeleteTheseIDs
        EXEC [master]..sqbdata @command
SELECT  'Deleting # Restore ids ' = COUNT(*)
FROM    #DeleteTheseIDs
 
 --loop until they're all deleted
WHILE EXISTS ( SELECT   1
               FROM     #DeleteTheseIDs )
    BEGIN
 
 -- get next set of ids
        SET @IDList = ''
        SELECT TOP 200
                @IDList = @IDList + CASE WHEN @IDList = '' THEN ''
                                         ELSE ','
                                    END + CONVERT(VARCHAR(10), id)
        FROM    #DeleteTheseIDs
        ORDER BY id
 
  --delete history
        SET @command = 'delete from restorefiles where restore_id IN ('
            + @IDList + ')'
        EXEC [master]..sqbdata @command
        SET @command = 'delete from restorelog where restore_id IN ('
            + @IDList + ')'
        EXEC [master]..sqbdata @command
        SET @command = 'delete from restorehistory where id IN (' + @IDList
            + ')'
        EXEC [master]..sqbdata @command
 
  --delete from temp file
        SET @Command = 'DELETE FROM #DeleteTheseIDs where id IN (' + @IDList
            + ')'
        EXEC (@Command)   
    END
SELECT  'Done.'

jgoemaat

Thanks for the quick reply, Dan!

I ran your script, but it also generated the same error as when I tried to delete that restorehistory record manually:

Msg 1, Level 16, State 1, Line 0
Floating point division by zero

But it did delete some backup records - here is output from running it:


Therefore, my log backups are still getting the same warnings error every time.

Any more ideas on how I can fix this?

Thanks!

jgoemaat

OK, I fixed this myself after a lot of research & work.

Basically, SQL Backup still uses a V3.0 SQL Server CE format (deprecated in 2013 by Microsoft) to store backup and restore history and some other tables.

Trying to delete the troublesome record from SQL Server as obviously not working. Who knows what was triggering the division by zero error ... possibly a bug in V3.0 of SQL Server CE - there have been a lot of patches out since then?

Here are the rough steps I followed in case it helps anyone else out. My next step would have been to uninstall and reinstall (yes, pretty sad).

* stopped SQLBackup service and disabled SQL Agent log backup job * used Sysinternals ProcessExplorer (looking for "data.sdf") to make sure it was no longer being accessed * created a backup copy of "C:ProgramDataRed GateSQL BackupData(local)data.sdf" * copied "C:ProgramDataRed GateSQL BackupData(local)data.sdf" to my PC running Windows 10 (I could not get any of the SQL Server CE tools to work on several server OS machine, including the problem server) * used CompactView V1.4.12.0 (

https://sourceforge.net/projects/compactview/) to data.sdf and ran the delete stabement "delete from restorehistory" (it deleted the 1 record). NOTE: there were a plethora of 3rd party tools (http://erikej.blogspot.com/2009/04/sql- ... tools.html), most did not work on the deprecated V3.0 SQL Server CE database.
* copied from my workstation back over the original "C:ProgramDataRed GateSQL BackupData(local)data.sdf"
* started SQLBackup service
* launched the SQLBackup app to make sure it works now (it did)
* enabled SQL Agent log backup job
* made sure SQLBackup app showed logs for jobs OK now since the update (it did)




Hope this helps someone!