Table size showing 7GB which is having ntext column with 1 million rows

Question

Post reply

Table size showing 7GB which is having ntext column with 1 million rows

sri76

Say Hey Kid

Points: 666
More actions
July 24, 2009 at 12:06 am

#136600

Hi,
I am using SQL Server 2005 and i have a table having ntext column and the table contains 1 million rows. The size of table is huge around 7 GB. As ntext is deprecated in SQL Server 2005, i altered the column to nvarchar(max), now the size went up to 8.5GB, is there any way to shrink / reduce the size of the table.
Please help, this is a production database.
Thanks in advance
Sri

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Unless you are having disk space issues on your server, I would not shrink the database.

sri76 Say Hey Kid Points: 666 More actions · Answer 2

Ok, i have another question. Whether a table with ntext column having 1 million records occupy 7GB space?

shiniti Old Hand Points: 351 More actions · Answer 3

It could be due to fragmentation. If your table is used for a lot of insert/delete and also is a heap table, you can expect this kind of behavior. Better to check it =)

Regards.

sri76 Say Hey Kid Points: 666 More actions · Answer 4

Hi,

The table will meet with more number of insert statements and my table is not a heap table and when i run DBCC showcontig on the table i got the following

DBCC SHOWCONTIG scanning 'Indexer' table...

Table: 'Indexer' (2089058478); index ID: 1, database ID: 22

TABLE level scan performed.

- Pages Scanned................................: 7646

- Extents Scanned..............................: 973

- Extent Switches..............................: 977

- Avg. Pages per Extent........................: 7.9

- Scan Density [Best Count:Actual Count].......: 97.75% [956:978]

- Logical Scan Fragmentation ..................: 5.21%

- Extent Scan Fragmentation ...................: 99.59%

- Avg. Bytes Free per Page.....................: 44.3

- Avg. Page Density (full).....................: 99.45%

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Srikanth Anumalasetty (7/24/2009)
Ok, i have another question. Whether a table with ntext column having 1 million records occupy 7GB space?

Consider this, each row of the ntext (or nvarchar(max)) column could potentially hold 2 GB of data. How large could the database become with a million rows of data?

So yes, a table with an ntext or nvarchar(max) column could easily occupy 7 GB of space.

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 6

Just curious- is there any explanation why replacing "ntext" data type by "nvarchar(max)" results in table size increasing? Thanks,

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Could be how the data was moved around in the datapages as it was converted. More pages could have been allocated to store the same amount of data.

Yuri55 SSCrazy Eights Points: 8449 More actions · Answer 8

Lynn, sorry, just to clarify- theoretically (what actually I am asking about 🙂 one should not win/lose (from storage point of view) anything replacing ntext to nvarchar(max)? Thanks