| Author |
Topic |
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
 Posted - 2010-11-07 : 16:22:18
|
| I have a database which has reported 100 fragmentation then I have to do the defragmentation. After Defragmentation I haven't done any REINDEX or SHRINK Manually.After completing with Defragmentation the following day (Routine Reindex process) has increased the MDF and LDF sizes.Then I got rid of the excess 'GROWTH' by trimming the MDF and LDF through Shrink process.But again on last sunday it has to do again Reindex process (This is scheduled Maintenance plan [MP] of doing the REINDEX) increased its MDF and LDF sizes. This reindex process could not be completed as a result of lack of space which is prematurely occupied by MDF and LDF.When I checked into ERRORLOG File This has the message (Only Evidence I have.2010-11-07 08:24:23.79 spid14s Analysis of database 'ITLM' (5) is 98% complete (approximately 420 seconds remain). This is an informational message only. No user action is required.2010-11-07 08:24:25.58 spid14s Analysis of database 'ITLM' (5) is 99% complete (approximately 208 seconds remain). This is an informational message only. No user action is required.2010-11-07 08:24:26.62 spid14s Analysis of database 'ITLM' (5) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.This took almost 7 hours which has started at 1 AM on Sunday and completed around 8 AM as indicated above.In order to correct what has been done so far and make the MP based Reindex to execute properly what shall I do.Can anyone advise me please.Many thanks to all Experts. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2010-11-07 : 17:10:44
|
| The Excess growth has raised to 159 GB as against the normal MDF file size of 48 GB.This even creates problem in Backup the database to 3 hours time and also consumes 159 Gb + LDF size to get .BAK file.So I need to get rid of this extra space as a result of failed REINDEX process.Please advise and many thanks TKIZER for your guidance. |
 |
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2010-11-07 : 17:13:08
|
| Also, all this happened because of DEFRAGMENTATION process and later that REINDEX process picked up the EXTRA GROWTH and still it couldn't complete now.This is imminent for me to REMOVE THE EXTRA GROWTH of 159 back to 48 GB for the MDF.Please Advise.Once again thanks TKIZER. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-11-07 : 18:35:40
|
| You should not remove the excess growth. It is required.Here is what is needed to complete a rebuild according to MS:1. 125% of the size of the index in the MDF file2. 100% of the size of the index in the LDF fileSo if you've got a 100GB index, it'll need 125GB of free space inside the MDF file. It'll need 100GB of free space inside the LDF file.Stop shrinking the database files. You are only causing harm.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2010-11-07 : 18:55:09
|
| Till Saturday MDF is 48 GB and LDF is 2 GB;Sunday performed the REINDEX Process (Automated every Sunday Morning); as a result of REINDEX process MDF became 159 GB and LDF 48 GB;Normally every week it wont grow like this (MDF is 48 GB and LDF is 2 GB) has grown because DEFRAGMENTATION was done 6 days before last sunday (Which is yesterday) and Automated REINDEX has picked up thereafter and grown UNUSUALLY like other times.Now I want to reduce this UNWANTED GROWTH as a Result of REINDEX.Please Advise me. Many Thanks to you. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2010-11-09 : 18:15:48
|
| I know this SPACE is required to keep as a result of REINDEX process.BUT,All these weeks since the problem arises, there is no evidence for the growth of the database, but [See Above] as a result of REINDEX process only during this week it has grown from Normal 48 GB to whooping 202 GB.Based on this growth I have asked for what could be the reason for growing the Database size to 4 times.Thanks for your time. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dbalearner
Constraint Violating Yak Guru
272 Posts |
Posted - 2010-11-09 : 18:37:02
|
| Thanks Tkizer for your explanation, as you have correctly predicted I have ventured into all areas of what went wrong in going in circles like 'loop into tables' while doing the reindex.Obviously I could not find the result of what went wrong but from the ErrorLog file I could only see 'ANALYSING OF DATABASE' from 2 AM till 8:30 Am on last sunday morning and thereafter DATABASE has grown in its size and all users did complained of getting slowness.obviously thereafter I have seen as a result of 'ANALYSING OF DATABASE' Fragmentation reached 100 percent and I have to defragment and then follow the REINDEX (I Know you would say here dont do REINDEX after Defragment) but I want to see what went wrong with this Reindex. As a result of Reindex then again the SIZE of the MDF and LDF has grown, which thereafter SHRUNK back to the size where I have anticipated.This is the entire story.Now drawing conclusions as what I need to do.NOW,I haven't changed the REINDEX script which comprises DBCC DBREINDEX on all tables.UPDATE STATISTICS on all tables.Recompile all procedures.UPDATE USAGE on the database.DBCC CHECKDB on the database.I have created the maintenance Plan driven SQL AGENT Job to perform this.Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Defragmentation