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.
LVL 14
quizwedgeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gothamiteCommented:
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.
0
quizwedgeAuthor Commented:
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.
0
gothamiteCommented:
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.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

quizwedgeAuthor Commented:
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.
0
quizwedgeAuthor Commented:
Haven't gotten an answer on this yet... anyone have any thoughts?
0
Jim P.Commented:
How big is this DB?

How much space do you have to store backups?

Are you running tran log backups?
0
Anthony PerkinsCommented:
>>If I don't shrink, the database logs get too large and fill up the server. <<
Than that means your Transaction Log Backups are not frequent enough.  Once a week is clearly not enough.  There is simply no reason to schedule a shrink ever.  If you cannot or will not or unable to do frequent Transaction Log backups, than I would recommend you change you recovery model to Simple and understand that you lose the possibility of doing point-in-time restores.
0
quizwedgeAuthor Commented:
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.
0
Jim P.Commented:
>> 853.25 MB

That's your biggie? I've had databases servers on which my smallest DB was at 10 GB and that was as compact as I could make it. ;-) That being said, you not only have to look at the mdf, you have to look at the transaction log (ldf) files as well.

We're assuming SQL 2005. The full backups are, for all intent, largely just dumping everything from the mdf and everything that isn't checkpointed in the ldf. So what you get is this huge file on a full. But then if you were to use a compression app (7Zip, Zip, Winrar) you get better than a 50% compression ratio.

As for the errors, some stuff isn't registering in the MSDB db and so you get there is "no full backup" for the differential. I have never bothered with differentials. By the time you hit several days out the differentials are as large as a daily full. I would suggest do a daily full and then hourly tran logs on the databases that are full recovery model.

Two easy ways to determine the databases that are full recovery model -- in the SSMS: do Management --> Maintenance Plans --> Right Click --> New Maintenance Plan --> Transaction Log Backups.

The other way is a New Query window type in "select * from sys.databases" (skip quotes) then hit <F5> . One of the columns shows the recovery model column.
0
quizwedgeAuthor Commented:
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. :)
0
Anthony PerkinsCommented:
>>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. <<
Correct. See my comment here http:#a32425438

>>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?<<
Put it another way, there is no downside to backing up the transaction log every hour.  Either do it right or switch to Simple Recovery Model.
0
Jim P.Commented:
I have to agree with AC, do the tran logs hourly.

As far as keeping backups -- I would keep the fulls for whatever time period, but trash the tran logs after 3-4 days. Realistically, if they want a Point-In-Time (PIT) recovery any longer than 24 hours they are trashing a lot of other people's work. The reason I say 3-4 days is that if someone screws up on Friday and you don't find out until Monday, you can restore to a different DB and then import specific rows or tables as needed.

Another thing to notice is the COPY_ONLY flag (http://www.mssqltips.com/tip.asp?tip=1075) when taking backups. You may also want to read some of the articles from mark_wills on the Accidental DBA:

http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Managing-the-Transaction-Log-for-the-Accidental-DBA.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
quizwedgeAuthor Commented:
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.
0
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.