What are the challenges you face when working across database platforms? Take the survey
Options

Backup failing on large database

CorrinHarrisCorrinHarris Posts: 12
edited July 5, 2013 8:15AM in SQL Backup Previous Versions
I have a large database backup failing (when other databases on the same server to same destination are working).

SQL Version SQL2008r2 Enterprise (RTM)

results from SP_HELPFILE for sizes....

name fileid filename filegroup size maxsize growth usage
DatabaseName_Dev 1 M:\Data\DatabaseName_Dev.mdf PRIMARY 2113415552 KB 2147483648 KB 1024000 KB data only
DatabaseName_Dev_log 2 N:\Logs\DatabaseName_Dev_log.ldf NULL 507747264 KB 588800000 KB 1024000 KB log only
DatabaseName_Dev2 3 M:\Data\DatabaseName_Dev2.ndf PRIMARY 266242048 KB 614400000 KB 1024000 KB data only


Backup log....
SQL Backup log file 6.5.1.9

-SQL "BACKUP DATABASE [databasename] to DISK = '\\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\<DATABASE>\<DATABASE><DATETIME YYYY-MM-DD-HH-NN>-<TYPE>-1-of-3.SQB',DISK = '\\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\<DATABASE>\<DATABASE><DATETIME
YYYY-MM-DD-HH-NN>-<TYPE>-2-of-3.SQB',DISK = '\\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\<DATABASE>\<DATABASE><DATETIME YYYY-MM-DD-HH-NN>-<TYPE>-3-of-3.SQB' WITH INIT,THREADPRIORITY=2,MAXDATABLOCK=65536,COMPRESSION=1,SINGLERESULTSET "

ERRORS AND WARNINGS


27/06/2013 16:25:46: Backing up databasename (full database) on BC1 instance to:
27/06/2013 16:25:46: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-1-of-3.SQB
27/06/2013 16:25:46: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-2-of-3.SQB
27/06/2013 16:25:46: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-3-of-3.SQB

27/06/2013 16:25:47: BACKUP DATABASE [databasename] TO VIRTUAL_DEVICE = 'SQLBACKUP_42BB9A75-7CAC-4B8D-9950-0B20076994A1', VIRTUAL_DEVICE = 'SQLBACKUP_42BB9A75-7CAC-4B8D-9950-0B20076994A101', VIRTUAL_DEVICE = 'SQLBACKUP_42BB9A75-7CAC-4B8D-9950-0B20076994A10
2' WITH BUFFERCOUNT = 18, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (databasename), 27/06/2013 16:25:46', DESCRIPTION = N'Backup on 27/06/2013 16:25:46 Server: xxxPLLBC1\BC1 Database: databasename', FORMAT

27/06/2013 20:36:03: Thread 0 error:
Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
27/06/2013 20:36:03:
27/06/2013 20:36:03: Thread 1 error:
Error 620: Error writing to backup file(s).
Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
27/06/2013 20:36:03: Warning 210: Thread 1 warning:
WriteFile failed for file: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-2-of-3.SQB at position: 171744166912
27/06/2013 20:31:02: WriteFile failed for file: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-2-of-3.SQB (The requested operation could not be completed due to a file system limitation)
27/06/2013 20:31:32: Re-attempt: 1
27/06/2013 20:31:32: WriteFile failed for file: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-2-of-3.SQB (The requested operation could not be completed due to a file system limitation)
27/06/2013 20:32:02: Re-attempt: 2
27/06/2013 20:32:02: WriteFile failed for file: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-2-of-3.SQB (The requested operation could not be completed due to a file system limitation)
27/06/2013 20:32:32: Re-attempt: 3
27/06/2013 20:32:32: WriteFile failed for file: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-2-of-3.SQB (The requested operation could not be completed due to a file system limitation)
27/06/2013 20:33:02: Re-attempt: 4
27/06/2013 20:33:02: WriteFile failed for file: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-2-of-3.SQB (The requested operation could not be completed due to a file system limitation)
27/06/2013 20:33:32: Re-attempt: 5
27/06/2013 20:33:32: WriteFile failed for file: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-2-of-3.SQB (The requested operation could not be completed due to a file system limitation)
27/06/2013 20:34:02: Re-attempt: 6
27/06/2013 20:34:02: WriteFile failed for file: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-2-of-3.SQB (The requested operation could not be completed due to a file system limitation)
27/06/2013 20:34:32: Re-attempt: 7
27/06/2013 20:34:32: WriteFile failed for file: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-2-of-3.SQB (The requested operation could not be completed due to a file system limitation)
27/06/2013 20:35:02: Re-attempt: 8
27/06/2013 20:35:02: WriteFile failed for file: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-2-of-3.SQB (The requested operation could not be completed due to a file system limitation)
27/06/2013 20:35:32: Re-attempt: 9
27/06/2013 20:35:33: WriteFile failed for file: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-2-of-3.SQB (The requested operation could not be completed due to a file system limitation)
27/06/2013 20:36:03: Re-attempt: 10
27/06/2013 20:36:03: WriteFile failed for file: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-2-of-3.SQB (The requested operation could not be completed due to a file system limitation)
27/06/2013 20:36:03:
27/06/2013 20:36:03: Thread 2 error:
Error 620: Error writing to backup file(s).
Backup error: Access violation at address 00402D17 in module 'SQBCoreService.exe'. Write of address 07081000
27/06/2013 20:36:03: Warning 210: Thread 2 warning:
WriteFile failed for file: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-3-of-3.SQB at position: 171337319424
27/06/2013 20:30:57: WriteFile failed for file: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-3-of-3.SQB (The requested operation could not be completed due to a file system limitation)
27/06/2013 20:31:27: Re-attempt: 1
27/06/2013 20:31:27: WriteFile failed for file: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-3-of-3.SQB (The requested operation could not be completed due to a file system limitation)
27/06/2013 20:31:57: Re-attempt: 2
27/06/2013 20:31:57: WriteFile failed for file: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-3-of-3.SQB (The requested operation could not be completed due to a file system limitation)
27/06/2013 20:32:27: Re-attempt: 3
27/06/2013 20:32:27: WriteFile failed for file: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-3-of-3.SQB (The requested operation could not be completed due to a file system limitation)
27/06/2013 20:32:57: Re-attempt: 4
27/06/2013 20:32:57: WriteFile failed for file: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-3-of-3.SQB (The requested operation could not be completed due to a file system limitation)
27/06/2013 20:33:27: Re-attempt: 5
27/06/2013 20:33:27: WriteFile failed for file: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-3-of-3.SQB (The requested operation could not be completed due to a file system limitation)
27/06/2013 20:33:57: Re-attempt: 6
27/06/2013 20:33:57: WriteFile failed for file: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-3-of-3.SQB (The requested operation could not be completed due to a file system limitation)
27/06/2013 20:34:27: Re-attempt: 7
27/06/2013 20:34:27: WriteFile failed for file: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-3-of-3.SQB (The requested operation could not be completed due to a file system limitation)
27/06/2013 20:34:57: Re-attempt: 8
27/06/2013 20:34:57: WriteFile failed for file: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-3-of-3.SQB (The requested operation could not be completed due to a file system limitation)
27/06/2013 20:35:27: Re-attempt: 9
27/06/2013 20:35:27: WriteFile failed for file: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-3-of-3.SQB (The requested operation could not be completed due to a file system limitation)
27/06/2013 20:35:57: Re-attempt: 10
27/06/2013 20:35:57: WriteFile failed for file: \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-3-of-3.SQB (The requested operation could not be completed due to a file system limitation)
27/06/2013 20:36:03:
27/06/2013 20:36:03: SQL error 3013: SQL error 3013: BACKUP DATABASE is terminating abnormally.
27/06/2013 20:36:03: SQL error 3271: SQL error 3271: A nonrecoverable I/O error occurred on file "SQLBACKUP_42BB9A75-7CAC-4B8D-9950-0B20076994A1:" 995(failed to retrieve text for this error. Reason: 15105).
27/06/2013 20:36:03: SQL error 3202: SQL error 3202: Write on "SQLBACKUP_42BB9A75-7CAC-4B8D-9950-0B20076994A1" failed: 995(failed to retrieve text for this error. Reason: 15105)

Comments

  • Options
    peteypetey Posts: 2,358 New member
    What are the sizes of the files:
      \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-1-of-3.SQB
      \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-2-of-3.SQB
      \\xxxPLLBKP01\MBSQLBACKUPS4$\BACKUPS\xxxPLLBC1\BC1\databasename\databasename2013-06-27-16-25-FULL-3-of-3.SQB
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    157GB EACH
  • Options
    peteypetey Posts: 2,358 New member
    The error suggests that there is an issue with writing a file of that size. It's possible that it's a file system issue as suggested here, or a fragmentation issue. You could use the Contig utility from Microsoft/Sysinternals (http://download.sysinternals.com/files/Contig.zip) to analyze the fragmentation of the backup file(s). If excessive fragmentation is the cause, you could try backing up to a single file. If that is not feasible, you could try backing up to 5 or 6 files to reduce the level of fragmentation in each individual file.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Increasing the number of files (plus some index maintenance) appears to have done the trick. Thanks.
Sign In or Register to comment.