Red Gate forums :: View topic - Backup failing on large database
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 6
SQL Backup 6 forum

Backup failing on large database

Search in SQL Backup 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
CorrinHarris



Joined: 31 Oct 2012
Posts: 9

PostPosted: Fri Jun 28, 2013 10:24 am    Post subject: Backup failing on large database Reply with quote

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)
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2276

PostPosted: Sun Jun 30, 2013 3:40 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
CorrinHarris



Joined: 31 Oct 2012
Posts: 9

PostPosted: Mon Jul 01, 2013 10:08 am    Post subject: Reply with quote

157GB EACH
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2276

PostPosted: Wed Jul 03, 2013 2:31 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
CorrinHarris



Joined: 31 Oct 2012
Posts: 9

PostPosted: Fri Jul 05, 2013 1:15 pm    Post subject: Reply with quote

Increasing the number of files (plus some index maintenance) appears to have done the trick. Thanks.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group