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.