Datafile increased due to DBCC

  • Hi ,

    i have scheduled a job for dbcc checkdb with data_purity which runs weekly on Sunday.but on first run i found that one of the data file got increased by 15 GB.(prior size = 52 GB) so it became 67 GB. i am not sure how it happened.Any idea?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • DBCC CHECKDB uses an internal database snapshot for the transactional consistency needed to perform these checks.

    What's your free space like at the moment? 🙂 It may have just needed room to work.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Craig Farrell (9/20/2010)


    What's your free space like at the moment? 🙂 It may have just needed room to work.

    thanks for quick response :-).15 GB space to work ? additionally i think it should affect log file not the data file ? what do you say ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • IF it's taking largish snapshots to deal with multiple enforced relationships... yeah, I could see it, especially if you're meticulous about FK relationships.

    As to affecting the log, hm... That it was run, sure, I could see entries for that and possibly some snapshot information, but DBCC CheckDB is just a shorthand way of running CheckAlloc, CheckTable, and CheckCatalog. I can't see these doing any serious log work... unless something was broken and it went and did its job.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Some more information about the concerned database :

    1. Data file growth has been set to : 1 MB.

    2. it contains 18000 tables,there are couple of heavy table like 18 -19 GB.

    3. this is report database where data gets populated through BCP insertions from csv file etc.

    4. this database also contains 2000 heap tables.

    5. there is no foreign key relationship concept.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh (9/20/2010)


    .

    2. it contains 18000 tables,there are couple of heavy table like 18 -19 GB.

    There's your 15 gig bump, needed snapshot room.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Craig Farrell (9/20/2010)


    There's your 15 gig bump, needed snapshot room.

    can you please brief it ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Okay, what the check is trying to do is be able to scan the table, end to end, and make sure it's correct, allocated, etc etc.

    To do this, without screwing up workload any more then its going to, it takes a snapshot of the recordset, meaning it makes a copy of it in the database. So, it's going to need the room to do this. If you were already running tight in the space of the 52 gigs that were already there, say, only 3-4 gigs worth of room left, and it needed to snapshot (copy) an 18 gig table, it's going to expand the db until it has enough room to work in.

    That help?


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Craig Farrell (9/21/2010)


    To do this, without screwing up workload any more then its going to, it takes a snapshot of the recordset, meaning it makes a copy of it in the database. So, it's going to need the room to do this. If you were already running tight in the space of the 52 gigs that were already there, say, only 3-4 gigs worth of room left, and it needed to snapshot (copy) an 18 gig table, it's going to expand the db until it has enough room to work in.

    That help?

    thanks for explanation.but why is going to permanent increment of data file?, that should be released after the operation by Sql server. second, how should be reclaim this space ? by shrinking and then indexdefrag or rebuild ? but is this a good /feasible approach on prod environment specially in case of database in starving state ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Well, once the db grows to a size it doesn't autoshrink, usually. If you're running DBCC regularly you'll want to leave the space there so it doesn't have to re-grow when you run it again.

    But yes, to recover the space you'd shrink again.

    What do you mean by: Starving State?


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Craig Farrell (9/21/2010)


    To do this, without screwing up workload any more then its going to, it takes a snapshot of the recordset, meaning it makes a copy of it in the database.

    Um, sorry, no.

    CheckDB uses a hidden database snapshot. It doesn't take copies of tables within the DB its working on.

    So, it's going to need the room to do this. If you were already running tight in the space of the 52 gigs that were already there, say, only 3-4 gigs worth of room left, and it needed to snapshot (copy) an 18 gig table, it's going to expand the db until it has enough room to work in.

    CheckDB, run without options, should not make any changes at all to the database. It just checks.

    Bhuvnesh, sure there wasn't an index rebuild job running around the same time? Index rebuild will grow the DB, checkDB should not make any changes at all. It just reads and checks pages.

    It can cause TempDB to grow, especially if you have indexed views as it re-materialises the view in TempDB to ensure that it's still valid, but there's no reason for it to grow the database that it's checking. I've never seen it do that, even when run on massive databases.

    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
  • Bhuvnesh (9/20/2010)


    Some more information about the concerned database :

    1. Data file growth has been set to : 1 MB.

    ...

    While it's not related to the specific question you raised - I would address this as well. Try to identify your likely growth requirements over a period of time, increase database size accordingly, then set Autogrowth to a more reasonable size should it be needed. I'd do the same for the logs too (if necessary). Has this database been set up using default sizing?

  • Andrew Gothard-467944 (9/21/2010)


    Has this database been set up using default sizing?

    this DB belongs to our new clients.So cant comment much on it . but it seems that this is a default setup configuration.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • GilaMonster (9/21/2010)


    Um, sorry, no.

    CheckDB uses a hidden database snapshot. It doesn't take copies of tables within the DB its working on.

    Because there's no way I'm gonna disagree with you until I've made dang sure I'm right, I went off and did some more research and... yeah, you're right. :blush:

    http://msdn.microsoft.com/en-us/library/ms188796.aspx

    The following DBCC commands operate on an internal read-only database snapshot that the Database Engine creates.

    Sorry Bhuvnesh, ignore everything I said about this. I misunderstood something I read in another article.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Craig Farrell (9/21/2010)


    http://msdn.microsoft.com/en-us/library/ms188796.aspx

    The following DBCC commands operate on an internal read-only database snapshot that the Database Engine creates.

    If you're interested, the snapshot is created using the NTFS alternate stream feature, and is created as an alternate stream of the data files, hence same drive and same directory as the data files are.

    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 15 (of 21 total)

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