SQL Server Performance

After DBCC DBReindex, Space double

Discussion in 'General DBA Questions' started by whygh, Nov 27, 2006.

  1. whygh New Member

    Hi,

    I have a 47G database. I did a "DBCC Dbredindex" on one table. After 4 hours, It did not finish, So I canceled it. Then I check the space of database. It is 89G.

    I used the "Sp_spaceused " to check all the table space, then add them together. It is around 47G.

    What is going on to my database? what objects take other 40G space? how can I shrink this database and do not affect performance?

    Thanks in advance.
  2. Luis Martin Moderator

    Do you have recovery model full?

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  3. satya Moderator

    It is quite common to double-size the transaction log for such operations using DBCC and reindexing.
    Check what is the size of transaction log by using enterprise manager taskpad view, you can use DBCC SHRINKFILE to shrink the log.
    REfer to books online for more information.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  4. MohammedU New Member

    I am assuming your recovery model is full other wise your tlog should not grow this much.

    I think tlog backup was not running during the time of DBCC DBREINDEX or might be full backup was running while DBCC DBREINDEX was running which blocks tlog backup.

    Make sure you run tlog backup every 30 minutes OR run the tlog backup after each table reindex.

    use DBCC SHRINKFILE command to shrink the log file to a reasonable size.



    Mohammed U.
  5. satya Moderator

    ... assuming your recovery model is full other wise your tlog should not grow this much.

    Not quite right, even though with SIMPLE recovery model and having AUTOGROW enabled the database transaction log will be wild to grow...nothing to stop.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  6. whygh New Member

    sorry, I forfet to mention,
    1. Database is in simple recovery mode, Autogrow
    2. It is not log file to grow. it is data File. Log file grow to around 1G. I aleady use "Shrinkfile" to shrink it.

    I know how to use shrinkfile. But I do not know what is going to happen if I shrink the datafile?

    Can I do it little by litte? like: dbcc shrinkfile(dbid,80000), every time shrink 10G , until it decrease to 50G?

    Thanks for all your reply.
  7. MohammedU New Member

    ...Not quite right, even though with SIMPLE recovery model and having AUTOGROW enabled the database transaction log will be wild to grow...nothing to stop.

    I thought it is tlog grown to 89 GB... I was trying to tell DBCC DBREINDEX only will not increase the log size to 89 GB for 40 GB database...

    I don't think DBCC DBRINDEXED caused your db to grow 89 GB unless you have 30+ GB size one table...

    Nothing is going to happend but you may see some performance impact on high OLTP server...
    when you run the shrinkfile it copies the data to different location on the disk before it free up space to OS. You can all at once or 10 GB at a time... I will prefer 10 GB at a time...

    Read the following article which has detailed information...

    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

    All work done by DBCC DBREINDEX occurs as a single, atomic transaction. The new indexes must be completely built and in place before the old index pages are released. Performing the rebuild requires adequate free space in the data file(s). With not enough free space in the data file(s), DBCC DBREINDEX may be unable to rebuild the indexes, or the indexes may be rebuilt with logical fragmentation values above zero. The amount of free space needed varies and is dependent on the number of indexes being created in the transaction. For clustered indexes, a good guideline is: Required free space = 1.2 * (average rowsize) * (number of rows).

    For nonclustered indexes, you can predict free space necessary by calculating the average row size of each row in the nonclustered index (length of the nonclustered key plus the length of clustering key or row ID). Then multiply that value by the number of rows. If you rebuild indexes for an entire table, you will need enough free space to build the clustered index and all nonclustered indexes. Similarly, if you rebuild a nonunique clustered index, you will also need free space for both the clustered and any nonclustered indexes. The nonclustered indexes are implicitly rebuilt because SQL Server must generate new unique identifiers for the rows. When you use DBCC DBREINDEX, it is good practice to specify the index you want to defragment. This gives you more control over the operations being performed and can help to avoid unnecessary work.



    Mohammed U.
  8. satya Moderator

    What has been changed on table indexes as compared to previouslY?

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  9. whygh New Member

    Thanks.

    I shrunk my datafile to 60G.

    My steps:

    dbcc shrinkfile (dbID,85000) 2 seconds
    dbcc shrinkfile (dbID,80000) 2 seconds
    dbcc shrinkfile (dbID,75000) 2 seconds
    dbcc shrinkfile (dbID,70000) 2 seconds
    dbcc shrinkfile (dbID,65000) 2 seconds

    dbcc shrinkfile (dbID,60000) 16 minutes

    I will continute shrinking until it reach 50G




  10. whygh New Member

    quote:Originally posted by satya

    What has been changed on table indexes as compared to previouslY?

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.


    the index size is no change.

    the table size is around 7G. index size is around 20G. there are 6 indexes on it and no clustered index.
  11. Luis Martin Moderator

    quote:Originally posted by whygh

    Thanks.

    I shrunk my datafile to 60G.

    My steps:

    dbcc shrinkfile (dbID,85000) 2 seconds
    dbcc shrinkfile (dbID,80000) 2 seconds
    dbcc shrinkfile (dbID,75000) 2 seconds
    dbcc shrinkfile (dbID,70000) 2 seconds
    dbcc shrinkfile (dbID,65000) 2 seconds

    dbcc shrinkfile (dbID,60000) 16 minutes

    I will continute shrinking until it reach 50G

    But after a new reindex your data will grow again, and when you shrinkfile you loose all update statistics.






    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  12. whygh New Member


    I use "exec sp_spaceused" to check space for that big table, I did not see more space which it took than before.

    I am assuming that the database didn't release extra 40G space after I clicked cancel.

  13. MohammedU New Member

    Shrinkfile had taken couple of seconds to shrink 10 GB means there was nothing to move...
    SQL server will shrink your file once it is increased unless AUTO SHRINK option is enabled.
    But I don't understand what cuased your db data file to grow 40+GB...

    If you have time, patience and HW... you can restore this db and test it by running the dbcc dbreindex command and see how much it grows while DBCC DBREINDEX running.

    You can use the code from the following site to get the huge tables info...

    http://vyaskn.tripod.com/code/sp_show_huge_tables.txt


    Mohammed U.
  14. Luis Martin Moderator

    Also you can find an article by Tom Pullen(in our forum) to run reindex only when neccesary.

    I used a lot.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  15. satya Moderator

    I would suggest to run the SHRINK operation as a job and do not attempt to cancel the operation, as it will have reverse affect on sizes too.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

Share This Page