July 31, 2012 at 11:40 am
Hi,
Hi,
We have SQL Server 2008 SP2 enterprise edition.
We have a database of size 50 GB and it is configured for log shipping.
The logshipping backup/copy/restore jobs run every 15 mins.
On every Sunday, we reorganize all indexes for this database, because the avg_fragmentation_in_percent is <30% all the time.
This job taking 1 hr & 30 mins and the log shiping backup job genarates huge log backup files (total up to 30 GB)
But we have a small 5 mbps netwrok pipe to copy to secondary server and because of this huge file sizes, the copy job taking several hours (sometimes >1day).
Is there any fix for this problem?
I do not want have these big log file backups to be generated while index reorganize. Is there any option to say NOT to log transaction log while index reorganize?
Is there any solution or better technology that I can implement to aviod this problem other than log shipping?
Please advise.
July 31, 2012 at 11:46 am
Have you looked into the various types of replication? I don't know the structure of your database, but if possible I'd probably look to transactional replication first.
July 31, 2012 at 12:07 pm
I looked at transaction replication and it requires primary key for each table. But some of the tables in our database do not have primary key
Is implementing any replication, avoid generating huge log while performing index derfrag?
July 31, 2012 at 12:07 pm
Rather than reorganizing all indexes every sunday, why not break it up into smaller pieces and do a bit of the re-org on multiple nights?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 31, 2012 at 12:20 pm
how to find which indexes are generating huge log while performing index defrag (I'm using index reorganize maintenance plan)?
thank you
July 31, 2012 at 12:48 pm
how frequent is your log backup being performed?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 31, 2012 at 1:05 pm
Are you using the native index maintenance task, or something custom?
You can use something like ola.hallengren for your index maintenence, or build your own custom job after identifying which of your largest tables is frequently your most fragmented.
There are lots of t-sql statements online for doing that.
You can also use something like sqb2mtf from redgate to compress and encrypt the log files.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply