Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gpassfield
Starting Member
1 Post |
 Posted - 2010-10-22 : 09:00:36
|
| Hi Everyone,I am currently using the following script to re-index and clean up the log file for SQL 2000 and SQL 2005 database. It has been working fine.I have tried running this script on a SQL 2008 database and it actually increases the size of the data file considerably after re-indexing (this did not happen with the same database restored on SQL 2005)-- Instructions:-- Replace XXX with the name of the clients database-- Replace YYY with the name of the clients log file-- ----------------------------------------------------------------------------------USE XXXDUMP TRANSACTION XXX with no_log;DBCC dbreindex(dhl_sample_column_details,'',0);--repeat for all desired tablesdbcc shrinkfile(YYY, 50);DUMP TRANSACTION XXX with no_log;Any ideas how to achieve the same results and not have the data file grow?Cheers. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-10-23 : 15:22:34
|
| There is no way to achieve the same results and not have the data file grow or the log file grow. A reindex requires the following FREE space inside the files to complete successfully:1. 125% of the index size in the data file2. 100% of the index size in the log fileSo let's take an example. Let's say you have a 120GB database, with a 100GB data file and a 20GB log file. Now let's say that the index to be rebuilt is 25GB in size and your data file only has 10% free space (10GB). Your data file is going to grow an additional 15GB to accomodate the reindex. And your log file is going to expand another 5GB. There is no way around this. This is what is required to complete that index rebuild successfully. Since your reindex requires this extra space, do NOT shrink the files as this space will be required the next time it runs. Plus you are causing fragmentation by running DBCC SHRINKFILE, so you are not helping things.Instead of shrinking the files, add disk space to accomodate this process or redesign your database to avoid fragmentation.Now perhaps DBCC DBREINDEX is doing something different than ALTER INDEX (which is what you should be using for 2005/2008. The information I posted is true for ALTER INDEX and is directly from MS.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
|
|
|
Maintenance script increases database size.