Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL BACKUP JOB Failure Expand / Collapse
Author
Message
Posted Monday, May 18, 2009 8:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 24, 2014 5:35 AM
Points: 285, Visits: 1,053
I am currently trying to investigate why our nightly full backups have been failing. The nightly full backups were working correctly but have now stopped. I have had a look in application logs and get these following messages.

Executed as user: NT AUTHORITY\SYSTEM. ...ercent processed. [SQLSTATE 01000] (Message 3211) 30 percent processed. [SQLSTATE 01000] (Message 3211) 41 percent processed. [SQLSTATE 01000] (Message 3211) 50 percent processed. [SQLSTATE 01000] (Message 3211) 61 percent processed. [SQLSTATE 01000] (Message 3211) 70 percent processed. [SQLSTATE 01000] (Message 3211) 81 percent processed. [SQLSTATE 01000] (Message 3211) 90 percent processed. [SQLSTATE 01000] (Message 3211) Processed 360 pages for database 'master', file 'master' on file 1. [SQLSTATE 01000] (Message 4035) 100 percent processed. [SQLSTATE 01000] (Message 3211) Processed 2 pages for database 'master', file 'mastlog' on file 1. [SQLSTATE 01000] (Message 4035) BACKUP DATABASE successfully processed 362 pages in 5.107 seconds (0.580 MB/sec). [SQLSTATE 01000] (Message 3014) 10 percent processed. [SQLSTATE 01000] (Message 3211) 20 percent processed. [SQLSTATE 01000] (Message 3211) 30 percent processed. [SQLSTATE 01000] (M... The step failed.

The backup data at the end of "nwcscmdsdataBACK(\\brentsan\e$\Brentvmsollis-BackupwcscmdsdataBACK.BAK)" is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets.

Error: 3266, Severity: 16, State: 1.

BACKUP failed to complete the command BACKUP DATABASE NWCSCMDSDataREP. Check the backup application log for detailed messages.

Error: 3041, Severity: 16, State: 1.


I am not sure if this a permissions issue or disk size issue. I think it is unlikely to be a permissions issue as the Log backups are working fine. Any help would help as to what might be the cause would be much appreciated.


Post #719131
Posted Monday, May 18, 2009 9:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:24 AM
Points: 33,088, Visits: 15,197
A couple things.

First, can you post the backup command?

Second, does this run interactively? If you run from SSMS?

Last, backups to remove drives (shares) are problematic, and flaky. The backup process throws pages out as fast as it can. If there is a network delay, it fails the backup. If a copy has a network error, it retries.

You would do better to back up locally and copy to the network (and delete locally).







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #719152
Posted Monday, May 18, 2009 9:06 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:50 PM
Points: 23,045, Visits: 31,570
I agree with Steve. Also, I'd run separate backup processes for each database.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #719160
Posted Monday, May 18, 2009 10:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 24, 2014 5:35 AM
Points: 285, Visits: 1,053
The BACKUP Command is shown below

DECLARE @securedb varchar(255)
IF EXISTS (SELECT * FROM NWCSCMDSData.dbo.sysobjects WHERE id = object_id(N'NWCSCMDSData.[dbo].[GPSecureDatabase]') AND xtype = 'U')
BEGIN
SET @securedb = (SELECT TOP 1 GPSecureDB FROM NWCSCMDSData..GPSecureDatabase)
END


declare dblist cursor for select name from master..sysdatabases
where not name in ('tempdb','model')
open dblist
declare @dn varchar(100)

fetch next from dblist into @dn
WHILE (@@FETCH_STATUS <> -1)
begin

exec ('BACKUP DATABASE [' + @dn + '] to [' + @dn + 'BACK] WITH NAME=''' + @dn +' Backup'' , INIT,STATS=10')


--exec ('sp_dboption ''' + @dn + ''',''autoclose'',''false''')
fetch next from dblist into @dn
end
close dblist
deallocate dblist
end

It is run as a sql job nightly monday to friday.
Post #719210
Posted Monday, May 18, 2009 10:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:24 AM
Points: 33,088, Visits: 15,197
Change the EXEC() to SELECT, get the statements, check and see if they execute.








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #719229
Posted Monday, May 18, 2009 11:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 24, 2014 5:35 AM
Points: 285, Visits: 1,053
I have changed the exec statements to select and they execute.

Incidentally the SQL-Server instance sits on a virtual server. I am wondering if that might have any impact. We were also recently attacked by a conficker virus. However this has not impacted on the log backup or other maintenance plans that we run.
Post #719292
Posted Wednesday, June 3, 2009 3:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 24, 2014 5:35 AM
Points: 285, Visits: 1,053
I have managed to get the backup to work. I deleted two of the backups in the folder and nightly schedule ran smooth. Two of the backups had become corrupt and the backup just seemed to keel over when it came to backing them up.

thanks for your input.
Post #728022
Posted Monday, April 29, 2013 6:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 13, 2014 10:06 PM
Points: 16, Visits: 73
Hi ,
let me know you are taking backup to local disk or network path . i guess you are taking backup to network path .

possible reason :
the backup failed due to network issue or in b\w the network was disconnected .

possible soluation:
improve the network performance b\w your local system and network path and some times
reran the job also will solve the problem.



--subahan munthamadugu---
Post #1447472
Posted Monday, April 29, 2013 6:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,467, Visits: 35,535
Please note: 4 year old thread.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1447482
Posted Monday, April 29, 2013 10:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 13, 2014 10:06 PM
Points: 16, Visits: 73
Hi ,
let me know you are taking backup to local disk or network path . i guess you are taking backup to network path and backup sizes are also big .

possible reason :
the backup failed due to network issue or in b\w the network was disconnected .

possible soluation:
1. improve the network performance b\w your local system and network path and some times
reran the job also will solve the problem.
2.suppose your databases are very big then stripe and take backup to net work path.
3. put retry option on your backup step.


--subahan munthamadugu---
Post #1447657
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse