How to regain disk space after dropping a column with MSSQL

You can regain the disk space only if you drop one of the following:

  1. varchar
  2. nvarchar
  3. varbinary
  4. text
  5. ntext
  6. image
  7. xml

Display the table structure & space used

  1. USE sql911;
  2. go
  3.  
  4. SELECT CAST(col.name AS VARCHAR(20)) AS "Column Name",
  5.        col.column_id,
  6.        CAST(SCHEMA_NAME(typ.schema_id) AS VARCHAR(10)) AS "Schema",
  7.        CAST(typ.name AS VARCHAR(12)) AS "Type",
  8.        typ.is_user_defined,
  9.        typ.is_assembly_type,
  10.        col.max_length,
  11.        col.PRECISION,
  12.        col.scale
  13. FROM sys.columns AS col
  14. JOIN sys.types AS typ ON col.user_type_id=typ.user_type_id
  15. WHERE col.OBJECT_ID = OBJECT_ID('sales02')
  16. ORDER BY 2;
  17. go
Column Name          column_id   Schema     Type         is_user_defined is_assembly_type max_length precision scale
-------------------- ----------- ---------- ------------ --------------- ---------------- ---------- --------- -----
sales_id             1           sys        int          0               0                4          10        0
sales_date           2           sys        datetime     0               0                8          23        3
sales_qty            3           sys        decimal      0               0                5          9         2
sales_price          4           sys        decimal      0               0                9          12        2
emp_short            5           sys        varchar      0               0                50         0         0
vartext              6           sys        varchar      0               0                500        0         0

(6 row(s) affected)
  1. SELECT  CAST(OBJECT_NAME(id) AS VARCHAR(12)) AS 'Table',
  2.         CAST(rowcnt AS VARCHAR(10)) AS '#Rows',
  3.         reserved * 8 AS 'Disk - Kb',
  4.         dpages * 8 AS 'Data - Kb',
  5.         (SUM(used) * 8) - (dpages * 8) AS 'Index - Kb'
  6. FROM sysindexes
  7. WHERE indid IN (0,1)
  8. AND   OBJECTPROPERTY(id, 'IsUserTable') = 1
  9. GROUP BY id, rowcnt, reserved, dpages
  10. ORDER BY 'Table';
  11. go
Table        #Rows      Disk - Kb   Data - Kb   Index - Kb
------------ ---------- ----------- ----------- -----------
DEPARTMENT   9          16          8           8
EMP_WORK     75         16          8           8
Emps         32         16          8           8
Nums         1048577    13576       13488       56
ORG          10         16          8           8
PROJECT      20         16          8           8
sales        57         16          8           8
sales02      32         40          24          16

Notice the disk for sales02: 40kb and the data: 24kb

Drop the vartext column & regain the space

  1. ALTER TABLE sales02
  2.    DROP COLUMN vartext;
  3. go
  4.  
  5. DBCC CLEANTABLE ('sql911','sales02',0);
  6. Go
  • 0 means that DBCC will process the entire table as one transaction.
  • DBCC CLEANTABLE places an exclusive lock on the table.
  • DBCC CLEANTABLE is NOT supported for system tables and temp tables.
  • DBCC CLEANTABLE is NOT be used as a routine maintenance task.

Verification

  1. UPDATE STATISTICS Sales02;
  2. go
  3.  
  4. SELECT  CAST(OBJECT_NAME(id) AS VARCHAR(12)) AS 'Table',
  5.         CAST(rowcnt AS VARCHAR(10)) AS '#Rows',
  6.         reserved * 8 AS 'Disk - Kb',
  7.         dpages * 8 AS 'Data - Kb',
  8.         (SUM(used) * 8) - (dpages * 8) AS 'Index - Kb'
  9. FROM sysindexes
  10. WHERE indid IN (0,1)
  11. AND   OBJECTPROPERTY(id, 'IsUserTable') = 1
  12. GROUP BY id, rowcnt, reserved, dpages
  13. ORDER BY 'Table';
  14. go
Column Name          column_id   Schema     Type         is_user_defined is_assembly_type max_length precision scale
-------------------- ----------- ---------- ------------ --------------- ---------------- ---------- --------- -----
sales_id             1           sys        int          0               0                4          10        0
sales_date           2           sys        datetime     0               0                8          23        3
sales_qty            3           sys        decimal      0               0                5          9         2
sales_price          4           sys        decimal      0               0                9          12        2
emp_short            5           sys        varchar      0               0                50         0         0
vartext              6           sys        varchar      0               0                500        0         0

(6 row(s) affected)
  • The UPDATE STATISTICS is to ensure that the sys.indexes table is correctly updated right now. That would depend on the SQL Server load.
  1. SELECT  CAST(OBJECT_NAME(id) AS VARCHAR(12)) AS 'Table',
  2.         CAST(rowcnt AS VARCHAR(10)) AS '#Rows',
  3.         reserved * 8 AS 'Disk - Kb',
  4.         dpages * 8 AS 'Data - Kb',
  5.         (SUM(used) * 8) - (dpages * 8) AS 'Index - Kb'
  6. FROM sysindexes
  7. WHERE indid IN (0,1)
  8. AND   OBJECTPROPERTY(id, 'IsUserTable') = 1
  9. GROUP BY id, rowcnt, reserved, dpages
  10. ORDER BY 'Table';
  11. go
Table        #Rows      Disk - Kb   Data - Kb   Index - Kb
------------ ---------- ----------- ----------- -----------
DEPARTMENT   9          16          8           8
EMP_WORK     75         16          8           8
Emps         32         16          8           8
Nums         1048577    13576       13488       56
ORG          10         16          8           8
PROJECT      20         16          8           8
sales        57         16          8           8
sales02      32         24          8           8
  1. Notice the disk for sales02: 24kb and the data: 8kb
  2. You can regain the disk space only if you drop one of the following:
    • varchar
    • nvarchar
    • varbinary
    • text
    • ntext
    • image
    • xml