Post reply

Unexpected Growth in one DB

  • July 21, 2009 at 10:05 am

    #133111

    I have a DB that unexpectedly grew from 245 GB to almost 340 GB (90 GB growth) in a day and I haven't been able to figure out what caused it. I checked records growth in the tables and the seem Ok, also there are no indexes creation reported in the default trace.

    Any ideas? is there a way to find out what caused it?

    Thanks

  • July 21, 2009 at 10:10 am

    #1027751

    Any index rebuilds or large data imports?

  • July 21, 2009 at 10:19 am

    #1027761

    Is the 90GB growth on mdf or ldf or is it combined?

    Check for the space usage of all the tables, check for reserved space here.

  • July 21, 2009 at 10:24 am

    #1027767

    No data imports, or index creation; I have been logging records growth for all tables and nothing out of the ordinary; the growth was only in the mdf file.

  • July 21, 2009 at 10:28 am

    #1027772

    What are the Autogrowth settings for your database?

  • July 21, 2009 at 10:28 am

    #1027773

    Sounds like data growth to me. Possibly have page splits in some of your tables and indexes.

  • July 21, 2009 at 10:30 am

    #1027779

    Autogrowth is set to unrestricted growth by 500 MB

  • July 21, 2009 at 10:33 am

    #1027781

    Just a blind guess

    Has the rebuild index job ran with a change in the fill factor from 5-10% or something like that.

  • July 21, 2009 at 10:40 am

    #1027790

    I was thinking massive rebuild of indexes as well.

  • July 21, 2009 at 11:05 am

    #1027811

    That is the weird thing, I have a maintenance plan that does all the rebuilding and reorganizing of the indexes on weekends and this happened on a Thursday; also when I checked the information in the SQL default trace no activity on the indexes is shown for that day.

  • July 21, 2009 at 12:24 pm

    #1027840

    Hi,

    Check your Autogrowth mdf and ldf files, I had a same issue last time, it it is set up % then it happend, just change into ??MB

  • July 21, 2009 at 6:04 pm

    #1028036

    It could be the same problem/solution as on the following thread...

    http://www.sqlservercentral.com/Forums/Topic756703-146-1.aspx

    --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)

  • July 22, 2009 at 12:59 am

    #1028104

    Steve,

    Does it mean that when we rebuild the index for large DB as 1000GB, we should make the recovery model = SIMPLE

    -LK

  • July 22, 2009 at 8:55 am

    #1028375

    That's up to you. If you set it to simple, then the log space is still required (depending on how much space is needed for that operation), but it's cleared afterwards. However you then need a backup before and after the operation to be sure you can recover.

    The best solution is to learn how much space you need, and keep that in the log.

  • July 22, 2009 at 10:21 am

    #1028494

    luckysql.kinda (7/22/2009)

    Steve,

    Does it mean that when we rebuild the index for large DB as 1000GB, we should make the recovery model = SIMPLE

    -LK

    That might break your backup chain and wouldn't be allowed if replication is in effect. On 2k5, you can rebuild indexes with the "Sort In TemDB" option.

    --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)

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

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