February 25, 2010 at 4:08 pm
Hello All,
My DB has had only minor growth for the last few years. 2 weeks ago I noticed that the DB has grown by about 60GB.
I looked at the table using BigTables and here is the output. The only real data change I see is the largest table went from 10 million rows to 20 million rows (I have stopped the process that caused this). The doubling of that data does not equal the amount the DB grew.
database_name database_size unallocated space
----------------------------------------------------
DBName 118210.25 MB 81.14 MB
reserved data index_size unused
------------------ ------------------ ------------------ ------------------
120883888 KB 115605536 KB 4493296 KB 785056 KB
Table_Name rows reserved_KB data_KB index_size_KB unused_KB
------------- --------- ------------ ----------- -------------- -----------
A 20031089 12112528 8588416 3466632 57480
B 6051671 417056 207544 208648 864
C 3741911 681848 275440 406208 200
D 856797 81616 37448 31800 12368
E 658028 608424 491112 116224 1088
F 543564 148240 77808 56672 13760
G 415196 138200 79712 58344 144
H 141183 128856 46472 65608 16776
I 135574 81264 32184 34608 14472
J 105054 181584 158680 11264 11640
k 91573 20056 10880 8936 240
The rest of the tables are small.
Ideas?
TIA,
Ron
February 25, 2010 at 4:16 pm
Hi there,
Can you give us some information about the process you think is causing this behavior?
José Cruz
February 25, 2010 at 4:23 pm
The process I stopped is a log of what a service is doing. The service went haywire and was dumping errors into the table (lots of them). This is what I initially thought caused the growth.
The amount of space that that table grew in no way comes close to the 60GB.
February 25, 2010 at 4:40 pm
Did the database grow? Could autogrow have jumped by xxGB?
February 25, 2010 at 5:35 pm
Autogrowth is set to 10%.
I looked at the 20 million row table and it has a text field in it. 19.9 million of those rows the text size is under 210 characters.
February 25, 2010 at 11:35 pm
First, I'd change the autogrowth to a fixed number of mega-bytes. Do you really want your 20GB database to grow by 2GB when it needs to?
It sounds like you've experienced a "run away" query which may be caused by an accidental cross-join possibly caused by changes in the data. Of course, you need to figure out which query caused that, what the problem with that query is, and fix it.
The other thing that may have happened is simply because the way the data doubled. If new data was inserted into tables that have clustered indexes that are not in the same logical order as the data being inserted, massive page splits may have occurred. The same goes for the non-clustered indexes. IIRC correctly, they split at an extent level. That means that you could have a data base mostly full of reserved but unused space. Rebuilding all the indexes (clustered first) will likely make the reservered space drop like a rock.
If you decide that the database needs to be shrunk after this episode (I'd just leave it for future growth instead of shrinking it), then be advised that you'll need to rebuild all of the indexes again after the shrink because the shrink moves data on the disk and it's almost always the wrong thing to do.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 26, 2010 at 9:29 am
I will reset the growth to a fixed amount (don't need it to grow that much when it needs to).
I have seen the issue with the massive page splits and this does not look like it. I would see a large number in the unused column would I not?
Table_Name rows reserved_KB data_KB index_size_KB unused_KB
TblName 19867394 119678248459664344696061200
I'll go through and rebuild the indexes on this table anyway.
When I do shrink (and I hope to sometime), I'll be sure to rebuild all the indexes.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply