Unused Space in Tables with NTEXT columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Unused Space in Tables with NTEXT columns

Author

Topic

Martinator
Starting Member

4 Posts

Posted - 2009-02-03 : 12:30:50

Hi everyone, I have a 50 GIG database that is showing a lot of unused space in the Disk Usage by Top Table report.

The top 5 space using tables all have ntext columns and show unused space of 65-70% for a total of about 34 Gig.

Table 1 - Rows: 1,996,855 Reserved: 12,771,944 Data: 4,224,288 Index: 176 Unused: 8,547,480
another query shows average bytes per row of 6550 and average unsued bytes per row of 4382 for this table.

Any ideas on why this is happening, what I can do to prevent that and how I can recapture the unsused space would be greatly appreciated.

Thank you.

Martin Kultermann
eStar Communications
Office: +1 (312) 275-0291
Email: Martinator@eStarMail.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-03 : 12:36:06

NTEXT columns only occupy 16 bytes each as a pointer.
The data in NTEXT are stored "off-page".

E 12°55'05.63"
N 56°04'39.26"

Martinator
Starting Member

4 Posts

Posted - 2009-02-03 : 13:13:21

Hi Peso, thanks, but how does that explain the space?

The table I provided the stats for only has an identity key and an ntext column. Nothing else.

Does the average row size include the size of the off-page object?

Where is the unused space?

Martin Kultermann
eStar Communications
Office: +1 (312) 275-0291
Email: Martinator@eStarMail.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-03 : 13:17:52

To be more precise, the values for ntext columns are stored in the same data file as the rest of the database, just not together with table datapages.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-03 : 13:20:07

also see

http://technet.microsoft.com/hi-in/library/cc917636(en-us).aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources