SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Failed Simple Maintenance Plan only when Full Backups do not run


Failed Simple Maintenance Plan only when Full Backups do not run

Author
Message
josh.hill
josh.hill
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 9
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.
shellbus
shellbus
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 79
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211997 Visits: 46259
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


josh.hill
josh.hill
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 9
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211997 Visits: 46259
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


josh.hill
josh.hill
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 9
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211997 Visits: 46259
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


shellbus
shellbus
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 79
You'd be surprised at what you find sometimes :-D

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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211997 Visits: 46259
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search