Backup failing on large database

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

Backup failing on large database

Postby CorrinHarris » Fri Jun 28, 2013 10:24 am

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)
CorrinHarris
 
Posts: 9
Joined: Wed Oct 31, 2012 1:55 pm

Postby petey » Sun Jun 30, 2013 3:40 pm

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 7
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm

Postby CorrinHarris » Mon Jul 01, 2013 10:08 am

157GB EACH
CorrinHarris
 
Posts: 9
Joined: Wed Oct 31, 2012 1:55 pm

Postby petey » Wed Jul 03, 2013 2:31 am

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 7
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm

Postby CorrinHarris » Fri Jul 05, 2013 1:15 pm

Increasing the number of files (plus some index maintenance) appears to have done the trick. Thanks.
CorrinHarris
 
Posts: 9
Joined: Wed Oct 31, 2012 1:55 pm


Return to SQL Backup 6

Who is online

Users browsing this forum: No registered users and 0 guests