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?

1
  • Are both SQL Server the same version? What is the fragmentation of the page in the first and in the second table? Sep 25, 2020 at 6:41

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.