November 21, 2012 at 2:08 pm
Hello all.
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
Please assist. Any help appreciated.
November 22, 2012 at 2:56 am
Hi Josh,
What do you mean by "simple maintenance plan" ? What's it doing?
Do not increase the log space unless you can prove that your system requires it to be very large.
Do you mean that your log file is explicitly set to be 20GB??
Is there replication, mirroring or anything going on with this database?
S
November 22, 2012 at 4:32 am
josh.hill (11/21/2012)
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?
Don't rebuild all your indexes all the time. Rebuild just the ones that need rebuilding, there are several custom scripts that will do that for you, the maintenance plan task rebuilds everything .
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 22, 2012 at 5:42 am
Thanks for the replies!
Yes the logs are explicitly set to 40 GB today. They were set to 20 GB in the past.
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
November 22, 2012 at 11:25 am
See my earlier post. You have an index rebuild job that is rebuilding every single index in your database every time it runs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 23, 2012 at 1:06 pm
How appropriate of my predecessor to implement a hammer when a scalpel is needed.
What tools can I use to evaluate what indexes to rebuild?
November 23, 2012 at 1:48 pm
http://ola.hallengren.com/Versions.html
http://sqlfool.com/2011/06/index-defrag-script-v4-1/
Just 2 of the options available.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 26, 2012 at 2:18 am
You'd be surprised at what you find sometimes 😀
I just started a new position and I've found some wonderful maintenace plans that had been set up by someone previously.
My favorite is the one that Reorganises, then rebuilds the indexes, then does a DB shrink on all the sys databases, all without backing them up...ever! (They hadn't been backed up in over 2 years)
:ermm:
November 26, 2012 at 3:36 am
shellbus (11/26/2012)
My favorite is the one that Reorganises, then rebuilds the indexes, then does a DB shrink on all the sys databases
I believe that one is known as 'complete waste of time'
The rebuild redoes everything the reorg has already done, then the shrink undoes what the rebuild did. Deleting the entire maint plan would probably result in better maintained indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply