July 3, 2011 at 10:05 pm
Hi all,
I have a database of 2 TB in sql server 2005. There is a maintenance plan for rebuild indexes, that executes every Sunday and takes 16-18 hrs to complete, on friday I checked the fragmentation on this database and found so many fragmentation. After completion of rebuild index maintenance plan on Sunday, I again checked the fragmentation and wondered the fragmentation is still remain same as it was before completion of rebuild indexes. Can anybody tell me the reason of it. I can i fix this problem ?
Thanks in advance.
July 3, 2011 at 11:24 pm
Rebuild Indexes effect when you have page count > 1000,check the page count of the index with the help of
sys.dm_db_index_physical_stats
http://msdn.microsoft.com/en-us/library/ms188917.aspx
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
July 4, 2011 at 12:28 am
Yes, all page counts are >1000.
July 4, 2011 at 1:26 am
beejug1983 (7/3/2011)
Hi all,I have a database of 2 TB in sql server 2005. There is a maintenance plan for rebuild indexes, that executes every Sunday and takes 16-18 hrs to complete, on friday I checked the fragmentation on this database and found so many fragmentation. After completion of rebuild index maintenance plan on Sunday, I again checked the fragmentation and wondered the fragmentation is still remain same as it was before completion of rebuild indexes. Can anybody tell me the reason of it. I can i fix this problem ?
Thanks in advance.
How many pages the tables have?
Does the tables are heaps?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
July 4, 2011 at 2:02 am
No tables are not heaps.
July 4, 2011 at 3:14 am
beejug1983 (7/4/2011)
No tables are not heaps.
Do you have any shrinking task?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
July 4, 2011 at 8:20 pm
No, we do not have any shrinking task too.
July 5, 2011 at 9:18 am
1) stop using maintenance plans for index and stats mx. get a copy of ola.hallengren.com's free and fully documented maintenance suite.
2) do you have any free space in the database or are you allowing auto-growth to size the database? You need free space for optimal defrag activities.
3) you need to do an index strategy session to determine many things about indexing including missing indexes, unused indexes, fragmentation over time, optimal fill factors, clustered vs nonclustered, etc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 5, 2011 at 9:55 pm
I used DBCC command manually to defrag the indexes and it worked for me. Thanks all.
July 5, 2011 at 11:36 pm
Hi Beejug1983,
I need your help.
In my environment i have a 2 TB SQL 2005 Database and the DB is in SIMPLE recovery mode.
Now we are changing Simple to Full to achive the point in time by configuring log backups.
I just want to know from you is, while Rebuilding indexes of this 2TB Database, what extent your log file increasing. Based on your reply i will plan for the Log file Space in my environment to rebuild the indexes on my 2 TB DB.
Current DB Details:
DB Size = 2.2 TB
LOG file Size = 276 GB
Your input highly importent for me.
thanks
July 6, 2011 at 2:02 am
Please don't post the same question in multiple places. It just wastes people's time as they answer questions already answered.
Replies to Mohan's question to this thread please: http://www.sqlservercentral.com/Forums/Topic1137066-146-1.aspx
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
July 6, 2011 at 7:30 pm
Hi Mohan,
Please refer your old thread.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply