Anticlue

Compact a SQL Server

Recently, someone mildly familiar with Access, had a problem with an SQL server db. The question came to me, where is the compact and repair utility in SQL? Just one of the educational opportunities that has arisen lately.

In case the questions comes to you, there are a couple of SQL statements one needs to be familiar with.

EXEC sp_dboption 'dbName', 'single user', 'TRUE'

Set the database in single user mode.

DBCC ShrinkDatabase ( DBName, TargetPercentFreeSpace, TRUNCATEONLY)

This command will shrink the database size to the size + percentage of free space. The truncateonly attribute releases the shrunken space to the operating system.

DBCC CheckDB(dbNAME, REPAIR_REBUILD )

checks the integrity of the db, and repairs some issues without data loss. This will rebuild your indexes.

EXEC sp_dboption 'dbName', 'single user', 'False'

Sets the db back to full access. As always it is good to have a backup in place before doing any database maintenance.