July 24, 2009 at 12:06 am
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
July 24, 2009 at 12:28 am
Unless you are having disk space issues on your server, I would not shrink the database.
July 24, 2009 at 12:48 am
Ok, i have another question. Whether a table with ntext column having 1 million records occupy 7GB space?
July 24, 2009 at 4:53 am
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.
July 24, 2009 at 5:50 am
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%
July 24, 2009 at 7:11 am
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.
July 24, 2009 at 7:19 am
Just curious- is there any explanation why replacing "ntext" data type by "nvarchar(max)" results in table size increasing? Thanks,
July 24, 2009 at 7:29 am
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.
July 24, 2009 at 7:39 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply