Error 166 Failed to delete old entries in local history

• 
  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.'
  

  Dan B February 06, 2017 05:42

  - 0 +
• 
  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 February 08, 2017 11:58

  - 0 +
• 
  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!

  jgoemaat February 08, 2017 21:09

  - 0 +

Add comment

Please sign in to leave a comment.