1

Let me start off that I'm new to SQL administration and have inherited an older server that serves the main DB for our day in/out operations. So, my apologies in advanced to my beginner questions.

What I've been tasked with is to discover why the simple maintenance plan on this server fails ONLY when full backups do NOT run. I've done some digging and have identified the culprit as being my transaction log is filling up (statically set to 20 GB and then to 40 GB after the first round of job failures). I've also come to understand that our current recovery model (simple) is a bad idea for a production SQL server and should be moved to either batch or full. I've also placed monitoring on the log file usage (%) and have noticed that the log file is minimally (less than 1%) used during production hours and only fills up when maintenance comes through. A successful simple maint plan will consume between 40% and 80% of the trans log. A failure will max out the log. What other pieces of information do you need?

I need to understand why the simple maint plan succeeds when full backups run and fails when they do not. Everything I've read says that I should not be seeing this issue. I want to get a better understanding of what is going on so I don't have to 'just increase the log space'. To me that thought is a band-aid that will just hurt us in the long term.

Server environment: SQL Ent 2005 x64 (9.0.3080) w/ SSRS on a Windows Server 2003 R2 x64. Full backups run on all DBs M-F. The main ops DB has 10 min differentials during production/business hours and a simple recovery model. The simple maint plan runs every 24 hours. On the file system: Main ops DB is approx. 70 GB, index files (4 of them) are approx. 2 GB, trans log is 40 GB. Full backups are about 50 GB

No replication or mirroring.

I'm looking at the job and its type is listed as an SSIS package. Does this make sense?

Exection tasks: Database Integrity - Include Indexes Rebuild Indexes - Default free space per page - Sort results in tempdb Update Statistics - Column Stats Only - Full Scan Maintenance Cleanup - Delete files: maintenance plan text reports - Age: Older than 2 weeks History Cleanup - Delete files: Backup and Restore, SQL Server Agent job History, Maintenance Plan History - Age: Older than 2 weeks

Please assist. Any help appreciated.

Josh
  • 11
  • 1
  • "I've also come to understand that our current recovery model (simple) is a bad idea for a production SQL server and should be moved to either batch or full" - This is entirely dependent on your recovery needs. I know plenty of shops that use the Simple Recovery model because it meets their recovery needs and it simplifies log file management. Is it "bad for a production server"? Not neccessarily. Is it bad for your production server? Maybe, dependent on your needs. – joeqwerty Nov 27 '12 at 21:19

1 Answers1

-1

This Symantec Connect article about using trying to figure out the backup strategy for the different recovery models of SQL should help. Even if you don't use Backup Exec, it's almost all relevant and will apply to other backup models including if you use the SQL tools to do the DB dumps for backup: http://www.symantec.com/connect/forums/be2012-backing-sql-databases-full-simple-recovery-mode-issue

James McKey
  • 1
  • 1
  • Welcome to Server Fault - [we generally discourage link-only answers here](http://meta.stackexchange.com/questions/8231/are-answers-that-just-contain-links-elsewhere-really-good-answers). Please consider updating your answer so that it still provides useful information is Symantec ever decides to move that document elsewhere. Thanks! – voretaq7 Dec 04 '12 at 17:46