SQL Backup Warning 166

Kallandor

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.

petey

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.

petey

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).

Kallandor

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.

Kallandor

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’

petey

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.