March 8, 2010 at 10:39 am
Is it possible in SQL Server 2005 such that NO log should be generated while reorganize/rebuild indexes?
March 8, 2010 at 10:46 am
No. All data modification operations in SQL Server are logged. If they were not, then rollback would not be possible and a cancellation/failure of the operation would result in the DB being marked suspect and requiring a restore from backup.
Unless you're using database mirroring you can switch the recovery model to bulk-logged and index rebuilds will be minimally logged. Switch back to full and take a log backup after the rebuild has finished.
Reorgs are fully logged in all recovery models.
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
March 8, 2010 at 11:00 am
we have log shipping for production database and we do index rebuild every 2 weeks and huge log of size 15 GB is generating and it's taking several hours to transfer that log to secondary location effecting the performance of users coming from remote location to access our production database. If I change the recovery model to bulk-logged before running the rebuild task, will the log size will be less? (I will switch the recovery to full once the index rebuild is done)
Is log shipping breaks when we switch the recovery model to bulk-logged?
March 8, 2010 at 11:14 am
pshaship (3/8/2010)
Is log shipping breaks when we switch the recovery model to bulk-logged?
No, but your log backups will be no smaller. The transaction log won't grow as much but the log backup sizes will stay the same.
Do you rebuild all indexes every time? If so you may want to implement a selective rebuilding script that only rebuild the indexes that really need rebuilding.
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
March 8, 2010 at 11:25 am
Do you rebuild all indexes every time? If so you may want to implement a selective rebuilding script that only rebuild the indexes that really need rebuilding.
Yes, I'm using a selective rebuilding script that only reorganize/rebuild the indexes that really required. I took the below script from MSDN, which will reorganize if fragmentation<30 and rebuild if fragmentation>30.
I have modified the script to reorganize/rebuild indexes having page count>1000. But still the log backup size is 15 GB. If Ido selective reorganize or just run rebuild maintenance plan task, the log size is same 15 GB.
There are only 75 indexes that have avg_fragmentation_in_percent <30 and 2 indexes having avg_fragmentation_in_percent>30. But still log backup size is 15 GB after selective index defrag as well as rebuild maintenance plan task
will the log backup size depends on the size of the index that is reorganized/rebuild?
March 8, 2010 at 12:19 pm
pshaship (3/8/2010)
I have modified the script to reorganize/rebuild indexes having page count>1000. But still the log backup size is 15 GB. If Ido selective reorganize or just run rebuild maintenance plan task, the log size is same 15 GB.
I think you may need to do some investigation as to why your indexes are fragmenting so fast. Are you doing database shrinks?
will the log backup size depends on the size of the index that is reorganized/rebuild?
Yes.
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
March 8, 2010 at 5:37 pm
I think you may need to do some investigation as to why your indexes are fragmenting so fast. Are you doing database shrinks?
I'm NOT doing any database shrinks and after my investigation, I have noticed that the avg_fragmentation_in_percent is between 10 to 30 % for most of the indexes & just 2 indexes have avg_fragmentation_in_percent is around 40% , 2 weeks after performing index defrag.
2 weeks after performing index defrag, having avg_fragmentation_in_percent is between 10 to 30 % is considered to be our indexes are fragmenting so fast? or it's normal
March 8, 2010 at 8:23 pm
pshaship (3/8/2010)
I think you may need to do some investigation as to why your indexes are fragmenting so fast. Are you doing database shrinks?
I'm NOT doing any database shrinks and after my investigation, I have noticed that the avg_fragmentation_in_percent is between 10 to 30 % for most of the indexes & just 2 indexes have avg_fragmentation_in_percent is around 40% , 2 weeks after performing index defrag.
2 weeks after performing index defrag, having avg_fragmentation_in_percent is between 10 to 30 % is considered to be our indexes are fragmenting so fast? or it's normal
We really cannot say whether or not that is normal. As far as we can say right now is that is appears to be normal in your environment.
It really depends upon the number of transactions, the fill factor, the structure of the indexes, the key columns of the indexes, etc...
For a table with a high number of inserts and minimal updates and the primary key defined on an identity column that would not be normal. However, change the primary key to GUID and it could very well be normal for that table.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 9, 2010 at 12:40 am
I wonder why you're getting the same log impact from selective rebuilding as from rebuilding all then....
Try changing the schedule from every 2 weeks to every week, and change the thresholds so that it's rebuild > 30%. reorg between 15 and 30. See it that helps the log impact.
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
March 10, 2010 at 6:19 am
Gail, we don't have enough information to know if 15GB is an appropriate amount of logging for his reorg/build. If the OP has 2TB of tables then 15GB could be a very good number for log size after index maint.
OP, what is size of data? Also, what is percent full on pages just before doing index maint and do you see many page splits and/or do a lot of insert/update/delete activity each period?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 10, 2010 at 6:36 am
TheSQLGuru (3/10/2010)
Gail, we don't have enough information to know if 15GB is an appropriate amount of logging for his reorg/build. If the OP has 2TB of tables then 15GB could be a very good number for log size after index maint.
OP stated that he sees the same log impact from selective rebuilding as he does from rebuilding everything. Hence my suggestion to rebuild more often, hopefully rebuilding less each time
15GB may well be an appropriate amount, but if it's killing log shipping, then it's a problem even if it is an appropriate amount.
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
March 16, 2010 at 12:41 am
I have a question why are you doing so frequent index organize and rebuild?
I suggest to investigate is the index really required to rebuild so frequently?
1) Do you have any performance issues?
2) Check the root cause like the query that has performance issue
3) Check the related tables and index along with considering other performance influencing parameters like optimized query(join, temptable, recompilation, short etc..), out dated statistics and resource bottlenecks, blockings)
4) If the index is really getting fragmented unexpectedly then consider choosing right index with appropriate fill factor
a) On table where inserts, delete and updates are very high try to create non cluster index if you are using clustered index then use the fill factor below 60% to avoid fragmentation
b) Cluster index are very good for high reading operation but get fragmented for frequentl delete and inserts are happening
Let me know if the above recommendation is useful for you? As I do not know the exact cause I have given a very generic recommendation
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply