Post reply

db size grow when using index defrag-HELP!!

  • October 13, 2008 at 1:58 am

    #75339

    Hi All,

    I ran an index defrag on my db which was 71gb, it has now become 91 gb.. Is this normal???

    Thanks for your help.

  • October 13, 2008 at 2:12 am

    #884003

    It is normal. No problem. Tempsize come down after defrag.

  • October 13, 2008 at 2:18 am

    #884007

    after that use DBCC UPDATEUSAGE ([DBNAME])

    🙂

  • October 13, 2008 at 2:27 am

    #884009

    amit (10/13/2008)

    Hi All,

    I ran an index defrag on my db which was 71gb, it has now become 91 gb.. Is this normal???

    Yes. SQL needs space to rebuild the index. Don't worry about it.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 13, 2008 at 2:29 am

    #884011

    What has grown exactly? Is it data or only the log file??

    Is there any way to gain back the space..?

  • October 13, 2008 at 2:46 am

    #884016

    Why do you need to reclaim the space? If you do, the DB is just going to grow again next time you rebuild indexes.

    Is it the data or the log file that's grown?

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 13, 2008 at 3:55 am

    #884043

    The data has not grown by much. May be only a few rows only. I need to regain this space for other db on the server. Is there any way to implement this? Or is there any checkpoint or do i truncate the transaction log itself?

  • October 13, 2008 at 4:55 am

    #884061

    Do BACKUP LOG.

    _____________
    Code for TallyGenerator

  • October 13, 2008 at 8:01 am

    #884146

    What recovery model is the database in?

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 13, 2008 at 7:35 pm

    #884386

    I believe you can add something like "Sort In TempDB" to your commands and only TempDB will change size, then. Lookup the reindexing commands you're using in Books Online and see.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 14, 2008 at 2:23 am

    #884461

    HI Gail,

    The recovery model is full. I hope this help.

    Thanks

  • October 14, 2008 at 3:04 am

    #884481

    How often do you have log backups running?

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 14, 2008 at 4:08 am

    #884500

    There is no transaction logs backup running. Is there any script to complete the same? Because i had scheduled this index defrag. I can complete the transaction log back up in the stored procedure itself,.

    Thanks for your replies

  • October 14, 2008 at 5:15 am

    #884534

    amit (10/14/2008)

    There is no transaction logs backup running. Is there any script to complete the same? Because i had scheduled this index defrag. I can complete the transaction log back up in the stored procedure itself,.

    Thanks for your replies

    Then, you don't need FULL recovery... Set it to "SIMPLE" and make your life easier.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 14, 2008 at 5:28 am

    #884541

    amit (10/14/2008)

    There is no transaction logs backup running.

    Then why is the DB in full recovery? If you're in full recovery and have no log backups running, the transaction log will grow without bound. Yiou've got two options here.

    You can switch to simple recovery. This is the easiest and it means you won't have to worry at all about the tran log. The downside of this is that you will only be able to recover the database to the last full backup. So if you have daily full backups and the drive fails 10 min before the full backup would run, you will have lost a full day's worth of data. Is that acceptable?

    If it is, switch the SB to simple recovery. If not, then set up regular transaction log backups. The interval should be determined by the amount of data that you're willing to loose in the case of a disaster.

    The easiest way is to use the maintenance plans to set up the log backups.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply