CODEWITHSUNDEEP
sql-server
Rhendar
Rhendar

Reputation: 450

Shrink database after data clean up

I have a database table that is being used to store Xml. A lot of the records have a base64 pdf file within the Xml which is taking up a lot of space. If I clear the Xml column out the size of the database doesn't shrink. I've even tried completely deleting every record on our test database and the physical size of the database is still sitting at 300 GB. How do I get SQL to realize that the space is no longer being used and it should re-size?

I've tried DBCC Shrinkfile but it just makes the log file grow by 1GB.

Upvotes: 1

Views: 4064

Answers (1)

Chris Catignani
Chris Catignani

Reputation: 5306

Microsoft Documentation

Exert

To shrink a database
Using SQL Server Management Studio

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
  2. Expand Databases, and then right-click the database that you want to shrink.
  3. Point to Tasks, point to Shrink, and then click Database.
    Database
     Displays the name of the selected database.

    Current allocated space
    Displays the total used and unused space for the selected database.

    Available free space Displays the sum of free space in the log and data files of the selected database.

    Reorganize files before releasing unused space
    Selecting this option is equivalent to executing DBCC SHRINKDATABASE specifying a target percent option. Clearing this option is equivalent to executing DBCC SHRINKDATABASE with TRUNCATEONLY option. By default, this option is not selected when the dialog is opened. If this option is selected, the user must specify a target percent option.

    Maximum free space in files after shrinking
    Enter the maximum percentage of free space to be left in the database files after the database has been shrunk. Permissible values are between 0 and 99.

  4. Click OK.

Upvotes: 1

Related Questions