September 20, 2010 at 10:42 pm
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;-)
September 20, 2010 at 10:48 pm
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.
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
September 20, 2010 at 10:52 pm
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;-)
September 20, 2010 at 11:00 pm
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.
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
September 20, 2010 at 11:13 pm
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;-)
September 20, 2010 at 11:19 pm
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.
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
September 21, 2010 at 12:14 am
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;-)
September 21, 2010 at 12:17 am
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?
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
September 21, 2010 at 12:45 am
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;-)
September 21, 2010 at 1:03 am
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?
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
September 21, 2010 at 2:56 am
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
September 21, 2010 at 3:59 am
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?
September 21, 2010 at 4:16 am
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;-)
September 21, 2010 at 10:28 am
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.
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
September 21, 2010 at 11:18 am
Craig Farrell (9/21/2010)
http://msdn.microsoft.com/en-us/library/ms188796.aspxThe 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
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply