Should we Shrink database – Sql Server

Lets try to understand with an example.

First we will create database and add some tables in that database with some sample data and then we will major fragmentation level as well size of database & log. After that we will compress database files and then we will major fragmentation level and then at last after rebuild and reorganize on indexes will major fragmentation level & database size.

So Run below script to create database, note blank database size:

USE [master];
GO
 
IF DATABASEPROPERTYEX (N'DBMaintanace', N'Version') IS NOT NULL
    DROP DATABASE [DBMaintanace];
GO
 
CREATE DATABASE DBMaintanace;
GO
USE [DBMaintanace];
GO
 
SET NOCOUNT ON;
GO

SELECT size AS 'blank db size',name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

Output:

 

After that run below script which will create tables and generate index on single table and try to analyse size of database :

-- Create around 10MB Student table at the 'front' of the data file
CREATE TABLE [Student] (
    [c1] INT IDENTITY,
    [c2] CHAR (8000) DEFAULT 'Student');
GO

-- Fill up the Student table
INSERT INTO [Student] DEFAULT VALUES;
GO 12800

-- Create the Exam table, which will be 'after' the Student table in the data file
CREATE TABLE [Exam] (
    [c1] INT IDENTITY,
    [c2] CHAR (8000) DEFAULT 'Exam');
CREATE CLUSTERED INDEX [exam_cl] ON [Exam] ([c1]);
GO

INSERT INTO [Exam] DEFAULT VALUES;
GO 12800

SELECT size AS 'After Insertion db size',name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

-- Check the fragmentation of the Exam table
SELECT
    [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
    DB_ID (N'DBMaintanace'), OBJECT_ID (N'Exam'), 1, NULL, 'LIMITED');
GO

Output:

 

Now please note that I will delete table “Student” and shrink database files and for each operations we will note database files size and fragmentation level of created index. You could find it will be grew by almost 100%.

DROP TABLE [Student];
GO

DECLARE @DBName VARCHAR(200)=DB_NAME()
DBCC SHRINKFILE(@DBName);  
GO 

SELECT size AS 'After shrink db size',name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

DECLARE @DBLogFile VARCHAR(200)
SELECT  @DBLogFile=[name]
FROM [sys].[database_files] 
where [type] = 1

DBCC SHRINKFILE(@DBLogFile);  
GO

SELECT size AS 'After shrink Log file size',name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
GO

-- Check the fragmentation of the Exam table
SELECT
    [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
    DB_ID (N'DBMaintanace'), OBJECT_ID (N'Exam'), 1, NULL, 'LIMITED');
GO

Output:

 

To overcome from slowness issue we need to rebuild/reorganize index by using below script:

--************************************************--
-- Rebuild and Reorganize on indexes,which are required.
-- https://www.technothirsty.com/rebuild-and-reorganize-indexing-sql-server-scripts/
-- Which will tune your fragmentaion level 
--************************************************--

-- Make Sure you have write USE <databasename> statement before executing statement.  
-- USE <databasename>
SET NOCOUNT ON;  
DECLARE @objectid int;  
DECLARE @indexid int;  
DECLARE @partitioncount bigint;  
DECLARE @schemaname nvarchar(500);   
DECLARE @objectname nvarchar(500);   
DECLARE @indexname nvarchar(500);   
DECLARE @partitionnum bigint;  
DECLARE @partitions bigint;  
DECLARE @frag float;  
DECLARE @command nvarchar(4000);   
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function   
-- and convert object and index IDs to names.  
SELECT  
    object_id AS objectid,  
    index_id AS indexid,  
    partition_number AS partitionnum,  
    avg_fragmentation_in_percent AS frag  
INTO #work_to_do  
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')  
WHERE index_id > 0;  

-- Declare the cursor for the list of partitions to be processed.  
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;  

-- Open the cursor.  
OPEN partitions;  

-- Loop through the partitions.  
WHILE (1=1)  
    BEGIN;  
        FETCH NEXT  
           FROM partitions  
           INTO @objectid, @indexid, @partitionnum, @frag;  
        IF @@FETCH_STATUS < 0 BREAK;  
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)  
        FROM sys.objects AS o  
        JOIN sys.schemas as s ON s.schema_id = o.schema_id  
        WHERE o.object_id = @objectid;  
        SELECT @indexname = QUOTENAME(name)  
        FROM sys.indexes  
        WHERE  object_id = @objectid AND index_id = @indexid;  
        SELECT @partitioncount = count (*)  
        FROM sys.partitions  
        WHERE object_id = @objectid AND index_id = @indexid;  

		-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.  
        IF @frag < 30.0  OR @frag >= 5.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';  
        IF @frag >= 30.0 --OR @frag < 5.0 
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';  
        IF @partitioncount > 1  
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));  
        EXEC (@command);  
        PRINT N'Executed: ' + @command;  
    END;  

-- Close and deallocate the cursor.  
CLOSE partitions;  
DEALLOCATE partitions;  

-- Drop the temporary table.  
DROP TABLE #work_to_do;  
GO  

 
SELECT size AS 'After rebuild size',name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
GO

SELECT
    [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
    DB_ID (N'DBMaintanace'), OBJECT_ID (N'Exam'), 1, NULL, 'LIMITED');
GO

Output:

So now you can notice that After Rebuild size has been increased to 26336 from 25888 of DBMaintanace and 224 from 99 of Log file.

So from this article you could understand that we should not do shrink database frequently or on regular basis.

But I would recommend if your log file grows exponentially high and you think you could shrink that file to get space, you could shrink log file only(recommended).