| Author |
Topic |
|
BlackKnight
Starting Member
8 Posts |
 Posted - 2010-02-23 : 06:34:51
|
| Hi,I've recently run into a very strange problem with one of our CRM databases.In particular one of our tables has grown to 60Gb (data size), as can be confirmed by the file size, and results of sp_spaceused.However there are only 515,000 rows in the table, with only 3 large columns (text, nvarchar(max)).Doing a select sum(datalength(col_name)) from bigtable on all columns only adds up to 12Gb!By duplicating the table, migrating the data across, then dropping the original, the space then adds up. But this is a major inconvenience.Does anyone have any ideas where all this space has gone?!Thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-23 : 06:52:22
|
| "as can be confirmed by the file size"Might be empty space in the file that you are seeing?What did sp_SpaceUsed say?You might want to try this script that shows the size of each table in the DBhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762 |
 |
|
|
BlackKnight
Starting Member
8 Posts |
Posted - 2010-02-23 : 07:18:18
|
| sp_spaceused says it's using 60Gb.The script you gave said the same. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-23 : 09:34:18
|
| indexes? where is the LOB data stored? and WHY are you using text data type by the way?post output of sp_spaceusedby the way, there are lots of objects besides tables in your db, but not likely to be 48 GB worth...still no reason to expect the size of tables to add up to the size of the db |
|
|
|
BlackKnight
Starting Member
8 Posts |
Posted - 2010-02-23 : 09:45:01
|
quote:
Originally posted by russell
indexes? where is the LOB data stored? and WHY are you using text data type by the way?post output of sp_spaceusedby the way, there are lots of objects besides tables in your db, but not likely to be 48 GB worth...still no reason to expect the size of tables to add up to the size of the db
Output of sp_spaceused:name rows reserved data index_size unusedAsyncOperationBase 515220 65347064 KB 64234528 KB 1001160 KB 111376 KB quote:
indexes? where is the LOB data stored?
The table data and clustered index is on a separate file and filegroup. The other indexes are on the primary filegroup.quote:
and WHY are you using text data type by the way?
The table is a default CRM table, we have no choice over the column types. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-23 : 10:36:01
|
| Not sure of my facts here, but if you are using TEXT datatype with small amounts of data in them then I think the "rounding-up-to-a-whole-page" effect may be significant.But an empty table should be .... empty!Run DBCC CHECKDB to check there is nothing untoward in the database perhaps? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-02-23 : 12:03:21
|
| You can also run into issues like this if you do not have a clustered index on the table to be able to do a physical reorganization.The space can also be used by TEXT columns that are blank ( = '') instead of null. Each blank TEXT column will still use 8K.CODO ERGO SUM |
|
|
|
BlackKnight
Starting Member
8 Posts |
Posted - 2010-02-23 : 12:04:29
|
| DBCC CHECKDB returned:DBCC results for 'AsyncOperationBase'.There are 515535 rows in 492463 pages for object "AsyncOperationBase".Almost as many pages as rows. This must be part of the reason, but why is it happening and what can be done... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-23 : 12:12:50
|
| Your post may have crossed with MVJ's?If the table is empty can you Drop it and Recreate it? (or are there FKeys that would make that a bit tricky?) |
|
|
|
BlackKnight
Starting Member
8 Posts |
Posted - 2010-02-23 : 12:35:59
|
We did drop and recreate it - that's the only way we could get it back down to size.(We had to drop and recreate the foreign keys as well).However it is now back up to 60Gb.quote:
The space can also be used by TEXT columns that are blank ( = '') instead of null. Each blank TEXT column will still use 8K.
But even if each row was taking a full 8k page, that would still amount to less than 4Gb! There are only about 515,000 rows.The table does have a clustered index which we have rebuilt several times, but has no effect. |
|
|
|
|
Table size does not add up