SQL Error 913 on SQL Backup of database(s) with new db's

rick.sheeleyrick.sheeley Posts: 32
edited February 7, 2011 6:20PM in SQL Backup Previous Versions
Two databases, EZCAP_LHCHPLSR and EZCAP_LHNCHPLS were added to the database in question on 07/20/2010.

We are receiving the following error on nightly backups since 07/21:

-SQL "BACKUP DATABASES EXCLUDE 'master,model,msdb' TO DISK = 'E:\SQLBackup\<AUTO>' WITH ERASEFILES_ATSTART = 1, MAILTO_ONERROR = 'sqldba@bannerhealth.com', DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, COMPRESSION = 3 "

ERRORS AND WARNINGS



7/27/2010 12:01:38 AM: Backing up EZCAP_LHCHPLSR (full database) to:
7/27/2010 12:01:38 AM: E:\SQLBackup\FULL_(local)_EZCAP_LHCHPLSR_20100727_000138.sqb

7/27/2010 12:01:38 AM: Deleting old backup file: E:\SQLBackup\FULL_(local)_EZCAP_LHCHPLSR_20100725_000155.sqb
7/27/2010 12:01:38 AM: BACKUP DATABASE [EZCAP_LHCHPLSR] TO VIRTUAL_DEVICE = 'SQLBACKUP_45836A10-1255-46F8-AD33-728A09FCCD35' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (EZCAP_LHCHPLSR), 7/27/2010 12:01:38 AM', DESCRIPTION = N'Backup on 7/27/2010 12:01:38 AM Server: PHX00417 Database: EZCAP_LHCHPLSR', FORMAT

7/27/2010 12:02:42 AM: Warning 167: Failed to get database size from server.
7/27/2010 12:02:42 AM: SQL error 913: Could not find database ID 46. Database may not be activated yet or may be in transition.
7/27/2010 12:02:42 AM: Backup data size : 1.793 GB
7/27/2010 12:02:42 AM: Compressed data size: 282.916 MB
7/27/2010 12:02:42 AM: Compression rate : 84.59%

7/27/2010 12:02:42 AM: Processed 234904 pages for database 'EZCAP_LHCHPLSR', file 'EZCAP_LHCHPLSR' on file 1.
7/27/2010 12:02:42 AM: Processed 1 pages for database 'EZCAP_LHCHPLSR', file 'EZCAP_LHCHPLSR_log' on file 1.
7/27/2010 12:02:42 AM: BACKUP DATABASE successfully processed 234905 pages in 63.361 seconds (30.370 MB/sec).


7/27/2010 12:02:42 AM: Backing up EZCAP_LHNCHPLS (full database) to:
7/27/2010 12:02:42 AM: E:\SQLBackup\FULL_(local)_EZCAP_LHNCHPLS_20100727_000242.sqb

7/27/2010 12:02:42 AM: Deleting old backup file: E:\SQLBackup\FULL_(local)_EZCAP_LHNCHPLS_20100725_000239.sqb
7/27/2010 12:02:42 AM: BACKUP DATABASE [EZCAP_LHNCHPLS] TO VIRTUAL_DEVICE = 'SQLBACKUP_B05A00F2-3F40-43FF-AC64-B9D63CD4FA26' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (EZCAP_LHNCHPLS), 7/27/2010 12:02:42 AM', DESCRIPTION = N'Backup on 7/27/2010 12:02:42 AM Server: PHX00417 Database: EZCAP_LHNCHPLS', FORMAT

7/27/2010 12:03:32 AM: Database size : 2.996 GB
7/27/2010 12:03:32 AM: Compressed data size: 342.729 MB
7/27/2010 12:03:32 AM: Compression rate : 88.83%

7/27/2010 12:03:32 AM: Processed 343264 pages for database 'EZCAP_LHNCHPLS', file 'EZCAP_LHNCHPLS_dat' on file 1.
7/27/2010 12:03:32 AM: Processed 1 pages for database 'EZCAP_LHNCHPLS', file 'EZCAP_LHNCHPLS_log' on file 1.
7/27/2010 12:03:32 AM: BACKUP DATABASE successfully processed 343265 pages in 50.040 seconds (56.195 MB/sec).

EZCAP_LHNCHPLS backs up just fine, but not EZCAP_LHCHPLSR.The job completes, but still errors out overall. This was not happening as of 07/20, the day these databases were added. The errors seem to be a direct result of adding the two new databases. I have confirmed that DBID (46) does not exist. Still, shouldn't SQLBackup just be picking up these new backups automatically and backing them up?

Any ideas or thoughts on this?
Eric (Rick) Sheeley, Sr. SQL/Oracle DBA
Sacramento, CA Cell: 602.540.6750
"Those are my principles, and if you don't like them... well, I have others." - Groucho Marx

Comments

  • peteypetey Posts: 2,358 New member
    The backup is still usable, it's just that SQL Backup could not get the database size for its internal statistics. The query it's using to retrieve the database size in pages (if you're using 6.4) is as follows:
    SELECT SUM&#40;CAST&#40;size AS BIGINT&#41;&#41; size FROM &#91;EZCAP_LHCHPLSR&#93;..sysfiles
    

    Can you run this query without errors using Query Analyzer/SSMS?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Yes. Works fine, Peter...... results are correct.
    Eric (Rick) Sheeley, Sr. SQL/Oracle DBA
    Sacramento, CA Cell: 602.540.6750
    "Those are my principles, and if you don't like them... well, I have others." - Groucho Marx
  • peteypetey Posts: 2,358 New member
    Could you please use Profiler to trace the command that SQL Backup is using to get the database size, just to check if it's the same query?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • I am now having the same problem. Last week I had to drop a database and then restore it from an earlier backup. This caused the DB ID to change.

    I have checked how the size is calculated with profiler: SELECT SUM(size) size FROM [Tiger]..sysfiles

    On this Server, I am using SQL Backup 6.4 and SQL Server 2000.

    Is there a solution to this problem?

    Details below:

    ERRORS AND WARNINGS


    7/02/2011 10:11:06 AM: Backing up Tiger (full database) to:
    7/02/2011 10:11:06 AM: D:\FleetPlanner Backups\RedGate\Tiger 20110207_01.sqb
    7/02/2011 10:11:06 AM: D:\FleetPlanner Backups\RedGate\Tiger 20110207_02.sqb
    7/02/2011 10:11:06 AM: D:\FleetPlanner Backups\RedGate\Tiger 20110207_03.sqb

    7/02/2011 10:11:09 AM: BACKUP DATABASE [Tiger] TO VIRTUAL_DEVICE = 'SQLBACKUP_2D00985B-9743-4EBC-BF47-E19CCF1A6E7E', VIRTUAL_DEVICE = 'SQLBACKUP_2D00985B-9743-4EBC-BF47-E19CCF1A6E7E01', VIRTUAL_DEVICE = 'SQLBACKUP_2D00985B-9743-4EBC-BF47-E19CCF1A6E7E02' WITH BUFFERCOUNT = 18, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (Tiger), 7/02/2011 10:11:06 AM', DESCRIPTION = N'Backup on 7/02/2011 10:11:06 AM Server: FYBRSN14 Database: Tiger', FORMAT

    7/02/2011 10:11:14 AM: Warning 167: Failed to get database size from server.
    7/02/2011 10:11:14 AM: SQL error 913: Could not find database ID 52. Database may not be activated yet or may be in transition.
    7/02/2011 10:11:14 AM: Backup data size : 114.125 MB
    7/02/2011 10:11:14 AM: Compressed data size: 23.407 MB
    7/02/2011 10:11:14 AM: Compression rate : 79.49%

    7/02/2011 10:11:14 AM: Processed 13472 pages for database 'Tiger', file 'Tiger' on file 1.
    7/02/2011 10:11:14 AM: Processed 1 pages for database 'Tiger', file 'Tiger_log' on file 1.
    7/02/2011 10:11:14 AM: BACKUP DATABASE successfully processed 13473 pages in 4.255 seconds (25.937 MB/sec).
  • peteypetey Posts: 2,358 New member
    If you were to run that query in QA/SSMS, does it result in the same error?

    Would it be possible for you to free the procedure cache (DBCC FREEPROCCACHE) to see if it clears up the error?

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Firstly, I must apologise for forgetting my signature block.

    Running the Select statement in QA returned the correct result.

    After running the DBCC FREEPROCCACHE the backup is now working from the automated job and manually via SQL Backup 6.

    I will see what happens when the job runs early tomorrow morning.

    Thank you for your help and quick reply.

    Scott Koorey
    APT Business Solutions
    Canberra ACT, Australia
Sign In or Register to comment.