HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

A large percentage of unused space in the table

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
spacethetableunusedlargepercentage

Problem

SQL Server 2005.

I have a table size 16GB. Table has 14 GB of free space.

sp_spaceused 'FREECLIENTDOC'

name            rows   reserved      data          index_size  unused
--------------- ------ ------------- ------------- ----------- ------------
FREECLIENTDOC   72407  16168232 KB   1742544 KB    30696 KB    14394992 KB


Indexes do not take up much space:

DBCC SHOWCONTIG scanning 'FREECLIENTDOC' table...
Table: 'FREECLIENTDOC' (659533433); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 3508
- Extents Scanned..............................: 457
- Extent Switches..............................: 2555
- Avg. Pages per Extent........................: 7.7
- Scan Density [Best Count:Actual Count].......: 17.18% [439:2556]
- Logical Scan Fragmentation ..................: 71.18%
- Extent Scan Fragmentation ...................: 69.80%
- Avg. Bytes Free per Page.....................: 1981.6
- Avg. Page Density (full).....................: 75.52%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


Perhaps the problem in the fields of image types.
How to determine what is in an unused space?
I'm interested in the general case definition table space.
This table - a example.

Solution

If you have deleted a lot of data, this isn't recovered until you rebuild.

ALTER INDEX PK_PrimaryKeyName ON dbo.FREECLIENTDOC REBUILD;


Please see all the options in the documentation before just running the default.

Also, IMAGE is deprecated - if you are storing files in your database tables, you should look into VARBINARY(MAX) - on newer versions of SQL Server you can look at FILESTREAM or FILETABLE.

Code Snippets

ALTER INDEX PK_PrimaryKeyName ON dbo.FREECLIENTDOC REBUILD;

Context

StackExchange Database Administrators Q#64494, answer score: 3

Revisions (0)

No revisions yet.