Back to all posts
SQL

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 hai…

Author

SQLDataDev Editorial Team

Mar 25, 2026 21 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?

  1. Free space release karna – Disk me jagah khali karni ho.

  2. Bada data delete kiya ho – Jaise purana data archive karke delete kar diya.

  3. 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 is like kapde bar-bar sukha ke dobara paani me daal dena – space to mil jaata hai par folding (fragmentation) bigad jaati hai.

🛠 Shrink Commands

  1. Shrink Entire Database

SQL 
DBCC SHRINKDATABASE (YourDatabaseName, target_percent);

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

Example:

SQL 
DBCC SHRINKDATABASE (AdventureWorks, 10);

  1. Shrink Specific File (Data ya Log File)

SQL 
DBCC SHRINKFILE (FileName, target_size_in_MB);

Example:

SQL 
DBCC SHRINKFILE (AdventureWorks_Data, 500); -- file ko 500MB tak shrink karo

  1. Log File Shrink
    Aksar log file badi ho jaati hai, uske liye:

SQL 
DBCC SHRINKFILE (AdventureWorks_Log, 200);

⚠️ Lekin shrink karne se pehle log backup lena zaroori hai (warna shrink nahi hoga).

🔍 Kaise Pata Kare File Info?

SQL 
sp_helpfile;

Ya phir

SQL 
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

SQL 
CREATE DATABASE ShrinkDemo;
GO
USE ShrinkDemo;
GO

2. Ek badi table banao aur data insert karo

SQL 
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

SQL 
EXEC sp_helpfile;

Ya phir:

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

4. Ab data delete karo

SQL 
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

SQL 
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)

SQL 
DBCC SHRINKFILE (ShrinkDemo, 50);  -- 50 MB tak shrink karo

7. Size dobara check karo

SQL 
EXEC sp_helpfile;

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

SQL 
ALTER INDEX ALL ON BigTable REBUILD;

Perfect chalo ab Log File Shrink step by step dekhte hain, kyunki log file hamesha thoda extra headache deta hai

Step by Step: Log File Shrink

1. Pehle database ka log size check karo

SQL 
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

SQL 
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:

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

SQL 
ALTER DATABASE ShrinkDemo SET RECOVERY SIMPLE;
DBCC SHRINKFILE (ShrinkDemo_Log, 50);
--Matlab SIMPLE model me shrink = direct ho jaata hai, no backup needed.

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

SQL 
-- File ka naam pehle check kar lo with sp_helpfile
DBCC SHRINKFILE (ShrinkDemo_Log, 50);  -- 50 MB tak shrink

5. Size dobara check karo

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

image
SQL 

-- --------------------------------------------------------------------------------
--  PARAMETERS:
--    @LogBackupPath   : Path for log backup file (optional)
--                       If NULL and Recovery = FULL  SQL Server default path used
--    @DataBackupPath  : Path for full DB backup file (optional)
--                       If NULL and Recovery = FULL  SQL Server default path used
--    @LogShrinkTargetMB  : LDF shrink target in MB         (default: 1024 = 1GB)
--    @DataShrinkTargetMB : MDF/NDF shrink target in MB     (default: 0 = minimum)
--    @ShrinkDBPercent    : SHRINKDATABASE free space %     (default: 10)
--    @RebuildIndexes     : 1 = Rebuild indexes after shrink (default: 1)
--    @UpdateStatsOnly    : 1 = Only update stats (faster)  (default: 0)
-- --------------------------------------------------------------------------------
--  USAGE EXAMPLES:
--
--    -- With custom paths:
--    EXEC SP_DBShrink_Maintenance
--        @LogBackupPath  = 'D:\Backup\LogBackup.bak',
--        @DataBackupPath = 'D:\Backup\FullBackup.bak'
--
--    -- Without paths (uses SQL Server default backup folder):
--    EXEC SP_DBShrink_Maintenance
--
--    -- Custom shrink targets, no index rebuild:
--    EXEC SP_DBShrink_Maintenance
--        @LogShrinkTargetMB  = s2048,
--        @DataShrinkTargetMB = 5120,
--        @RebuildIndexes     = 0,
--        @UpdateStatsOnly    = 1
-- ================================================================================

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

CREATE PROCEDURE dbo.SP_DBShrink_Maintenance
    -- ── Path Parameters ─────────────────────────────────────────────────────────
    @LogBackupPath          NVARCHAR(500) = NULL,   -- NULL = use SQL Server default
    @DataBackupPath         NVARCHAR(500) = NULL,   -- NULL = use SQL Server default

    -- ── Shrink Config ────────────────────────────────────────────────────────────
    @LogShrinkTargetMB      INT           = 1024,   -- LDF target size in MB
    @DataShrinkTargetMB     INT           = 0,      -- MDF/NDF target (0 = minimum)
    @ShrinkDBPercent        INT           = 10,     -- Free space % to leave after SHRINKDB

    -- ── Post-Shrink Maintenance ──────────────────────────────────────────────────
    @RebuildIndexes         BIT           = 1,      -- 1 = Rebuild/Reorganize indexes
    @UpdateStatsOnly        BIT           = 0       -- 1 = Only sp_updatestats (faster)
AS
BEGIN
    SET NOCOUNT ON;

    -- ============================================================================
    -- INTERNAL VARIABLES
    -- ============================================================================
    DECLARE @DB                 NVARCHAR(255);
    DECLARE @SQL                NVARCHAR(MAX);
    DECLARE @LogFile            NVARCHAR(255);
    DECLARE @DataFile           NVARCHAR(255);
    DECLARE @RecoveryModel      NVARCHAR(50);
    DECLARE @DefaultBackupPath  NVARCHAR(500);
    DECLARE @FinalLogPath       NVARCHAR(500);
    DECLARE @FinalDataPath      NVARCHAR(500);
    DECLARE @PrintMsg           NVARCHAR(1000);

    SET @DB = DB_NAME();

    -- ============================================================================
    -- STEP 0: Capture Recovery Model + Resolve Backup Paths
    -- ============================================================================

    -- Get current recovery model
    SELECT @RecoveryModel = recovery_model_desc
    FROM sys.databases
    WHERE name = @DB;

    -- Get SQL Server default backup path from registry
    EXEC master.dbo.xp_instance_regread
        N'HKEY_LOCAL_MACHINE',
        N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
        N'BackupDirectory',
        @DefaultBackupPath OUTPUT;

    -- If path not provided  use SQL default path
    SET @FinalLogPath  = ISNULL(
                            NULLIF(LTRIM(RTRIM(@LogBackupPath)),  ''),
                            @DefaultBackupPath + '\' + @DB + '_LogBackup_'
                            + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 120), '-', ''), ' ', '_'), ':', '') + '.bak'
                         );

    SET @FinalDataPath = ISNULL(
                            NULLIF(LTRIM(RTRIM(@DataBackupPath)), ''),
                            @DefaultBackupPath + '\' + @DB + '_FullBackup_'
                            + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 120), '-', ''), ' ', '_'), ':', '') + '.bak'
                         );

    -- ── Print & Log Step 0 ───────────────────────────────────────────────────────
    SET @PrintMsg = '[STEP 0] Init | DB: ' + @DB
                  + ' | Recovery: '        + @RecoveryModel
                  + ' | LogPath: '         + @FinalLogPath
                  + ' | DataPath: '        + @FinalDataPath;
    PRINT @PrintMsg;

    INSERT INTO ScheduledLogs (SchTaskID, LogType, LogDesc, LogTime)
    SELECT 10, 1,
        'DB Shrink SP Started | DB: '           + @DB
        + ' | RecoveryModel: '                  + @RecoveryModel
        + ' | LogBackupPath: '                  + @FinalLogPath
        + ' | DataBackupPath: '                 + @FinalDataPath
        + ' | LogShrinkTarget(MB): '            + CAST(@LogShrinkTargetMB  AS VARCHAR(20))
        + ' | DataShrinkTarget(MB): '           + CAST(@DataShrinkTargetMB AS VARCHAR(20))
        + ' | ShrinkDBFreePercent: '            + CAST(@ShrinkDBPercent    AS VARCHAR(10))
        + ' | RebuildIndexes: '                 + CAST(@RebuildIndexes     AS VARCHAR(5))
        + ' | UpdateStatsOnly: '                + CAST(@UpdateStatsOnly    AS VARCHAR(5)),
        GETDATE();
    PRINT '     Step 0 Log inserted into ScheduledLogs';

    BEGIN TRY

        -- ========================================================================
        -- STEP 1: Log Backup BEFORE switching to SIMPLE (preserve log chain)
        -- ========================================================================
        IF @RecoveryModel = 'FULL'
        BEGIN
            PRINT '';
            PRINT '[STEP 1] Taking Log Backup before SIMPLE switch...';

            SET @SQL = 'BACKUP LOG [' + @DB + '] TO DISK = ''' + @FinalLogPath + ''' WITH INIT, NO_COMPRESSION, STATS = 10;';
            EXEC(@SQL);

            PRINT '     Log Backup completed: ' + @FinalLogPath;
            INSERT INTO ScheduledLogs (SchTaskID, LogType, LogDesc, LogTime)
            SELECT 10, 1,
                'Step 1 Done | Log Backup taken before SIMPLE switch | Path: ' + @FinalLogPath,
                GETDATE();
            PRINT '     Step 1 Log inserted into ScheduledLogs';
        END
        ELSE
        BEGIN
            PRINT '';
            PRINT '[STEP 1] Skipped — Recovery is not FULL, no log backup needed.';
            INSERT INTO ScheduledLogs (SchTaskID, LogType, LogDesc, LogTime)
            SELECT 10, 1, 'Step 1 Skipped | Recovery not FULL | DB: ' + @DB, GETDATE();
            PRINT '     Step 1 Log inserted into ScheduledLogs';
        END

        -- ========================================================================
        -- STEP 2: Set RECOVERY SIMPLE
        -- ========================================================================
        PRINT '';
        PRINT '[STEP 2] Setting Recovery Model to SIMPLE...';

        SET @SQL = 'ALTER DATABASE [' + @DB + '] SET RECOVERY SIMPLE;';
        EXEC(@SQL);

        PRINT '     Recovery set to SIMPLE.';
        INSERT INTO ScheduledLogs (SchTaskID, LogType, LogDesc, LogTime)
        SELECT 10, 1, 'Step 2 Done | Recovery set to SIMPLE | DB: ' + @DB, GETDATE();
        PRINT '     Step 2 Log inserted into ScheduledLogs';

        -- ========================================================================
        -- STEP 3: CHECKPOINT (flush dirty pages  shrink more effective)
        -- ========================================================================
        PRINT '';
        PRINT '[STEP 3] Running CHECKPOINT...';

        SET @SQL = 'USE [' + @DB + ']; CHECKPOINT;';
        EXEC(@SQL);

        PRINT '     CHECKPOINT done.';
        INSERT INTO ScheduledLogs (SchTaskID, LogType, LogDesc, LogTime)
        SELECT 10, 1, 'Step 3 Done | CHECKPOINT executed | DB: ' + @DB, GETDATE();
        PRINT '     Step 3 Log inserted into ScheduledLogs';

        -- ========================================================================
        -- STEP 4: Shrink LOG file (LDF)
        -- ========================================================================
        PRINT '';
        PRINT '[STEP 4] Shrinking LOG file (LDF)...';

        SELECT @LogFile = name
        FROM sys.master_files
        WHERE database_id = DB_ID(@DB) AND type = 1;  -- type 1 = LDF

        IF @LogFile IS NOT NULL
        BEGIN
            PRINT '     LDF File found: ' + @LogFile;

            SET @SQL = '
                SET NOCOUNT ON;
                USE [' + @DB + '];
                DECLARE @Before DECIMAL(18,2) = (SELECT CAST(size AS DECIMAL(18,2)) / 128 FROM sys.database_files WHERE name = ''' + @LogFile + ''');
                DBCC SHRINKFILE ([' + @LogFile + '], ' + CAST(@LogShrinkTargetMB AS VARCHAR(20)) + ') WITH NO_INFOMSGS;
                DECLARE @After DECIMAL(18,2) = (SELECT CAST(size AS DECIMAL(18,2)) / 128 FROM sys.database_files WHERE name = ''' + @LogFile + ''');
                INSERT INTO ScheduledLogs (SchTaskID, LogType, LogDesc, LogTime)
                SELECT 10, 1,
                    ''Step 4 Done | LDF Shrink | File: ' + @LogFile + ' | Before(MB): '' + CAST(@Before AS VARCHAR(30))
                    + '' | After(MB): '' + CAST(@After AS VARCHAR(30))
                    + '' | Saved(MB): '' + CAST((@Before - @After) AS VARCHAR(30)),
                    GETDATE();
            ';
            EXEC(@SQL);

            PRINT '     LDF shrink done. Target: ' + CAST(@LogShrinkTargetMB AS VARCHAR(20)) + ' MB';
            PRINT '     Step 4 Log inserted into ScheduledLogs';
        END
        ELSE
        BEGIN
            PRINT '     No LDF file found. Step 4 Skipped.';
            INSERT INTO ScheduledLogs (SchTaskID, LogType, LogDesc, LogTime)
            SELECT 10, 1, 'Step 4 Skipped | No LDF file found | DB: ' + @DB, GETDATE();
            PRINT '     Step 4 Log inserted into ScheduledLogs';
        END

        -- ========================================================================
        -- STEP 5: Shrink each DATA file (MDF / NDF)
        -- ========================================================================
        PRINT '';
        PRINT '[STEP 5] Shrinking DATA files (MDF/NDF)...';

        DECLARE @MDFFiles TABLE (ID INT IDENTITY(1,1), FileName NVARCHAR(255));

        INSERT INTO @MDFFiles (FileName)
        SELECT name
        FROM sys.master_files
        WHERE database_id = DB_ID(@DB) AND type = 0;  -- type 0 = MDF/NDF

        DECLARE @Total INT = (SELECT COUNT(*) FROM @MDFFiles);
        DECLARE @I     INT = 1;

        PRINT '     Total data files found: ' + CAST(@Total AS VARCHAR(10));

        WHILE @I <= @Total
        BEGIN
            SELECT @DataFile = FileName FROM @MDFFiles WHERE ID = @I;

            PRINT '     Shrinking file [' + CAST(@I AS VARCHAR(5)) + '/' + CAST(@Total AS VARCHAR(5)) + ']: ' + @DataFile;

            SET @SQL = '
                SET NOCOUNT ON;
                USE [' + @DB + '];
                DECLARE @Before DECIMAL(18,2) = (SELECT CAST(size AS DECIMAL(18,2)) / 128 FROM sys.database_files WHERE name = ''' + @DataFile + ''');
                DBCC SHRINKFILE ([' + @DataFile + '], ' + CAST(@DataShrinkTargetMB AS VARCHAR(20)) + ') WITH NO_INFOMSGS;
                DECLARE @After DECIMAL(18,2) = (SELECT CAST(size AS DECIMAL(18,2)) / 128 FROM sys.database_files WHERE name = ''' + @DataFile + ''');
                INSERT INTO ScheduledLogs (SchTaskID, LogType, LogDesc, LogTime)
                SELECT 10, 1,
                    ''Step 5 Done | MDF/NDF Shrink | File: ' + @DataFile + ' | Before(MB): '' + CAST(@Before AS VARCHAR(30))
                    + '' | After(MB): '' + CAST(@After AS VARCHAR(30))
                    + '' | Saved(MB): '' + CAST((@Before - @After) AS VARCHAR(30)),
                    GETDATE();
            ';
            EXEC(@SQL);

            PRINT '         File shrink done. Log inserted into ScheduledLogs';
            SET @I = @I + 1;
        END;

        PRINT '     All data files shrink completed.';

        -- ========================================================================
        -- STEP 6: SHRINKDATABASE — catch remaining free space
        -- ========================================================================
        PRINT '';
        PRINT '[STEP 6] Running SHRINKDATABASE (' + CAST(@ShrinkDBPercent AS VARCHAR(5)) + '% free space)...';

        SET @SQL = '
            SET NOCOUNT ON;
            USE [' + @DB + '];
            DECLARE @Before DECIMAL(18,2) = (SELECT SUM(CAST(size AS DECIMAL(18,2)) / 128) FROM sys.database_files);
            DBCC SHRINKDATABASE ([' + @DB + '], ' + CAST(@ShrinkDBPercent AS VARCHAR(10)) + ') WITH NO_INFOMSGS;
            DECLARE @After DECIMAL(18,2) = (SELECT SUM(CAST(size AS DECIMAL(18,2)) / 128) FROM sys.database_files);
            INSERT INTO ScheduledLogs (SchTaskID, LogType, LogDesc, LogTime)
            SELECT 10, 1,
                ''Step 6 Done | SHRINKDATABASE | DB: ' + @DB + ' | Before(MB): '' + CAST(@Before AS VARCHAR(30))
                + '' | After(MB): ''  + CAST(@After AS VARCHAR(30))
                + '' | Saved(MB): ''  + CAST((@Before - @After) AS VARCHAR(30)),
                GETDATE();
        ';
        EXEC(@SQL);

        PRINT '     SHRINKDATABASE done.';
        PRINT '     Step 6 Log inserted into ScheduledLogs';

        -- ========================================================================
        -- STEP 7: Restore original Recovery Model
        -- ========================================================================
        PRINT '';
        PRINT '[STEP 7] Restoring Recovery Model to: ' + @RecoveryModel + '...';

        SET @SQL = 'ALTER DATABASE [' + @DB + '] SET RECOVERY ' + @RecoveryModel + ';';
        EXEC(@SQL);

        PRINT '     Recovery restored to: ' + @RecoveryModel;
        INSERT INTO ScheduledLogs (SchTaskID, LogType, LogDesc, LogTime)
        SELECT 10, 1, 'Step 7 Done | Recovery restored to: ' + @RecoveryModel + ' | DB: ' + @DB, GETDATE();
        PRINT '     Step 7 Log inserted into ScheduledLogs';

        -- ========================================================================
        -- STEP 8: Log Backup AFTER restoring FULL Recovery (restart log chain)
        -- ========================================================================
        IF @RecoveryModel = 'FULL'
        BEGIN
            PRINT '';
            PRINT '[STEP 8] Taking Log Backup after FULL recovery restored (restart log chain)...';

            -- Append timestamp suffix so file name is unique from Step 1 backup
            DECLARE @Step8LogPath NVARCHAR(500);
            SET @Step8LogPath = ISNULL(
                                    NULLIF(LTRIM(RTRIM(@LogBackupPath)), ''),
                                    @DefaultBackupPath + '\' + @DB + '_LogBackup_PostShrink_'
                                    + REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 120), '-', ''), ' ', '_'), ':', '') + '.bak'
                                );

            SET @SQL = 'BACKUP LOG [' + @DB + '] TO DISK = ''' + @Step8LogPath + ''' WITH INIT, NO_COMPRESSION, STATS = 10;';
            EXEC(@SQL);

            PRINT '     Post-shrink Log Backup completed: ' + @Step8LogPath;
            INSERT INTO ScheduledLogs (SchTaskID, LogType, LogDesc, LogTime)
            SELECT 10, 1,
                'Step 8 Done | Post-shrink Log Backup | Path: ' + @Step8LogPath,
                GETDATE();
            PRINT '     Step 8 Log inserted into ScheduledLogs';
        END
        ELSE
        BEGIN
            PRINT '';
            PRINT '[STEP 8] Skipped — Recovery is not FULL.';
            INSERT INTO ScheduledLogs (SchTaskID, LogType, LogDesc, LogTime)
            SELECT 10, 1, 'Step 8 Skipped | Recovery not FULL | DB: ' + @DB, GETDATE();
            PRINT '     Step 8 Log inserted into ScheduledLogs';
        END

        -- ========================================================================
        -- STEP 9: Index Rebuild / Reorganize OR Update Stats
        -- ========================================================================
        IF @RebuildIndexes = 1
        BEGIN
            PRINT '';
            PRINT '[STEP 9] Starting Index Rebuild/Reorganize (fragmentation > 10%)...';
            INSERT INTO ScheduledLogs (SchTaskID, LogType, LogDesc, LogTime)
            SELECT 10, 1, 'Step 9 Started | Index Rebuild/Reorganize | DB: ' + @DB, GETDATE();
            PRINT '     Step 9 Start Log inserted into ScheduledLogs';

            SET @SQL = '
                SET NOCOUNT ON;
                USE [' + @DB + '];
                DECLARE @tbl        NVARCHAR(500);
                DECLARE @idx        NVARCHAR(500);
                DECLARE @frag       DECIMAL(5,2);
                DECLARE @rebuildSQL NVARCHAR(MAX);

                DECLARE idx_cursor CURSOR FOR
                SELECT
                    QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name),
                    QUOTENAME(i.name),
                    ips.avg_fragmentation_in_percent
                FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ''LIMITED'') ips
                INNER JOIN sys.tables  t  ON t.object_id  = ips.object_id
                INNER JOIN sys.schemas s  ON s.schema_id  = t.schema_id
                INNER JOIN sys.indexes i  ON i.object_id  = ips.object_id
                                         AND i.index_id   = ips.index_id
                WHERE ips.avg_fragmentation_in_percent > 10
                  AND ips.index_id  > 0          -- skip heaps
                  AND ips.page_count > 100;       -- skip tiny tables

                OPEN idx_cursor;
                FETCH NEXT FROM idx_cursor INTO @tbl, @idx, @frag;

                WHILE @@FETCH_STATUS = 0
                BEGIN
                    IF @frag >= 30
                        SET @rebuildSQL = ''ALTER INDEX '' + @idx + '' ON '' + @tbl + '' REBUILD WITH (ONLINE = OFF, SORT_IN_TEMPDB = ON);'';
                    ELSE
                        SET @rebuildSQL = ''ALTER INDEX '' + @idx + '' ON '' + @tbl + '' REORGANIZE;'';

                    BEGIN TRY
                        EXEC(@rebuildSQL);
                    END TRY
                    BEGIN CATCH
                        -- silently skip (e.g. LOB columns or disabled indexes)
                    END CATCH

                    FETCH NEXT FROM idx_cursor INTO @tbl, @idx, @frag;
                END;

                CLOSE idx_cursor;
                DEALLOCATE idx_cursor;
            ';
            EXEC(@SQL);

            PRINT '     Index Rebuild/Reorganize completed.';
            INSERT INTO ScheduledLogs (SchTaskID, LogType, LogDesc, LogTime)
            SELECT 10, 1, 'Step 9 Done | Index Rebuild/Reorganize completed | DB: ' + @DB, GETDATE();
            PRINT '     Step 9 Done Log inserted into ScheduledLogs';
        END
        ELSE IF @UpdateStatsOnly = 1
        BEGIN
            PRINT '';
            PRINT '[STEP 9] Running Update Stats only...';

            SET @SQL = 'USE [' + @DB + ']; EXEC sys.sp_updatestats;';
            EXEC(@SQL);

            PRINT '     sp_updatestats completed.';
            INSERT INTO ScheduledLogs (SchTaskID, LogType, LogDesc, LogTime)
            SELECT 10, 1, 'Step 9 Done | sp_updatestats completed | DB: ' + @DB, GETDATE();
            PRINT '     Step 9 Log inserted into ScheduledLogs';
        END
        ELSE
        BEGIN
            PRINT '';
            PRINT '[STEP 9] Skipped — RebuildIndexes=0 and UpdateStatsOnly=0.';
            INSERT INTO ScheduledLogs (SchTaskID, LogType, LogDesc, LogTime)
            SELECT 10, 1, 'Step 9 Skipped | RebuildIndexes=0, UpdateStatsOnly=0 | DB: ' + @DB, GETDATE();
            PRINT '     Step 9 Log inserted into ScheduledLogs';
        END

        -- ========================================================================
        -- STEP 10: Final Summary
        -- ========================================================================
        PRINT '';
        PRINT '[STEP 10] Logging final summary...';

        SET @SQL = '
            SET NOCOUNT ON;
            USE [' + @DB + '];
            DECLARE @FinalSizeMB DECIMAL(18,2) = (SELECT SUM(CAST(size AS DECIMAL(18,2)) / 128) FROM sys.database_files);
            INSERT INTO ScheduledLogs (SchTaskID, LogType, LogDesc, LogTime)
            SELECT 10, 1,
                ''Step 10 | DB Shrink SP Completed Successfully | DB: ' + @DB + ' | Final Total Size(MB): '' + CAST(@FinalSizeMB AS VARCHAR(30)),
                GETDATE();
        ';
        EXEC(@SQL);

        PRINT '     Final summary Log inserted into ScheduledLogs';
        PRINT '';
        PRINT '====================================================';
        PRINT ' SP_DBShrink_Maintenance — COMPLETED SUCCESSFULLY';
        PRINT ' DB: ' + @DB;
        PRINT '====================================================';

    END TRY
    BEGIN CATCH

        PRINT '';
        PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!';
        PRINT ' SP_DBShrink_Maintenance — ERROR OCCURRED';
        PRINT ' Error  : ' + ERROR_MESSAGE();
        PRINT ' Line   : ' + CAST(ERROR_LINE()     AS VARCHAR(10));
        PRINT ' Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10));
        PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!';

        -- ────────────────────────────────────────────────────────────────────────
        -- CRITICAL: Restore Recovery Model inside CATCH
        -- Agar yahan nahi kiya  DB SIMPLE mein stuck rahega (production disaster)
        -- ────────────────────────────────────────────────────────────────────────
        BEGIN TRY
            PRINT '[CATCH] Restoring Recovery Model to: ' + @RecoveryModel + '...';
            SET @SQL = 'ALTER DATABASE [' + @DB + '] SET RECOVERY ' + @RecoveryModel + ';';
            EXEC(@SQL);
            PRINT '     Recovery Model restored successfully in CATCH block.';
        END TRY
        BEGIN CATCH
            PRINT '[CATCH-INNER] CRITICAL — Could not restore Recovery Model!';
            PRINT '              Please run manually: ALTER DATABASE [' + @DB + '] SET RECOVERY ' + @RecoveryModel;
            INSERT INTO ScheduledLogs (SchTaskID, LogType, LogDesc, LogTime)
            SELECT 10, 2,
                'CRITICAL | Recovery Model could NOT be restored to ' + @RecoveryModel
                + ' | DB: ' + @DB
                + ' | Fix manually!',
                GETDATE();
            PRINT '     CRITICAL Log inserted into ScheduledLogs';
        END CATCH

        -- Log the actual error
        INSERT INTO ScheduledLogs (SchTaskID, LogType, LogDesc, LogTime)
        SELECT 10, 2,
            'SP_DBShrink_Maintenance FAILED | DB: '   + @DB
            + ' | Error: '                             + ERROR_MESSAGE()
            + ' | Line: '                              + CAST(ERROR_LINE()     AS VARCHAR(10))
            + ' | Severity: '                          + CAST(ERROR_SEVERITY() AS VARCHAR(10))
            + ' | State: '                             + CAST(ERROR_STATE()    AS VARCHAR(10)),
            GETDATE();
        PRINT '[CATCH] Error Log inserted into ScheduledLogs';

    END CATCH

END
GO

-- ================================================================================
-- END OF SP
-- ================================================================================

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

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

  1. Recovery Model check karta hai

  2. Log backup leta hai (agar FULL mode me hai)

  3. Database ko SIMPLE mode me switch karta hai

  4. CHECKPOINT run karta hai

  5. Log file (LDF) shrink karta hai

  6. Data files (MDF/NDF) shrink karta hai

  7. ShrinkDatabase run karta hai

  8. Recovery model wapas restore karta hai

  9. Index rebuild / stats update karta hai

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

🔹 Transaction Log (LDF)

  • Har change (INSERT/UPDATE/DELETE) yahan record hota hai

  • Ye grow karta rehta hai agar truncate nahi hua

👉 Problem:

  • Log file 100GB ho gaya 😱

👉 Solution:

  • Log backup + shrink

🔹 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

🔹 DBCC SHRINKFILE

👉 Specific file shrink karta hai (LDF ya MDF)

Example:

SQL 
DBCC SHRINKFILE (LogFileName, 1024)

👉 Target size MB me diya jata hai

🔹 DBCC SHRINKDATABASE

👉 Pura database shrink karta hai

  • Remaining free space clean karta hai

  • Percent ke basis par

🔹 Index Fragmentation

  • Data pages scattered ho jate hain

  • Performance slow ho jata hai

Solution:

  • REBUILD → heavy but best

  • REORGANIZE → light but slower

🔹 Statistics

  • Query optimizer ko data distribution batata hai

👉 Agar outdated ho:

  • Query slow ho jati hai

⚙️ 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)

SQL 
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

SQL 
ALTER DATABASE SET RECOVERY SIMPLE

👉 Benefit:

  • Log auto truncate ho jayega

🟢 STEP 3 – CHECKPOINT

SQL 
CHECKPOINT

👉 Memory → Disk flush
👉 Shrink effective banata hai

🟢 STEP 4 – Shrink LOG file

SQL 
DBCC SHRINKFILE (LDF)

👉 Target size:

SQL 
@LogShrinkTargetMB = 1024

🟢 STEP 5 – Shrink Data files

Loop me:

SQL 
DBCC SHRINKFILE (MDF/NDF)

👉 Har file individually shrink hoti hai

🟢 STEP 6 – SHRINKDATABASE

SQL 
DBCC SHRINKDATABASE

👉 Remaining free space clean

🟢 STEP 7 – Restore Recovery Model

SQL 
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. Important Warnings (Real-World)

❌ Daily shrink mat karo
❌ Production peak hours me mat run karo

👉 Kyun?

  • Fragmentation badhata hai

  • Performance degrade hoti hai

✅ 5. Best Practices

✔ Shrink only when:

  • Log file abnormal grow ho gaya ho

  • One-time cleanup

✔ Always:

  • Backup lo

  • Index rebuild karo

  • Monitoring karo

🔥 6. Is SP ki Strengths

✔ Fully automated
✔ Logging system built-in
✔ Safe recovery model handling
✔ Error handling with TRY/CATCH
✔ Production ready

👉 Ye ek enterprise-grade script hai 💯

🧾 7. Final Verdict

👉 Ye SP perfect hai controlled shrink operations ke liye
👉 Specially jab:

  • Log file uncontrolled grow ho gaya ho

  • Emergency cleanup karna ho

⚠️ But:

  • Regular maintenance ke liye use mat karo

  • Always use with understanding

Tags

SQLServerDatabaseMaintenanceDBShrinkTransactionLogSQLPerformanceDBAIndexOptimizationSQLTipsSQLServerDBADataEngineeringSQLHindiDatabaseOptimizationMSSQLStoredProcedureSQLBestPractices

Keep building your data skillset

Explore more SQL, Python, analytics, and engineering tutorials.

Browse All Posts