Link to home
Start Free TrialLog in
Avatar of quizwedge
quizwedgeFlag for United States of America

asked on

Failed Differential Backup (MS SQL Server 2005 SP3)

I have three maintenance plans on our MS SQL Server 2005 SP3.

Every Sunday at 2AM I do a Full backup, then cleanup backup files older than 3 weeks, then cleanup history older than 4 weeks, then reorganize indexes, and finally update statistics.  This works fine.

Every Saturday at 3AM, I run a BACKUP LOG and DBCC SHRINKFILE on each of the databases.  I just realized my notifications are off, so I'm not sure if this is running successfully.

Every Monday through Saturday at 2:30 AM, I run a differential backup.  Since updating to SP3, this has errored.  Here is what Event viewer has:


Package "DifferentialBackup" started

Starting up database 'XXXX'

BACKUP failed to complete the command BACKUP DATABASE 'YYYY' WITH DIFFERENTIAL. Check the backup application log for details messages

SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

A few more entries of "BACKUP failed to complete" for other databases.

SQL Server has encountered 1 occurrence(s) of cachestore flush the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

SQL Server has encountered 1 occurrence(s) of cachestore flush the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.

A few more entries of "BACKUP failed to complete" for other databases.

Starting up database 'XXXX'

Database differential changes were backed up. Database: XXXX, creation date(time): 2007/11/16(13:10:43), pages dumped: 1426, first LSN: 2441:82:1, last LSN: 2441:84:1, full backup LSN: 2431:55:37, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\XXXX_backup_201004220230.bak'}). This is an informational message. No user action is required.

Another entry of "BACKUP failed to complete" for other databases.

Package "DifferentialBackup" failed.

SQL Server Scheduled Job 'DifferentialBackup' (0x255F307F4216BB43B849FBBE7D8202C8) - Status: Failed - Invoked on: 2010-04-22 02:30:00 - Message: The job failed.  The Job was invoked by Schedule 7 (DifferentialBackup_MaintenancePlan_Job_Schedule).  The last step to run was step 1 (DifferentialBackup_MaintenancePlan_Job_Step).

Report Server Windows Service (MSSQL2K5WG) cannot connect to the report server database.


When I look at the SQL Server logs, I see a bunch of entries for "Error: 3041, Severity 16, State: 1." right before the "BACKUP failed to complete" entries.

I found http://support.microsoft.com/kb/921106 but it seems to only apply for SP1 or for no service packs.  Also, the article doesn't mention Workgroup Edition, which is what we're running.

Any ideas?  Thanks.
Avatar of gothamite
gothamite
Flag of United Kingdom of Great Britain and Northern Ireland image

Firstly I would turn off your shrink job. This causes massive fragmentation of indexes and will cause your index reorg job to take longer. Also this might give the diff backup more to do as all the pages would have moved (although I'm not 100% on this).  

Also your diff backup is only half an hour earlier than the other job. Does it clash. Try making the 3am job run a bit later.
Avatar of quizwedge

ASKER

If I don't shrink, the database logs get too large and fill up the server.  For the most part, the server is used Monday through Friday during normal business hours, so I figure having the longer processes run in the dead of night on the weekend gives it time.

The backup doesn't seem to clash with the shrink job, but the shrink job only runs on Saturdays so conflict isn't an issue for the differential on Monday through Friday.
Are you shrinking the database AND log files or just the log? Can you not just backup the log more often? We backup ours every 15 mins on every db. This should reduce the size that the log is getting to.
My goal was to just shrink the log.  I was worried about the I/O overhead of backing up every 15 minutes.  Some of the database are / will be quite large.  That being said, even with the shrink option, this was all working before the upgrade to SQL Server 2005 SP3.
Haven't gotten an answer on this yet... anyone have any thoughts?
How big is this DB?

How much space do you have to store backups?

Are you running tran log backups?
SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Multiple DB's with the MDF files ranging from 2MB to 853.25 MB

The current backup folder has 14.4 GB of files.  I'm clearing backups older than 3 weeks and history older than 4 weeks.  I currently have 55.5 GB left on that drive.  I also have another drive that has 145 GB free.  I should mention that the size of the backup is off from what should be normal.  First, the differential backup hasn't been running for a few weeks.  Second, in the last few days I switched to running the full backup described above daily so that I have daily backups.

I know just enough to be dangerous about SQL backup... not sure if I'm running tran log backups, so I'm probably not. :)

I know that I changed some of the recovery models to Simple.  Now that I think about it, I'm not sure I've had a problem with transaction logs getting too big since I changed it to Simple.  I do have at least one database that is still set to Full.  This may be another question (want to be fair), but if I'm only backing up once per day (differential or full), is there any benefit to using Full recovery instead of Simple?

Turns out that not only was the differential backup not working, but the shrink wasn't working as well.  I've turned that job off.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I think I may have had a realization.  The databases themselves aren't very big, but the log files have gotten so big that they fill up the hard drive (lots of updates instead of inserts).  It seems like I'm probably not backing up transaction logs.  The ldf files range from 1MB to 7.86GB.  I know the 7.86GB ldf file is a full recovery model.

From what I remember, I switched to Simple for most of the databases because of the log files and it seemed to be what people recommended.  Since I'd prefer to be able to restore many of the databases to a specific time (one of the databases, for example, stores order data), it sounds like I should have the following jobs run:

Daily: Full backup, then cleanup backup files older than 3 weeks, then cleanup history older than 4 weeks, then reorganize indexes, and finally update statistics.

Hourly: Transaction Log Backups

Kill the differential and shrink jobs (still concerns me that the differentials aren't working, but if I'm not using them anyway, probably not worth going further down the rabbit hole on that one.)

One other question:  If my transaction logs are taking two weeks to get too big, is there a downside to backing up transaction logs only daily instead of hourly?

Thanks for all of the help... I really know just enough to be dangerous on this stuff. :)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
While this didn't fix the issue if I decide to do differentials in the future, it solved the problem and I don't see myself needing to do differentials.  Haven't gotten this set up yet, but the Accidental DBA article was great!  Thanks for all of the help.
Glad to be of assistance. May all your days get brighter and brighter.