January 16, 2012 at 1:49 pm
Hi,
We have SQL server 2008 Std server which contains main Database of size around 150GB.We have setup Log shipping to DR site.
Our Back statergy: 1.Daily night full backup
2.Every 10 min trans log backup(Morning 6 to 9pm)
3.Every night 12 to 6pm hourly trans backup.
Every Sunday night:Maintenance jobs:ChECKDB,REBUILD INdex
When we complete our weekly maintenance task then our first log file size will be around 50GB(mainly due rebuild indexing) which is having problem copying to DR site.
I have red many blogs but no one have suggested correctly and clearly how the we can have good maintenance plan.
We are not in the favour of changing recovery from FULL to simple/BULK.
1. I am thinking of running transaction log back during the maitenance job is running? Is it good idea to have small tranaction log files instead of ending up with big chunk after our maitenance?
2.Use your script to check and do the balanced index optimistation?
3.Find large indexes and sperad across whole week?
Please let me know your thought and your help is really appreicated.
Thanks
Nick
January 16, 2012 at 1:58 pm
Don't use maintenance plans for index maintenance. It rebuilds everything regardless of whether the indexes need rebuilding or not. Use something like the scripts from http://www.sqlfool.com or http://ola.hallengren.com/Versions.html
If need be also schedule log backups at intervals during the index rebuilds, but do remember that index rebuilds are each a single transaction, and so you may still end up with larger than expected log backups.
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
January 16, 2012 at 2:22 pm
Hi Gail,
Thanks for your quick reply and providing me URL's.
You suggest me to use Point 2. in my previous post which is mainly use the script and do index maintenance.
This is not not VLDB and was wondering is there any simpler solutions?
Thanks
Nick
January 16, 2012 at 2:38 pm
No, not really. bulk logged recovery won't reduce the size of your log backups. You've pretty much got the choice of rebuild selective (which I do on anything other than a tiny DB) or suck up the large log backups. If you want to rebuild your entire database weekly (which is what the maintenance plans do) then you have to accept that you'll get log backups comparable with the size of the database
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
January 16, 2012 at 2:56 pm
Hi Gail,
Thanks once again your quick reply.
I got your point and will go with selective index maintenance optimisation by using script NOT WITH MAINTENANCE PLAN.
You are right probably my some of large index are not fragmented but our maintenance plan still rebuilding all of them regardless of any fragmentation level.
It will be nice to have feature in Maintenance task if they combine ignore, reorganise and rebuild with option to specifying percentage and based on that optimise index:)
Do you recommend following?
0-10% Ignore
10-30% Reorganise
30%> Rebuild
Thanks
Nick
January 16, 2012 at 3:15 pm
Those are usually fine as defaults (and I believe it's what both of those scripts use by default).
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply