Warning 167

eilanddeilandd Posts: 44
edited November 12, 2008 8:56PM in SQL Backup Previous Versions
We had a need to restore a database on our production cluster using a Redgate backup from one of our test environments. The process caused a change in the dbid of the given database, not a name change. We then began getting errors from the scheduled transaction log backups

10/12/2007 11:10:04 AM: Warning 167: Failed to get database size from server. 10/12/2007 11:10:04 AM: SQL error 913: Could not find database ID 19. Database may not be activated yet or may be in transition.

The database is valid, functional and in full recovery mode. We then tried a full backup through the gui but received the same message. The microsoft native backups are working.

We are using version 5.1.0.2781

Any help would be greatly appreciated.

Comments

  • It appears that the RedGate backups are actually working. However, they are being reported as failed. My suspicion is that the RedGate backup history is the culprit. Is it possible to clear the RedGate backup history for a single database?
  • peteypetey Posts: 2,358 New member
    SQL Backup uses the following query to retrieve the dabase size:

    SELECT SUM(size) size FROM [<database name>]..sysfiles

    Does running this command in Query Analyzer/SSMS raise any errors?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Sorry for the delayed response.

    The query returns the appropriate results, no errors.
  • peteypetey Posts: 2,358 New member
    Could you pls run a test backup together with SQL Profiler and trace the sequence of commands leading up to the error? You can filter on the ApplicationName column with the value 'SQBCoreService.exe'. Try running the last few SELECT commands manually and check if the same error turns up.

    With regards to your earlier question, SQL Backup does not retrieve any data from its backup history database during a backup. If you want to clear this database, you can use the 'sqbdata' extended stored procedure e.g.
    EXEC master..sqbdata 'DELETE FROM backuplog WHERE backup_id IN &#40;SELECT id FROM backuphistory WHERE dbname = ''pubs''&#41;'
    
    EXEC master..sqbdata 'DELETE FROM backupfiles WHERE backup_id IN &#40;SELECT id FROM backuphistory WHERE dbname = ''pubs''&#41;'
    
    EXEC master..sqbdata 'DELETE FROM backuphistory WHERE dbname = ''pubs'''
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Peter;

    Thanks for your reply. It seems that time has healed this issue. We are no longer receiving the 913 error.
  • I too am getting this error.

    11/12/2008 5:00:05 PM: Warning 167: Failed to get database size from server.
    11/12/2008 5:00:05 PM: SQL error 913: Could not find database ID 38. Database may not be activated yet or may be in transition.

    The backups are fine, but the warning is anoying. This started happening when the database was deleted and a new database with the same name was created. The database in question now has an ID of 35 not 38. So how do I fix this?

    BTW, I did run the script posted earlier to clear out the history, but the problem remains.

    EXEC master..sqbdata 'DELETE FROM backuplog WHERE backup_id IN (SELECT id FROM backuphistory WHERE dbname = ''MyDBName'')'

    EXEC master..sqbdata 'DELETE FROM backupfiles WHERE backup_id IN (SELECT id FROM backuphistory WHERE dbname = ''MyDBName'')'

    EXEC master..sqbdata 'DELETE FROM backuphistory WHERE dbname = ''MyDBName'''
  • Update:

    I just recycled the SQL Server service and now I no longer get the warning.

    For future reference, is there a better way?
Sign In or Register to comment.