3

I came across this issue while troubleshooting a problem that I described in another post, but I thought this deserves a post of its own.

I'm trying to reclaim some unused space in a table. This is the current disk usage report for the table:

Unused Space

This is the current disk usage report for the database: Unused Space

As an experiment, I did the following:

  1. Used the bcp utility to export the table contents:
EXECUTE sys.xp_cmdshell 'bcp MyDb.dbo.MyTable out c:\bcp\MyTable.bcp -n -S MyServer -T'
  1. Created a new database.
  2. Created the table with its schema in the new database:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyTable](
    [Column1]  [int] NOT NULL,
    [Column2]  [int] NOT NULL,
    [Column3]  [int] NOT NULL,
    [Column4]  [bit] NOT NULL,
    [Column5]  [tinyint] NOT NULL,
    [Column6]  [datetime] NULL,
    [Column7]  [int] NOT NULL,
    [Column8]  [varchar](100) NULL,
    [Column9]  [varchar](256) NULL,
    [Column10] [int] NULL,
    [Column11] [varbinary](max) NULL,
    [Column12] [varchar](max) NULL,
    [Column13] [varchar](100) NULL,
    [Column14] [varchar](6) NULL,
    [Column15] [int] NOT NULL,
    [Column16] [bit] NOT NULL,
    [Column17] [datetime] NULL,
    [Column18] [varchar](50) NULL,
    [Column19] [varchar](50) NULL,
    [Column20] [varchar](60) NULL,
    [Column21] [varchar](20) NULL,
    [Column22] [varchar](120) NULL,
    [Column23] [varchar](4) NULL,
    [Column24] [varchar](75) NULL,
    [Column25] [char](1) NULL,
    [Column26] [varchar](50) NULL,
    [Column27] [varchar](128) NULL,
    [Column28] [varchar](50) NULL,
    [Column29] [int] NULL,
    [Column30] [varchar](max) NULL,
 CONSTRAINT [PK] PRIMARY KEY CLUSTERED 
(
    [Column1] ASC,
    [Column2] ASC,
    [Column3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Column4]  DEFAULT (0) FOR [Column4]
GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Column5]  DEFAULT (0) FOR [Column5]
GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Column15]  DEFAULT (0) FOR [Column15]
GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Column16]  DEFAULT (0) FOR [Column16]
GO
  1. Inserted the exported data into the new table:
BULK INSERT dbo.MyTable FROM 'c:\bcp\MyTable.bcp' 
WITH (
DATAFILETYPE = 'native', 
ORDER(Column1,Column2,Column3), 
TABLOCK, 
KEEPIDENTITY
)

Now here are the disk usage reports for the new database:

enter image description here enter image description here

We can see that we've eliminated most of the unused space, but the space for the data has gone way up, from 4.5 GB to 8.2 GB, resulting in a negligible change in the overall space.

So, why is this happening? Is this kind of outcome expected?

Ken
  • 193
  • 1
  • 6

0 Answers0