Database Shrink in SQL Server

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

DBCC SHRINKDATABASE (YourDatabaseName, target_percent);

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

Example:

DBCC SHRINKDATABASE (AdventureWorks, 10);

2. Shrink Specific File (Data ya Log File)

DBCC SHRINKFILE (FileName, target_size_in_MB);

Example:

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

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

DBCC SHRINKFILE (AdventureWorks_Log, 200);

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

🔍 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);  -- 50 MB tak shrink karo

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;

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

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;
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

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

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.