SQL Server Database Shrink Maintenance

Database shrink ka matlab hai database file (ya log file) ke size ko chhota karna by removing unused space . Matlab, agar tumhare DB me data delete ho gaya h...

Mar 25, 2026 17 min read

Database shrink ka matlab hai database file (ya log file) ke size ko chhota karna by removing unused space.
Matlab, agar tumhare DB me data delete ho gaya hai ya free space jyada pada hai, to shrink uss extra space ko release kar deta hai.

Shrink Kyu Use Karte Hain?

Free space release karna – Disk me jagah khali karni ho.
Bada data delete kiya ho – Jaise purana data archive karke delete kar diya.
One-time maintenance – Jaise backup ke liye size chhota karna.

Shrink Kab Avoid Karna Chahiye?

Har baar delete ke baad shrink mat karo ❌ (yeh galti sab karte hain).
Kyunki shrink karne se indexes fragment ho jaate hain aur performance slow ho sakti hai.
Agar DB waise bhi future me grow hone wala hai, to shrink karne ka koi fayda nahi.

Shrink Commands

Shrink Entire Database

DBCC SHRINKDATABASE (YourDatabaseName, target_percent);
DBCC SHRINKDATABASE (AdventureWorks, 10);

target_percent = kitna free space chhodna hai (ex: 10 matlab 10% free space bachegi).

Shrink Specific File (Data ya Log File)

DBCC SHRINKFILE (FileName, target_size_in_MB);
DBCC SHRINKFILE (AdventureWorks_Data, 500); -- file ko 500MB tak shrink karo
--SQL Server try karega file ko 50 MB tak shrink karne ka
--File shrink hoke approx 50 MB ho jayegi (agar possible hua)

Kaise Pata Kare File Info?

sp_helpfile;

Ya phir

SELECT name, size*8/1024 AS SizeMB, physical_name 
FROM sys.master_files
WHERE database_id = DB_ID('YourDatabaseName');

Best Practices

Shrink sirf rarely karo (one-time operation).
Har shrink ke baad index rebuild kar lo for performance.
Log file me shrink se pehle log backup lena na bhoolo.
Agar DB regularly grow-shrink kar raha hai → space planning galat hai, shrink solution nahi hai.

Step by Step Shrink Example

1. Ek dummy database banao

CREATE DATABASE ShrinkDemo;
GO
USE ShrinkDemo;
GO

2. Ek badi table banao aur data insert karo

CREATE TABLE BigTable (
    ID INT IDENTITY(1,1),
    SomeText CHAR(8000) DEFAULT 'ShrinkDemo Test Data'
);

-- 50,000 rows insert karte hain
INSERT INTO BigTable DEFAULT VALUES;
GO 50000

Ab tumhara database ka size kaafi bada ho gaya hoga.

3. Current size check karo

EXEC sp_helpfile;

Ya phir:

SELECT name, size*8/1024 AS SizeMB, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('ShrinkDemo');

4. Ab data delete karo

DELETE FROM BigTable;

Ab table khali hai, lekin database size abhi bhi bada dikh raha hoga (kyunki SQL Server free space ko turant release nahi karta).

5. Shrink Database

DBCC SHRINKDATABASE (ShrinkDemo, 10);

Ye command database file ko chhota kar dega, sirf 10% free space chod ke.

6. Shrink Specific File (agar chhoti karni ho)

DBCC SHRINKFILE (ShrinkDemo, 50);

7. Size dobara check karo

EXEC sp_helpfile;

⚠️ Note: Shrink ke baad index fragmentation ho jaata hai, to acche se performance ke liye:

ALTER INDEX ALL ON BigTable REBUILD;

Step by Step: Log File Shrink

1. Pehle database ka log size check karo

USE ShrinkDemo;
GO
EXEC sp_helpfile;

👉 Yaha tumhe 2 file dikhengi:

ek .mdf (Data file)
ek .ldf (Log file)

2. Thoda dummy transaction banao taaki log file badi ho jaye

BEGIN TRAN;
INSERT INTO BigTable DEFAULT VALUES;
GO 10000
COMMIT;

Ye operations log file ko bada kar denge.

3. Ab log file ka backup lo (important step)

FULL Recovery Model

Jab tak tum Log ka backup nahi lete, log file free space release nahi karti.
Isliye Shrink log file karne se pehle hamesha:

BACKUP LOG ShrinkDemo TO DISK = 'C:\ShrinkDemo_LogBackup.trn';

⚠️ Without log backup, log file shrink nahi hoga (agar DB FULL Recovery Mode me hai).

SIMPLE Recovery Model

log file apna space khud reuse kar leti hai.
Yaha log backup ki zarurat nahi.
Agar tum data delete karte ho, fir directly shrink kar sakte ho.

ALTER DATABASE ShrinkDemo SET RECOVERY SIMPLE;

Comparison Example

FULL mode = Tum diary likhte ho aur uska copy bhi bacha ke rakhna padta hai (backup ke bina diary khali nahi hogi).
SIMPLE mode = Tum whiteboard pe likhte ho, erase karte hi space free ho jaata hai.

4. Ab log file shrink karo

-- File ka naam pehle check kar lo with sp_helpfile
DBCC SHRINKFILE (ShrinkDemo_Log, 50);
--no backup needed.

5. Size dobara check karo

EXEC sp_helpfile;

Important Notes for Log File Shrink

Agar tumhara database SIMPLE recovery model me hai, to log backup ki need nahi hoti.
Lekin agar FULL recovery model me hai → pehle log backup lena compulsory hai.

Best Practice

Production me usually FULL recovery model use hota hai (data loss na ho).
SIMPLE recovery model mostly test ya dev environment me use karte hain.

Create SQL Server Database Shrink Maintenance Process

IF OBJECT_ID('dbo.ADTDB_ShrinkLogs', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.ADTDB_ShrinkLogs
    (
        LogID                 INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        RunID                 INT           NOT NULL,      -- groups all rows of one shrink execution
        DatabaseName          NVARCHAR(255) NOT NULL,
        LogTime               DATETIME      NOT NULL DEFAULT GETDATE(),
        EntryType             VARCHAR(10)   NOT NULL,      -- RUN_START / FILE / INDEX / MESSAGE / RUN_END
        LogType               TINYINT       NOT NULL DEFAULT 1,  -- 1 = info, 2 = warning/error
        Status                VARCHAR(20)   NULL,          -- Running / Success / Failed (RUN_START/RUN_END rows)
        RecoveryModel         NVARCHAR(60)  NULL,
        FileType              VARCHAR(10)   NULL,          -- Data / Log (FILE rows)
        FileName              NVARCHAR(255) NULL,
        BeforeSizeMB          INT           NULL,
        AfterSizeMB           INT           NULL,
        SchemaName            SYSNAME       NULL,          -- INDEX rows
        TableName             SYSNAME       NULL,
        IndexName             SYSNAME       NULL,
        FragmentationPercent  DECIMAL(5,2)  NULL,
        PageCount             INT           NULL,
        IndexAction           VARCHAR(10)   NULL,
        LogDesc               NVARCHAR(4000) NULL          -- free text for MESSAGE rows / errors
    );

    CREATE INDEX IX_ADTDB_ShrinkLogs_RunID ON dbo.ADTDB_ShrinkLogs (RunID);
    CREATE INDEX IX_ADTDB_ShrinkLogs_DatabaseName_LogTime ON dbo.ADTDB_ShrinkLogs (DatabaseName, LogTime DESC);
END
GO

IF OBJECT_ID('dbo.uspADTDB_ShrinkDB', 'P') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.uspADTDB_ShrinkDB;
END
GO

CREATE PROCEDURE dbo.uspADTDB_ShrinkDB
    @Mode                   INT           = 1,  -- 1 = Report only (current size/free space + estimated size after shrink, no changes made)
                                                 -- 2 = Perform the actual shrink (default, existing behavior)
    @LogBackupPath          NVARCHAR(500) = NULL,  -- optional folder for pre/post-shrink log backups
                                                   -- IF NULL, uses the folder the LDF file already lives in
    @LogShrinkTargetMB      INT           = 1024,  -- LDF shrink target in MB (default: 1024 = 1GB)
    @DataShrinkTargetMB     INT           = 0,  -- MDF/NDF shrink target in MB (default: 0 = minimum)
    @RebuildIndexes         BIT           = 1,  -- 1 = Rebuild/Reorganize indexes after shrink (default: 1)
    @UpdateStatsOnly        BIT           = 0  -- 1 = sp_updatestats instead of rebuild (default: 0)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @DB NVARCHAR(255);
    DECLARE @SQL NVARCHAR(MAX);
    DECLARE @LogFile NVARCHAR(255);
    DECLARE @DataFile NVARCHAR(255);
    DECLARE @OriginalRecoveryModel NVARCHAR(60);
    DECLARE @Before INT;
    DECLARE @After INT;
    DECLARE @RunID INT;
    DECLARE @Results TABLE (FileType VARCHAR(10), FileName NVARCHAR(255), BeforeMB INT, AfterMB INT);
    DECLARE @IndexResults TABLE (SchemaName SYSNAME, TableName SYSNAME, IndexName SYSNAME, FragmentationPercent DECIMAL(5,2), PageCount INT, Action VARCHAR(10));

    -- Backup-path resolution (no xp_instance_regread: needs elevated perms and
    -- doesn't exist on SQL Server on Linux; derive from the LDF's own folder instead)
    DECLARE @BackupFolder NVARCHAR(500);
    DECLARE @LogFilePath NVARCHAR(500);
    DECLARE @Timestamp NVARCHAR(20);
    DECLARE @PreShrinkLogBackup NVARCHAR(600);
    DECLARE @PostShrinkLogBackup NVARCHAR(600);

    SET @DB = DB_NAME();

    -- Refuse to run against system databases
    IF @DB IN ('master', 'model', 'msdb', 'tempdb')
    BEGIN
        RAISERROR('uspADTDB_ShrinkDB must not be run against system database [%s].', 16, 1, @DB);
        RETURN;
    END

    IF @Mode NOT IN (1, 2)
    BEGIN
        RAISERROR('@Mode must be 1 (report only) or 2 (perform shrink). Got: %d', 16, 1, @Mode);
        RETURN;
    END

    --------------------------------------------------------------------------------
    -- MODE 1: Report only - current size/free space per file, and the estimated
    -- size/free space if a shrink were run now with the given targets. No changes made.
    --------------------------------------------------------------------------------
    IF @Mode = 1
    BEGIN
        ;WITH FileInfo AS (
            SELECT
                mf.type                                            AS FileTypeCode,
                mf.name                                             AS FileName,
                mf.size / 128.0                                     AS CurrentSizeMB,
                FILEPROPERTY(mf.name, 'SpaceUsed') / 128.0          AS UsedSpaceMB,
                CASE WHEN mf.type = 1 THEN @LogShrinkTargetMB ELSE @DataShrinkTargetMB END AS ShrinkTargetMB
            FROM sys.database_files mf
            WHERE mf.type IN (0, 1)  -- 0 = Data (MDF/NDF), 1 = Log (LDF)
        )
        SELECT
            FileType, FileName, CurrentSizeMB, UsedSpaceMB, FreeSpaceMB, ShrinkTargetMB,
            EstimatedSizeAfterShrinkMB, EstimatedSpaceFreedMB
        FROM (
            SELECT
                CASE FileTypeCode WHEN 0 THEN 'Data' WHEN 1 THEN 'Log' END            AS FileType,
                FileName,
                CAST(CurrentSizeMB AS DECIMAL(12,2))                                   AS CurrentSizeMB,
                CAST(UsedSpaceMB AS DECIMAL(12,2))                                     AS UsedSpaceMB,
                CAST(CurrentSizeMB - UsedSpaceMB AS DECIMAL(12,2))                     AS FreeSpaceMB,
                ShrinkTargetMB,
                -- DBCC SHRINKFILE can't shrink a file below the space it's currently using,
                -- so the realistic floor is MAX(target, used space).
                CAST(CASE WHEN ShrinkTargetMB > UsedSpaceMB THEN ShrinkTargetMB ELSE UsedSpaceMB END AS DECIMAL(12,2)) AS EstimatedSizeAfterShrinkMB,
                CAST(CurrentSizeMB - CASE WHEN ShrinkTargetMB > UsedSpaceMB THEN ShrinkTargetMB ELSE UsedSpaceMB END AS DECIMAL(12,2)) AS EstimatedSpaceFreedMB,
                0 AS SortOrder
            FROM FileInfo

            UNION ALL

            SELECT
                'TOTAL', NULL,
                CAST(SUM(CurrentSizeMB) AS DECIMAL(12,2)),
                CAST(SUM(UsedSpaceMB) AS DECIMAL(12,2)),
                CAST(SUM(CurrentSizeMB - UsedSpaceMB) AS DECIMAL(12,2)),
                NULL,
                CAST(SUM(CASE WHEN ShrinkTargetMB > UsedSpaceMB THEN ShrinkTargetMB ELSE UsedSpaceMB END) AS DECIMAL(12,2)),
                CAST(SUM(CurrentSizeMB) - SUM(CASE WHEN ShrinkTargetMB > UsedSpaceMB THEN ShrinkTargetMB ELSE UsedSpaceMB END) AS DECIMAL(12,2)),
                1 AS SortOrder
            FROM FileInfo
        ) x
        ORDER BY SortOrder, FileType, FileName;

        RETURN;
    END

    --------------------------------------------------------------------------------
    -- MODE 2: Perform the actual shrink (existing behavior below)
    --------------------------------------------------------------------------------
    SELECT @OriginalRecoveryModel = recovery_model_desc
    FROM sys.databases
    WHERE name = @DB;

    -- RunID = this row's own LogID; every later row from this execution reuses it to group together.
    INSERT INTO dbo.ADTDB_ShrinkLogs (RunID, DatabaseName, EntryType, LogType, Status, RecoveryModel)
    VALUES (0, @DB, 'RUN_START', 1, 'Running', @OriginalRecoveryModel);
    SET @RunID = SCOPE_IDENTITY();
    UPDATE dbo.ADTDB_ShrinkLogs SET RunID = @RunID WHERE LogID = @RunID;

    SET @Timestamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 120), '-', ''), ' ', '_'), ':', '');

    IF @LogBackupPath IS NOT NULL AND LTRIM(RTRIM(@LogBackupPath)) <> ''
        SET @BackupFolder = LTRIM(RTRIM(@LogBackupPath));
    ELSE
    BEGIN
        SELECT @LogFilePath = physical_name FROM sys.master_files WHERE database_id = DB_ID(@DB) AND type = 1;
        SET @BackupFolder = LEFT(@LogFilePath, LEN(@LogFilePath) - CHARINDEX('\', REVERSE(@LogFilePath)) + 1);
    END

    IF RIGHT(@BackupFolder, 1) = '\'
        SET @BackupFolder = LEFT(@BackupFolder, LEN(@BackupFolder) - 1);

    -- Separate Pre/Post filenames so the pre-shrink safety backup is never
    -- overwritten by the post-shrink one, even when the same folder is reused.
    SET @PreShrinkLogBackup  = @BackupFolder + '\' + @DB + '_LogBackup_PreShrink_'  + @Timestamp + '.trn';
    SET @PostShrinkLogBackup = @BackupFolder + '\' + @DB + '_LogBackup_PostShrink_' + @Timestamp + '.trn';

    BEGIN TRY
        --------------------------------------------------------------------------------
        -- STEP 1: Log backup BEFORE switching to SIMPLE (preserves the backup chain)
        -- Only applies to FULL/BULK_LOGGED - SIMPLE recovery has no log chain to keep.
        --------------------------------------------------------------------------------
        IF @OriginalRecoveryModel IN ('FULL', 'BULK_LOGGED')
        BEGIN
            -- A log backup needs at least one prior full backup to attach to;
            -- without one, BACKUP LOG fails with Msg 4214. Don't let that abort the shrink.
            IF EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DB AND type = 'D')
            BEGIN
                BEGIN TRY
                    SET @SQL = 'BACKUP LOG ' + QUOTENAME(@DB) + ' TO DISK = ''' + REPLACE(@PreShrinkLogBackup, '''', '''''') + ''' WITH INIT, STATS = 10';
                    EXEC(@SQL);

                    INSERT INTO dbo.ADTDB_ShrinkLogs (RunID, DatabaseName, EntryType, LogType, LogDesc)
                    SELECT @RunID, @DB, 'MESSAGE', 1, 'Pre-shrink log backup taken: ' + @PreShrinkLogBackup;
                END TRY
                BEGIN CATCH
                    INSERT INTO dbo.ADTDB_ShrinkLogs (RunID, DatabaseName, EntryType, LogType, LogDesc)
                    SELECT @RunID, @DB, 'MESSAGE', 2, 'WARNING: pre-shrink log backup failed, continuing with shrink anyway | ' + ERROR_MESSAGE();
                END CATCH
            END
            ELSE
            BEGIN
                INSERT INTO dbo.ADTDB_ShrinkLogs (RunID, DatabaseName, EntryType, LogType, LogDesc)
                SELECT @RunID, @DB, 'MESSAGE', 2, 'WARNING: no full backup exists yet for ' + @DB + ' - pre-shrink log backup skipped. Take a full backup to start the backup chain.';
            END
        END
        ELSE
        BEGIN
            INSERT INTO dbo.ADTDB_ShrinkLogs (RunID, DatabaseName, EntryType, LogType, LogDesc)
            SELECT @RunID, @DB, 'MESSAGE', 1, 'Pre-shrink log backup skipped - recovery model is ' + @OriginalRecoveryModel;
        END

        --------------------------------------------------------------------------------
        -- STEP 2: Set SIMPLE Recovery for log shrink
        --------------------------------------------------------------------------------
        SET @SQL = 'ALTER DATABASE ' + QUOTENAME(@DB) + ' SET RECOVERY SIMPLE';
        EXEC(@SQL);

        --------------------------------------------------------------------------------
        -- STEP 3: CHECKPOINT - flush dirty pages so the shrink is more effective
        --------------------------------------------------------------------------------
        CHECKPOINT;

        --------------------------------------------------------------------------------
        -- STEP 4: Shrink every LOG file
        --------------------------------------------------------------------------------
        DECLARE @LogFiles TABLE (ID INT IDENTITY, Name NVARCHAR(255));
        INSERT INTO @LogFiles (Name)
        SELECT name
        FROM sys.master_files
        WHERE database_id = DB_ID(@DB) AND type = 1;  -- LDF

        DECLARE @LogTotal INT = (SELECT COUNT(*) FROM @LogFiles);
        DECLARE @LogI INT = 1;
        WHILE @LogI <= @LogTotal
        BEGIN
            SELECT @LogFile = Name FROM @LogFiles WHERE ID = @LogI;

            SELECT @Before = size/128 FROM sys.database_files WHERE name = @LogFile;
            EXEC sp_executesql N'DBCC SHRINKFILE (@fn, @sz) WITH NO_INFOMSGS',
                                N'@fn SYSNAME, @sz INT',
                                @fn = @LogFile, @sz = @LogShrinkTargetMB
            WITH RESULT SETS NONE;
            SELECT @After = size/128 FROM sys.database_files WHERE name = @LogFile;

            INSERT INTO dbo.ADTDB_ShrinkLogs (RunID, DatabaseName, EntryType, FileType, FileName, BeforeSizeMB, AfterSizeMB)
            VALUES (@RunID, @DB, 'FILE', 'Log', @LogFile, @Before, @After);

            INSERT INTO @Results (FileType, FileName, BeforeMB, AfterMB)
            VALUES ('Log', @LogFile, @Before, @After);

            SET @LogI = @LogI + 1;
        END;

        --------------------------------------------------------------------------------
        -- STEP 5: Shrink every DATA file
        --------------------------------------------------------------------------------
        DECLARE @MDFFiles TABLE (ID INT IDENTITY, Name NVARCHAR(255));
        INSERT INTO @MDFFiles (Name)
        SELECT name
        FROM sys.master_files
        WHERE database_id = DB_ID(@DB) AND type = 0;  -- MDF

        DECLARE @Total INT = (SELECT COUNT(*) FROM @MDFFiles);
        DECLARE @I INT = 1;
        WHILE @I <= @Total
        BEGIN
            SELECT @DataFile = Name FROM @MDFFiles WHERE ID = @I;

            SELECT @Before = size/128 FROM sys.database_files WHERE name = @DataFile;
            EXEC sp_executesql N'DBCC SHRINKFILE (@fn, @sz) WITH NO_INFOMSGS',
                                N'@fn SYSNAME, @sz INT',
                                @fn = @DataFile, @sz = @DataShrinkTargetMB
            WITH RESULT SETS NONE;
            SELECT @After = size/128 FROM sys.database_files WHERE name = @DataFile;

            INSERT INTO dbo.ADTDB_ShrinkLogs (RunID, DatabaseName, EntryType, FileType, FileName, BeforeSizeMB, AfterSizeMB)
            VALUES (@RunID, @DB, 'FILE', 'Data', @DataFile, @Before, @After);

            INSERT INTO @Results (FileType, FileName, BeforeMB, AfterMB)
            VALUES ('Data', @DataFile, @Before, @After);

            SET @I = @I + 1;
        END;

        --------------------------------------------------------------------------------
        -- STEP 6: Rebuild/Reorganize fragmented indexes, or just update stats
        -- Shrinking data files heavily fragments indexes; done here (still SIMPLE
        -- recovery) so the rebuild is minimally logged instead of fully logged under FULL.
        --------------------------------------------------------------------------------
        IF @RebuildIndexes = 1
        BEGIN
            DECLARE @IndexWork TABLE (ID INT IDENTITY, SchemaName SYSNAME, TableName SYSNAME, IndexName SYSNAME, FragPercent FLOAT, PageCount INT);

            INSERT INTO @IndexWork (SchemaName, TableName, IndexName, FragPercent, PageCount)
            SELECT
                s.name,
                t.name,
                i.name,
                ips.avg_fragmentation_in_percent,
                ips.page_count
            FROM sys.dm_db_index_physical_stats(DB_ID(@DB), NULL, NULL, NULL, 'LIMITED') ips
            JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
            JOIN sys.tables t ON i.object_id = t.object_id
            JOIN sys.schemas s ON t.schema_id = s.schema_id
            WHERE ips.index_id > 0          -- ignore heaps
              AND ips.page_count >= 1000    -- ignore small indexes (MS guidance)
              AND i.is_disabled = 0
              AND i.is_hypothetical = 0
              AND ips.avg_fragmentation_in_percent >= 5.0;

            DECLARE @IdxTotal INT = (SELECT COUNT(*) FROM @IndexWork);
            DECLARE @IdxI INT = 1;
            DECLARE @SchemaName SYSNAME, @TableName SYSNAME, @IndexName SYSNAME, @FragPercent FLOAT, @PageCount INT, @Action VARCHAR(10);

            WHILE @IdxI <= @IdxTotal
            BEGIN
                SELECT @SchemaName = SchemaName, @TableName = TableName, @IndexName = IndexName,
                       @FragPercent = FragPercent, @PageCount = PageCount
                FROM @IndexWork WHERE ID = @IdxI;

                SET @Action = CASE WHEN @FragPercent >= 30.0 THEN 'REBUILD' ELSE 'REORGANIZE' END;

                SET @SQL = 'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ' + @Action;
                EXEC(@SQL);

                INSERT INTO dbo.ADTDB_ShrinkLogs (RunID, DatabaseName, EntryType, SchemaName, TableName, IndexName, FragmentationPercent, PageCount, IndexAction)
                VALUES (@RunID, @DB, 'INDEX', @SchemaName, @TableName, @IndexName, @FragPercent, @PageCount, @Action);

                INSERT INTO @IndexResults (SchemaName, TableName, IndexName, FragmentationPercent, PageCount, Action)
                VALUES (@SchemaName, @TableName, @IndexName, @FragPercent, @PageCount, @Action);

                SET @IdxI = @IdxI + 1;
            END;
        END
        ELSE IF @UpdateStatsOnly = 1
        BEGIN
            EXEC sys.sp_updatestats;

            INSERT INTO dbo.ADTDB_ShrinkLogs (RunID, DatabaseName, EntryType, LogType, LogDesc)
            SELECT @RunID, @DB, 'MESSAGE', 1, 'sp_updatestats completed | DB: ' + @DB;
        END

        --------------------------------------------------------------------------------
        -- STEP 7: Restore original Recovery Model
        --------------------------------------------------------------------------------
        SET @SQL = 'ALTER DATABASE ' + QUOTENAME(@DB) + ' SET RECOVERY ' + @OriginalRecoveryModel;
        EXEC(@SQL);

        --------------------------------------------------------------------------------
        -- STEP 8: Log backup AFTER restoring FULL/BULK_LOGGED (restarts the log chain)
        --------------------------------------------------------------------------------
        IF @OriginalRecoveryModel IN ('FULL', 'BULK_LOGGED')
        BEGIN
            IF EXISTS (SELECT 1 FROM msdb.dbo.backupset WHERE database_name = @DB AND type = 'D')
            BEGIN
                BEGIN TRY
                    SET @SQL = 'BACKUP LOG ' + QUOTENAME(@DB) + ' TO DISK = ''' + REPLACE(@PostShrinkLogBackup, '''', '''''') + ''' WITH INIT, STATS = 10';
                    EXEC(@SQL);

                    INSERT INTO dbo.ADTDB_ShrinkLogs (RunID, DatabaseName, EntryType, LogType, LogDesc)
                    SELECT @RunID, @DB, 'MESSAGE', 1, 'Post-shrink log backup taken (log chain restarted): ' + @PostShrinkLogBackup;
                END TRY
                BEGIN CATCH
                    INSERT INTO dbo.ADTDB_ShrinkLogs (RunID, DatabaseName, EntryType, LogType, LogDesc)
                    SELECT @RunID, @DB, 'MESSAGE', 2, 'WARNING: post-shrink log backup failed | ' + ERROR_MESSAGE();
                END CATCH
            END
            ELSE
            BEGIN
                INSERT INTO dbo.ADTDB_ShrinkLogs (RunID, DatabaseName, EntryType, LogType, LogDesc)
                SELECT @RunID, @DB, 'MESSAGE', 2, 'WARNING: no full backup exists yet for ' + @DB + ' - post-shrink log backup skipped. Take a full backup to start the backup chain.';
            END
        END

        INSERT INTO dbo.ADTDB_ShrinkLogs (RunID, DatabaseName, EntryType, LogType, Status)
        VALUES (@RunID, @DB, 'RUN_END', 1, 'Success');
    END TRY
    BEGIN CATCH
        DECLARE @ErrMsg NVARCHAR(4000) = ERROR_MESSAGE();

        -- Always try to put the recovery model back, even after a failure,
        -- so the DB is never left stuck in SIMPLE and the backup chain isn't silently broken.
        IF @OriginalRecoveryModel IS NOT NULL
        BEGIN
            BEGIN TRY
                SET @SQL = 'ALTER DATABASE ' + QUOTENAME(@DB) + ' SET RECOVERY ' + @OriginalRecoveryModel;
                EXEC(@SQL);
            END TRY
            BEGIN CATCH
                SET @ErrMsg = @ErrMsg + ' | CRITICAL: failed to restore recovery model, fix manually: ALTER DATABASE ' + QUOTENAME(@DB) + ' SET RECOVERY ' + @OriginalRecoveryModel + ' | ' + ERROR_MESSAGE();
            END CATCH
        END

        INSERT INTO dbo.ADTDB_ShrinkLogs (RunID, DatabaseName, EntryType, LogType, LogDesc)
        SELECT
            @RunID,
            @DB,
            'MESSAGE',
            2,      -- LogType 2 = error/critical, so failures are easy to filter from normal runs
            @ErrMsg;

        INSERT INTO dbo.ADTDB_ShrinkLogs (RunID, DatabaseName, EntryType, LogType, Status, LogDesc)
        VALUES (@RunID, @DB, 'RUN_END', 2, 'Failed', @ErrMsg);

        SELECT * FROM @Results;        -- files shrunk before the failure

        THROW;  -- re-raise so the caller/job sees the failure instead of silently succeeding
    END CATCH

    --------------------------------------------------------------------------------
    -- Final output: before/after size (MB) per file
    --------------------------------------------------------------------------------
    SELECT * FROM @Results;
END
GO

1. Ye SP kya karta hai? (High Level Flow)

Ye stored procedure step-by-step ye kaam karta hai:

Recovery Model check karta hai
Log backup leta hai (agar FULL mode me hai)
Database ko SIMPLE mode me switch karta hai
CHECKPOINT run karta hai
Log file (LDF) shrink karta hai
Data files (MDF/NDF) shrink karta hai
ShrinkDatabase run karta hai
Recovery model wapas restore karta hai
Index rebuild / stats update karta hai
Final summary log karta hai

👉 Simple words me:
"Database ko safely shrink karna without breaking backup chain"

2. Important Terminology (Must Understand)

Recovery Model kya hota hai?

Recovery model decide karta hai ki data loss ka level aur backup strategy kya hogi

Types:

FULL
- Full logging hoti hai
- Point-in-time restore possible
- Production ke liye best
SIMPLE
- Log truncate ho jata hai automatically
- No point-in-time recovery
BULK_LOGGED
- Bulk operations ke liye optimized

👉 Is SP me:

Pehle FULL → SIMPLE
Baad me SIMPLE → wapas FULL

CHECKPOINT kya hota hai?

👉 Simple definition:

Memory (RAM) ke dirty pages ko disk par write karna

SQL Server data pehle memory me update karta hai
CHECKPOINT ensures ki wo disk me persist ho jaye

👉 Shrink se pehle zaroori hai
warna shrink effective nahi hoga

3. Step-by-Step Deep Explanation

STEP 0 – Initialization

DB name, recovery model fetch
Default backup path registry se read
Logging start

👉 Smart design 👍

STEP 1 – Log Backup (IMPORTANT)

BACKUP LOG

👉 Kyun?

FULL mode me log truncate nahi hota
Backup lene se log reusable ho jata hai

⚠️ Agar skip kiya → shrink useless

STEP 2 – Set Recovery SIMPLE

ALTER DATABASE SET RECOVERY SIMPLE

👉 Benefit:

Log auto truncate ho jayega

STEP 3 – CHECKPOINT

CHECKPOINT

👉 Memory → Disk flush
👉 Shrink effective banata hai

STEP 4 – Shrink LOG file

DBCC SHRINKFILE (LDF)

👉 Target size:

@LogShrinkTargetMB = 1024

STEP 5 – Shrink Data files

Loop me:

DBCC SHRINKFILE (MDF/NDF)

👉 Har file individually shrink hoti hai

STEP 6 – SHRINKDATABASE

DBCC SHRINKDATABASE

👉 Remaining free space clean

STEP 7 – Restore Recovery Model

ALTER DATABASE SET RECOVERY FULL

⚠️ VERY IMPORTANT
Agar yeh nahi kiya → backup chain break

STEP 8 – Log Backup Again

👉 New log chain start hoti hai

STEP 9 – Index Maintenance

Logic:

Fragmentation > 30% → REBUILD
10–30% → REORGANIZE

👉 Performance restore hoti hai

STEP 10 – Final Logging

Final DB size log karta hai

4. Best Practices

✔ Shrink only when:

Log file abnormal grow ho gaya ho
One-time cleanup

✔ Always:

Backup lo
Index rebuild karo
Monitoring karo

0 likes

Rate this post

No rating

Tap a star to rate

0 comments

Latest comments

0 comments

No comments yet.