Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Failed Simple Maintenance Plan only when Full Backups do not run Expand / Collapse
Author
Message
Posted Wednesday, November 21, 2012 2:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 23, 2012 1:02 PM
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.
Post #1387607
Posted Thursday, November 22, 2012 2:56 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 10, 2013 2:14 AM
Points: 5, 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
Post #1387794
Posted Thursday, November 22, 2012 4:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 40,193, Visits: 36,597
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 2008, MVP
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

Post #1387837
Posted Thursday, November 22, 2012 5:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 23, 2012 1:02 PM
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

Post #1387858
Posted Thursday, November 22, 2012 11:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 40,193, Visits: 36,597
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 2008, MVP
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

Post #1387958
Posted Friday, November 23, 2012 1:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 23, 2012 1:02 PM
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?
Post #1388244
Posted Friday, November 23, 2012 1:48 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 40,193, Visits: 36,597
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 2008, MVP
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

Post #1388248
Posted Monday, November 26, 2012 2:18 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 10, 2013 2:14 AM
Points: 5, Visits: 79
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)
Post #1388472
Posted Monday, November 26, 2012 3:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 40,193, Visits: 36,597
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 2008, MVP
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

Post #1388498
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse