DBCC SHRINKDATABASE with MSSQL

  • DBCC SHRINKDATABASE shrinks the size of both the data and log files of a database.
  • DBCC SHRINKDATABASE moves the data in data files from the end of a file to the unallocated pages at the front of the file.
  • If you use DBCC SHRINKDATABASE with the NOTRUNCATE, you will move the data at the front of the file but you will not shrink the physical file.
  • DBCC SHRINKDATABASE does not care about any index ordering or fragmentation.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008
  • If you run:
    1. DBCC INDEXDEFRAG with Microsoft SQL Server 2000 or ALTER INDEX ... REORGANIZE with Microsoft SQL Server 2005 to removes logical fragmentation.
    2. DBCC SHRINKDATABASE.
  • You will have:
    1. Defragmented the indexes.
    2. Refragmented the data and the indexes.

Not exactly what you were expecting.

The only time you should run: DBCC SHRINKDATABASE is when you have deleted large amounts of data and you will not reuse the space with inserts into the database.

DBCC SHRINKDATABASE

  1. ALTER DATABASE sql911
  2. SET single_user WITH ROLLBACK IMMEDIATE;
  3. go
  4. DBCC shrinkdatabase (sql911, truncateonly);
  5. go
DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
10     1           1888        256         1880        1880

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  • dbcc shrinkdatabase shrunk the data file by 8 pages * 8k/page = 64k