Some database Backups fail.

siebkesiebke Posts: 12
edited March 8, 2006 6:12AM in SQL Backup Previous Versions
It’s a very strange problem after installing version 4.0 some databases won’t backup. I receive an error on the databse IHDATA_test. The other databases work just fine with exact the same settings. The only difference is the size of the database.

The backups also work okay with version 3.2 so any ideas :?:

SQL Backup log file
1/24/2006 12:40:00 PM: Backing up BD107_test (full database) to:
\\nlstd140nas01\backupsqlsrv$\NLSTD116SQL02\Test\FULL_(local)_BD107_test_20060124 124000.sqb

1/24/2006 12:40:00 PM: BACKUP DATABASE [BD107_test] TO DISK = '\\nlstd140nas01\backupsqlsrv$\NLSTD116SQL02\Test\FULL_(local)_BD107_test_20060124 124000.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', INIT, COMPRESSION = 3, THREADS = 1

1/24/2006 12:41:42 PM: Backup data size : 4.014 GB
1/24/2006 12:41:42 PM: Compressed data size: 612.166 MB
1/24/2006 12:41:42 PM: Compression rate : 85.11%

Processed 525992 pages for database 'BD107_test', file 'GPSBD107Dat.mdf' on file 1.
Processed 1 pages for database 'BD107_test', file 'GPSBD107Log.ldf' on file 1.
BACKUP DATABASE successfully processed 525993 pages in 101.395 seconds (42.496 MB/sec).

1/24/2006 12:41:44 PM: Backing up Device_DB1 (full database) to:
\\nlstd140nas01\backupsqlsrv$\NLSTD116SQL02\Test\FULL_(local)_Device_DB1_20060124 124144.sqb

1/24/2006 12:41:44 PM: BACKUP DATABASE [Device_DB1] TO DISK = '\\nlstd140nas01\backupsqlsrv$\NLSTD116SQL02\Test\FULL_(local)_Device_DB1_20060124 124144.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', INIT, COMPRESSION = 3, THREADS = 1

1/24/2006 12:41:44 PM: Backup data size : 2.063 MB
1/24/2006 12:41:44 PM: Compressed data size: 210.500 KB
1/24/2006 12:41:44 PM: Compression rate : 90.03%

Processed 176 pages for database 'Device_DB1', file 'Device_db1' on file 1.
Processed 1 pages for database 'Device_DB1', file 'Device_db1_log' on file 1.
BACKUP DATABASE successfully processed 177 pages in 0.065 seconds (22.197 MB/sec).

1/24/2006 12:41:46 PM: Backing up IHDATA_test (full database) to:
\\nlstd140nas01\backupsqlsrv$\NLSTD116SQL02\Test\FULL_(local)_IHDATA_test_20060124 124146.sqb

1/24/2006 12:41:46 PM: BACKUP DATABASE [IHDATA_test] TO DISK = '\\nlstd140nas01\backupsqlsrv$\NLSTD116SQL02\Test\FULL_(local)_IHDATA_test_20060124 124146.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', INIT, COMPRESSION = 3, THREADS = 1

1/24/2006 12:41:49 PM: Thread 0 error:
Error 620: Error writing to backup file(s).
Process terminated unexpectedly. Error code: -2139684860
Thread 0 warning:
Warning 210: Error writing to backup file: \\nlstd140nas01\backupsqlsrv$\NLSTD116SQL02\Test\FULL_(local)_IHDATA_test_20060124 124146.sqb
Warning: System error code: (The operation completed successfully)

1/24/2006 12:41:50 PM: Server: Msg 3013
BACKUP DATABASE is terminating abnormally.
1/24/2006 12:41:50 PM: Server: Msg 3202
Write on 'SQLBACKUP_E9069AF3-80EA-4461-8995-18009B18720D' failed, status = 112. See the SQL Server error log for more details.
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    Are you backing up to a network drive directly? Do you encounter the same errors if backing up to local drives?

    I assume that you are using the BACKUP DATABASES ... syntax in 4.0. If you reverted to the 3.2 script used to backup multiple databases, do you still encounter the error?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • siebkesiebke Posts: 12
    Indeed these are backups to a network drive.

    I’ve tested it also on a local drive and this seems to work. But unfortunately I need it to backup tot a network drive because we’re short on disk space locally. Also the log I’ve posted is not the normal backup procedure, this procedure normally includes 32 databases with a total size of 150GB.

    I tried to use a script but this seems to be the exact same problem.
    · Share on Twitter
  • paulppp2004paulppp2004 Posts: 7
    We should be able to backup a DB to a remote server, shouldn't we? I'm really asking...I didn't know if that feature was available in v4.

    I am using the same syntax as the person who started this post, but remote backups seem to failed saying...

    Backing up master (full database) to:
    \\Hawk\D$\RptBackup\Condor4\master_db_(full)_200601250844.sqb

    Failed to create backup folder : \\Hawk\D$\RptBackup\Condor4SQL Backup exit code: 500

    From the source DB server, I can browse the remote network (through windows explorer) and create folder plus copy files. Also, I can do remote backups with normal SQL syntax right from the Query Analyzer console.
    But when I use red-gate v4. remote backups failed. They do work locally...not remotely however.

    Any ideas?

    Is my syntax wrong...
    You mentioned BACKUP DATABASES ... syntax in 4.0. Is there a new script from v3.2? Where may I find that?

    Thank you
    PP
    · Share on Twitter
  • paulppp2004paulppp2004 Posts: 7
    Just wanted to leave a quick note for those, who like me, just had a hard time finding the SQL backup agent.

    When you install Red-Gate v4, it creates a service called SQL Backup agent. It's under windows services. Look it up and make sure that it has access to create and copy files on the remote server.

    Thanks
    PP
    · Share on Twitter
  • tstadlertstadler Posts: 16
    We ran into that also, All our backups are done to remote storage servers for holding to go to tape. I wonder if durring the install of SQLBackup 4.x if it should prompt for what ID/PW to use or select default local account (the dafault behavior) for the service account. Would simplify some support issues I bet.
    · Share on Twitter
  • siebkesiebke Posts: 12
    Sorry but that isn't my solution.

    These things i've already checked.

    So any ideas?
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Is it always only the backup of IHDATA_test that's failing? Is it the last database in the list? When the backup fails, was the backup file created, and if so, what was it's size? Does it always fail with the same size? What's the database size?

    Can you manually back up this database?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • siebkesiebke Posts: 12
    Yes it’s always the IHDATA_test but this was just an example. Normally we have to backup approximately 31 databases. Almost all are failing. I think it has something to do with the size of the database because a small database (only a few Mb) works okay. Larger databases I think above 1 GB fail.

    The backup file is created and is always 1Kb large.

    Manually I receive the exact same error.

    Any ideas? I was very enthusiast about SQL Backup but I really need to fix this problem because it’s consuming a lot of time.

    Roger
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Manually I receive the exact same error.

    So the backup fails even if you make a backup of a single large database using Query Analyzer? If so, could you pls try backing up to another network drive? Also look in vdi.log and the SQL Server log file to see if there are additional error messages.

    Are you also using SQL Server 2000 with service pack 3 or higher applied?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • siebkesiebke Posts: 12
    Indeed the backup fails also on a single large database. I already tried to backup to another share also another server etc. but the errors remain the same.

    We’re using MS Windows 2003 Enterprise Edition with SP1 and latest patches, MS SQL Server 2000 Enterprise Edition with Service Pack 3a.

    I just tested with a database size of 4,5 GB and these are the errors. The SQL Logfile indicates an space error on the disk, but this can’t be the problem because there is plenty of free space.

    SQL Logfile:
    2006-01-31 10:47:20.29 spid57 BackupMedium::ReportIoError: write failure on backup device 'SQLBACKUP_91ED56C8-854C-4859-9AEA-A0A3FA6F7236'. Operating system error 112(There is not enough space on the disk.).
    2006-01-31 10:47:20.31 spid57 Internal I/O request 0x00D49108: Op: Write, pBuffer: 0x0B0F0000, Size: 1048576, Position: 16973824, UMS: Internal: 0x0, InternalHigh: 0x100000, Offset: 0x1000000, OffsetHigh: 0x0, m_buf: 0x0B0F0000, m_len: 1048576, m_actualBytes: 1048576, m_errcode: 112, BackupFile: SQLBACKUP_91ED56C8-854C-4859-9AEA-A0A3FA6F7236
    2006-01-31 10:47:20.31 backup BACKUP failed to complete the command sp_executesql;1
    2006-01-31 10:47:20.34 spid57 BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device 'SQLBACKUP_91ED56C8-854C-4859-9AEA-A0A3FA6F7236'. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).

    VDILog:
    2006/01/31 10:47:20 pid(1804) tid(3016)
    Error on Global\SQLBACKUP_91ED56C8-854C-4859-9AEA-A0A3FA6F7236
    Error at CVD::GetCommand: AbortDetected
    2006/01/31 10:47:20 pid(1308) tid(2160)
    Error on Global\SQLBACKUP_91ED56C8-854C-4859-9AEA-A0A3FA6F7236
    Error at TriggerAbort: invoked
    2006/01/31 10:47:20 pid(1804) tid(3016)
    Error on Global\SQLBACKUP_91ED56C8-854C-4859-9AEA-A0A3FA6F7236
    Error at TriggerAbort: invoked
    2006/01/31 10:47:20 pid(1308) tid(2160)
    Error on Global\SQLBACKUP_91ED56C8-854C-4859-9AEA-A0A3FA6F7236
    Error at SVDS::CloseDevice: Abort detected
    2006/01/31 10:47:20 pid(1308) tid(2160)
    Error on Global\SQLBACKUP_91ED56C8-854C-4859-9AEA-A0A3FA6F7236
    Error at TriggerAbort: invoked
    2006/01/31 10:47:20 pid(1308) tid(2932)
    Error on Global\SQLBACKUP_91ED56C8-854C-4859-9AEA-A0A3FA6F7236
    Error at TriggerAbort: invoked
    2006/01/31 10:47:20 pid(1804) tid(3400)
    Error on Global\SQLBACKUP_91ED56C8-854C-4859-9AEA-A0A3FA6F7236
    Error at TriggerAbort: invoked
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    What is the size of the backup file that's created? 1024 bytes?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • siebkesiebke Posts: 12
    Sorry missed that question before. :oops:

    Indeed 1024 bytes.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    And how long does the backup command run before the error occurs? Is it immediately after you issue the command, or some time after? Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • siebkesiebke Posts: 12
    It appears immediately after i issue the command. (Just a few seconds.)
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Does a backup of the database to a local drive work? I certainly hope so.

    Last year, we encountered an issue with a user where SQL Backup could not write 1 MB blocks to network paths. We never did find out the cause of it. We did create a test app which you could use. Download it from

    http://www.yohz.com/downloads/sqbnetworktest.zip

    Start it, enter the value \\nlstd140nas01\backupsqlsrv$\NLSTD116SQL02\Test in the Network path. Click 'Start test' to see if we can write blocks of a certain size to the network path. If you do hit an error, then click on the 'Test limit' button to find out the largest possible block that can be written. Perhaps this will shed some light on the problem.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • siebkesiebke Posts: 12
    The output of the Start Test:

    I’m not familiar with this tool but the output I don’t understand. Block write failed en after that The operation completed successfully?

    Creating buffered test file on network drive
    File created.
    Writing block (512)
    Block written.
    Writing block (1024)
    Block written.
    Writing block (1048576)
    Block written.
    Writing block (2097152)
    Block write failed. BytesWritten: -1
    Error 0: The operation completed successfully
    Writing block (3145728)
    Block write failed. BytesWritten: -1
    Error 0: The operation completed successfully
    Writing block (4194304)
    Block write failed. BytesWritten: -1
    Error 0: The operation completed successfully

    Comparing file size
    File size is consistent.

    Creating non-buffered test file on network drive
    File created.
    Writing block (512)
    Block written.
    Writing block (1024)
    Block written.
    Writing block (1048576)
    Block written.
    Writing block (2097152)
    Block write failed. BytesWritten: -1
    Error 0: The operation completed successfully
    Writing block (3145728)
    Block write failed. BytesWritten: -1
    Error 0: The operation completed successfully
    Writing block (4194304)
    Block write failed. BytesWritten: -1
    Error 0: The operation completed successfully

    Comparing file size
    File size is consistent.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Could you pls download and run the test again? I've changed it so that it traps errors earlier. Also, the pattern of the test now resembles a typical SQB 4 disk write i.e. 1 K first, followed by a 1 MB block.

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • siebkesiebke Posts: 12
    Hi,

    I’ve just tested with the new version off the tool. I’d also rebooted the server before running it to make sure that nothings else was hanging or running. Below the results of the test. I’d also tried to make another backup to test if it was still going wrong, this failed again. The strange thing that happened when I tried to run the test again after I tried to make a backup this test failed with error 1450.

    Results before backup:
    Creating buffered test file on network drive
    File created.
    Writing block (1024)
    Block written.
    Writing block (1048576)
    Block written.
    Writing block (2097152)
    Block written.
    Writing block (3145728)
    Block written.
    Writing block (4194304)
    Block written.

    Comparing file size
    File size is consistent.

    Creating non-buffered test file on network drive
    File created.
    Writing block (1024)
    Block written.
    Writing block (1048576)
    Block written.
    Writing block (2097152)
    Block written.
    Writing block (3145728)
    Block written.
    Writing block (4194304)
    Block written.

    Comparing file size
    File size is consistent.

    Results after backup:
    Creating buffered test file on network drive
    File created.
    Writing block (1024)
    Block written.
    Writing block (1048576)
    Block written.
    Writing block (2097152)
    Block written.
    Writing block (3145728)
    Block written.
    Writing block (4194304)
    Block write failed. BytesWritten: -1
    Error 1450: Insufficient system resources exist to complete the requested service

    Comparing file size
    File size is consistent.

    Creating non-buffered test file on network drive
    File created.
    Writing block (1024)
    Block written.
    Writing block (1048576)
    Block written.
    Writing block (2097152)
    Block written.
    Writing block (3145728)
    Block written.
    Writing block (4194304)
    Block write failed. BytesWritten: -1
    Error 1450: Insufficient system resources exist to complete the requested service

    Comparing file size
    File size is consistent.
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    And if you run the test again, does it still fail at writing 4 MB?

    Do you experience the same symptoms as described on http://support.microsoft.com/default.as ... -us;304101 i.e. a large paged kernel memory value?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • siebkesiebke Posts: 12
    I'd run the tests again but each time it fails on the same point. Only if i reboor the server en run it before i yry to make an backup it runs okay.

    I've checked and tried the issues in the technet article but whitout any luck.
    · Share on Twitter
  • siebkesiebke Posts: 12
    Any ideas?
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Sorry, none at this point. Have you tried installing SQL Backup on another server to test if the same problem occurs?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • Daniel HandleyDaniel Handley Posts: 214
    We think we have found a fix for this.
    I will be sending you a private message with an attachment.

    Regards
    Dan
    Daniel Handley
    Red Gate Software Ltd
    · Share on Twitter
  • lslmustanglslmustang Posts: 6
    But the issue is only on one of three servers. See my other post.
    · Share on Twitter
  • Daniel HandleyDaniel Handley Posts: 214
    Can you contact support for the hot fix.

    Regards
    Dan
    Daniel Handley
    Red Gate Software Ltd
    · Share on Twitter
This discussion has been closed.