January 11, 2011 at 3:46 pm
Hi,
Is it possible to perform Index Reorganize and Index Rebuild as non-logged operations in SQL Server 2008 R2 Enterprise edition x64 with Sp1?
Because we have a database with Size 30 GB and its configured for Logshipping. The log shipping backup job runs every 10 mins. When we perform the weekly Index Defrag or Index Rebuild, it's generating big log files twice the size of the actual database size and taking several hours to copy to secondary and filling the network bandwidth and thus causing performance issue with other applications
I know that we have the option of NOLOG in Oracle while rebuilding indexes. I'm looking for a similar/equivalent option in SQL Server 2008
Thanks for your inputs
January 11, 2011 at 5:16 pm
I think all you can do is set your recovery model to bulk logged and try to proactively prevent fragmentation.
(Those operations are eligible for minimal logging.)
Sorry
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
January 11, 2011 at 5:36 pm
weitzera (1/11/2011)
I think all you can do is set your recovery model to bulk logged and try to proactively prevent fragmentation.(Those operations are eligible for minimal logging.)
Sorry
Even with minimal logging - it won't help because all that does is keep the transaction log file from growing. The transaction log backups (which are then shipped) will contain all of the changes.
What you need to do is a couple of things:
1) Only rebuild/reorganize indexes that need to be, instead of all of them
2) Rebuild indexes online and sort in tempdb on
a) Instead or reorganizing, rebuild online unless the index has LOB data. Only reorganize tables that have LOB data and are more than xx% fragmented. I would probably set that to 30% on those types of indexes - and define a smaller fill factor to prevent them from being reorganized very often.
b) If you can identify those indexes that have to be reorganized, schedule those indexes individually for weekends instead of during the week.
3) Enable backup compression for all backups - this will reduce the size of the log backups being sent across the network.
4) Schedule the rebuild/reorganize process to run daily instead of weekly. This will spread the load across the week. Again, schedule those indexes that cannot be rebuilt online for the weekend.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
January 11, 2011 at 11:00 pm
There's no such thing as a non-logged operation in SQL Server. All data modifications are logged.
In bulk logged and simple recovery, index rebuilds are minimally logged, index reorganise is still fully logged. That's the best you can do.
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 12, 2011 at 4:03 am
Ditto, we run weekly reports on index fragmentation for each database on our servers. This allows us to rebuild/reorganise only those that need doing and so we are able to proactively maintain databases and not have massive logs each time we rebuild indexes.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply