Shrink Database (SQL Server)

Kailash Chandra Behera | Thursday, September 17, 2020

Introduction

Shrink database helps to reduce and compress database sql serverwhich leads reclaim spaces by recovering spaces. This blog, guides to shrink SQL server (MSSQL shrink database) to recovers space in SQL Server by using Microsoft SQL Server Management Studio 2017 and TSQL shrink database query.

Getting Started

SQL shrinking database moves pages of data from the end of the file to unoccupied space closer to the front of the file to recover spaces. When enough free space is created at the end of the file, data pages at the end of the file can be deallocated and returned to the file system.

Note that you cannot shrink a database while the database is being backed up. Conversely, you cannot back up a database while a shrink operation on the database is in process.

Demonstration

This demonstration is conducted in SQL Server Management Studio 2017. Here we will discuss how to shrink the SQL server in two different ways. Using SSMS options and SQL query.

SQL Shrink DB using SSMS (MSSQL shrink Database)

  1. Open SQL Server Management Studio and login to your SQL instance.

  2. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.

  3. Expand Databases, and then right-click the database that you want to shrink.

  4. Point to Tasks, point to Shrink, and then click Database.

  5. Click OK.
Parameter Description

  1. Database
    Displays the name of the selected database.

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

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

  4. 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 the 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.

  5. 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.

SQL Shrink DB using the T-SQL query

The following SQL query helps to shrink the database. This example uses DBCC SHRINKDATABASE to decreases the size of the data and log files in the database and to allow for 10 percent free space in the database. 

 DBCC SHRINKDATABASE (database_name, 10);  
 GO

Tsql shrink database

The database cannot be made smaller than the minimum size of the database. The minimum size is the size specified when the database was originally created, or the last explicit size set by using a file-size-changing operation, such as DBCC SHRINKFILE.

For example, if a database was originally created with a size of 10 MB and grew to 100 MB, the smallest size the database could be reduced to is 10 MB, even if all the data in the database has been deleted.

Data that is moved to shrink a file can be scattered to any available location in the file. This causes index fragmentation and can slow the performance of queries that search a range of the index. To eliminate the fragmentation, consider rebuilding the indexes on the file after a shrink database.

Related Articles

  1. Create User In SQL Server Management Studio
  2. Recover SQL Databases from Suspect Mode in SQL Server
  3. SQL Sever Firewall Ports
  4. SQL Server Shrink Log File
  5. SQL Server Backup with Password
  6. Create A Database in SQL Server

Summary

In the above, we learn how to string SQL server database using two different ways using SSMS and T-SQL query. I hope you have enjoyed it a lot.

Thanks

By at