SQL Backup Warning 166

KallandorKallandor Posts: 5
edited January 22, 2008 9:49PM in SQL Backup Previous Versions
I started getting the warning:

Warning 166: Failed to delete old entries in local history tables: Error running ExecFast command: Floating point division by zero.

I know that this is caused by some sort of bad data in the SQL Server Compact Edition database that SQL Backup uses. And I know that the data file is located in the Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Data\ directory. What I don’t know is how to access this data, or even if I can what it is that I am looking for to fix this issue.

Comments

  • peteypetey Posts: 2,358 New member
    You can get a trace of the commands that are being ran by turning on a debug flag within the SQL Backup Agent service. To do this, you need to start the service with the -sqbdebug flag.

    Open the Windows Service Manager. Look for the SQL Backup Agent service. If no backups or restores are running, stop the service. On the 'Start parameters' value, enter '-sqbdebug' (without the quotes). Now click on 'Start'.

    A log file is generated in the folder where the service is located, named SQBCoreService_log.txt. Run a backup that results in the warning. Now stop the service, close the properties page and start the service normally (without the sqbdebug parameter).

    In the resulting log file, there should be a record of the command that caused the error. Let us know which is it.

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • peteypetey Posts: 2,358 New member
    You can access the SQL Server Compact database using SQL Server Management Studio. Select 'SQL Server Mobile' for server type, select the file and click 'Connect'.

    If you are using SQL Server 2000 and would prefer not to install Management Studio, you can use Compact Query, a free native-code IDE to access the database. You can download it from here (http://www.sqlinspect.com/downloads.html).
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Ok, I read the log, found the commands that (I thought) were causing the issue. I was able to execute these commands on the database myself without any errors. I do not know how this “division by 0” error is happening. But it still is an issue.
  • I believe this is the statement that is causing an issue:

    SELECT TOP 1 a.type, a.backup_set_uuid, a.first_lsn, a.last_lsn, a.checkpoint_lsn, a.database_backup_lsn, a.media_set_id, c.name
    FROM msdb..backupset a INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id INNER JOIN master..sysdatabases c
    ON a.database_name COLLATE DATABASE_DEFAULT = c.name COLLATE DATABASE_DEFAULT
    WHERE b.physical_device_name = 'SQLBACKUP_D19A5B27-2930-4457-8414-64D9F94BDE6B' ORDER BY a.media_set_id DESC

    The device name 'SQLBACKUP_D19A5B27-2930-4457-8414-64D9F94BDE6B' does not exist. In fact it is not even close to what is in that field. This field holds values like
    ‘[UNC Path]\[backup type]_[sql instance]_[database name]_[date]_[time].sqb’
  • peteypetey Posts: 2,358 New member
    Did the error (warning 166) occur when you turn on the sqbdebug flag? Is so, could you please send me the log?

    Warning code 166 points to an error with the query in the local data store. The query you pointed out (SELECT TOP 1 a.type, a.backup), has to do with the backup tables in the msdb database. That query does not return anything because a subsequent query updates the physical_device_name to the actual backup file names.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.