DB Growth Issue
-
I have done this experiment on one of the database tables. There is table called build havinf nvText Field with large no of records. I want to drop that column and recover space. These are the results I got.
SP_SPACEUSED ‘BUILD’ Results
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
Build 663211 341440 KB 339464 KB 1944 KB 32 KB
Build 663211 608280 KB 604744 KB 3456 KB 80 K
Build 663211 124096 KB 123392 KB 712 KB -8 KB
Can anyone please explain me after executing step 3 i.e shrink database, data column as well as index_size shows an increased figure whereas logically it should be a reduced figure.
Regards,
Harcharan
-
Hi Harcharan,
DBCC SHRINKDATABASE will shrink the database files, not the tables in the database. To do that, SQL Server may move your data around. This is one reason why this option is not used (generally) in production databases - it can *create* fragmentation, for example. I can't explain what's happened in this case (which is frustrating ... I should "get it," but it's just not clicking for me ... late night maybe?
) but at best I would say that you should not rely on DBCC SHRINKDATABASE (which shrinks physical files in the OS) to shrink tables (which are logical items inside the database). It's kind of analogous to the way people used to rely on GROUP BY to produce sorted result sets ... and then along came parallel query processing, and that strategy was trashed. The moral is the same in each case: don't count on an undocumented result from any command. DBCC SHRINKDATABASE is not documented to reduce the size of logical objects such as tables.Hope this helps. I also hope that somebody (maybe me) will be able to specifically explain the size increase.
By the way, I see you rebuilt the indexes with a fillfactor of 70. What was it at the beginning of the test?
Cheers,
Chris
-
Hey Chris !
Thanks for your reply. The fill factor was 70 at the initial stage itself. what I feel is that(sounding paranoid) ...is it the same way that we delete some files in windows explorere and it goes to Recycle Bin which is an overhead. Anyways ...Let me know exactly why it happened in my experiment which increased the data figure after shrinking the data base.
Regards,
harcharan
-
What version of SQL are you running.
There is a problem (bug) in 7.0 that would cause this to happen.
After much effort back and forth with MS, they confirmed it a bug.
It is fixed in 2K
KlK -
Harcharan,
I would retract my statement from last night that I should be able to explain what's happening. I can't. If I were you, I would follow up on kknudson's idea that this may be a bug. But I also hope that you will consider my previous statement about not expecting DBCC SHRINKDATABASE to shrink tables for you.
Good luck - keep us posted.
Chris
-
IS DBCC CLEANTABLE REALLY USELESS??
-
I carried out another round of testing with the same test table at offshore. Here are the steps I performed and results I got. Whereas DBCC CLEANTABLE appears to be giving no space recovery, only the DBCC DBREINDEX clears some space and that too only when indexes are there. If you notice in the second run the %age clearing of space is more as compared to first run. The Difference between the two runs is the variation of steps I performed i.e. I performed DBREINDEX ahead of DBCC Cleantable although understandably DBCC CLEANTABLE is useless.
These are my observations I wanted to share with you. Let me know your thoughts
FIRST RUN
1 sp_spaceused 'build'
name rows reserved data index_size unused
Build 663000 298536 KB 257208 KB 41248 KB 80 KB
2 alter table build
drop column nvTExt
Build 663000 298536 KB 257208 KB 41248 KB 80 KB
3 DBCC CLEANTABLE (EnglishTest, Build)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Build 663000 298536 KB 257208 KB 41248 KB 80 KB
4 DBCC Updateusage ( 'EnglishTest', 'build')
Build 663000 299344 KB 258016 KB 41256 KB 72 KB
5 DBCC DBREINDEX('Build','',70)
Build 663211 178432 KB 123392 KB 55048 KB -8 KB
6 DBCC DBREINDEX('Build','',0)
Build 663211 178432 KB 123392 KB 55048 KB -8 KB
SECOND RUN
1 sp_spaceused 'build'
name rows reserved data index_size unused
Build 663000 297064 KB 255760 KB 41232 KB 72 KB
2 alter table build
drop column nvTExt
Build 663000 297064 KB 255760 KB 41232 KB 72 KB
3 DBCC DBREINDEX('Build','',0)
Build 663211 162432 KB 123392 KB 39024 KB 16 KB
4 DBCC CLEANTABLE (EnglishTest, Build)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Build 6663211 162432 KB 123392 KB 39024 KB 16 KB
5 DBCC Updateusage ( 'EnglishTest', 'build')
Build 663211 162448 KB 123392 KB 39024 KB 32 KB
6 DBCC DBREINDEX('Build','',70)
Build 663211 178432 KB 123392 KB 55048 KB -8 KB
THIRD RUN ( WITHOUT INDEXES)
1 sp_spaceused 'build'
name rows reserved data index_size unused
Build 663000 260944 KB 258008 KB 8 KB 2928 KB
2 alter table build
drop column nvTExt
Build 663000 260944 KB 258008 KB 8 KB 2928 KB
3 DBCC CLEANTABLE (EnglishTest, Build)
Build 663000 260944 KB 258008 KB 8 KB 2928 KB
4 DBCC DBREINDEX('Build','',0)
Build 663000 260944 KB 258008 KB 8 KB 2928 KB
5 DBCC Updateusage ( 'EnglishTest', 'build')
Build 663000 260992 KB 258016 KB 8 KB 2968 KB
6 DBCC DBREINDEX('Build','',70)
Build 663000 260992 KB 258016 KB 8 KB 2968 KB
-
What is the datatype of the dropped column (nvText)? I think that DBCC CLEANTABLE is supposed to only work with VARCHAR, TEXT, and NTEXT.
-Chris
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply