MSSQL ٹرانزیکشن لاگ کا بھر جانا: روک تھام اور فوری بحالی کی حکمت عملی

مائیکروسافٹ ایس کیو ایل سرور (Microsoft SQL Server) کو منظم کرنے والے ڈیٹا بیس ایڈمنسٹریٹرز (DBAs) اور ڈیواپس (DevOps) انجینئرز کے لیے، چند الرٹس ہی اتنی فوری پریشانی کا باعث بنتے ہیں جتنا کہ ایرر 9002: ڈیٹا بیس ‘X’ کے لیے ٹرانزیکشن لاگ مکمل ہو چکا ہے۔ جب ٹرانزیکشن لاگ بھر جاتا ہے اور مزید بڑھ نہیں سکتا، تو ڈیٹا بیس مؤثر طریقے سے صرف پڑھنے کے قابل (read-only) ہو جاتا ہے۔ تمام INSERT، UPDATE، اور DELETE آپریشنز رک جاتے ہیں، ایپلیکیشن ٹرانزیکشنز ناکام ہو جاتی ہیں، اور پروڈکشن کا کام ٹھپ ہو جاتا ہے۔

ایس کیو ایل سرور ٹرانزیکشن لاگ کے بنیادی فن تعمیر کو سمجھنا، بنیادی وجہ کی درست تشخیص کرنا، اور فوری بحالی کے طریقہ کار پر عمل درآمد کرنا ہائی اویلیبلٹی (high availability) کو برقرار رکھنے کے لیے اہم مہارتیں ہیں۔ یہ جامع گائیڈ ٹرانزیکشن لاگ کے میکانکس، ہنگامی صورتحال میں بھرے ہوئے لاگ کو حل کرنے کے طریقے، اور اسے دوبارہ ہونے سے روکنے کے لیے آرکیٹیکچرل بہترین طریقوں کی وضاحت کرتی ہے۔

ایس کیو ایل سرور ٹرانزیکشن لاگ آرکیٹیکچر کو سمجھنا

بھرے ہوئے ٹرانزیکشن لاگ کو مؤثر طریقے سے ٹربل شوٹ کرنے کے لیے، آپ کو پہلے یہ سمجھنا ہوگا کہ ایس کیو ایل سرور ڈیٹا کو کیسے لکھتا اور منظم کرتا ہے۔

رائٹ-اوہیڈ لاگنگ (Write-Ahead Logging – WAL)

ایس کیو ایل سرور رائٹ-اوہیڈ لاگنگ (WAL) پروٹوکول کا استعمال کرتا ہے۔ جب بھی ڈیٹا میں کوئی تبدیلی ہوتی ہے، تو تبدیلی پہلے میموری میں ٹرانزیکشن لاگ میں لکھی جاتی ہے، پھر ڈیٹا بیس فائلوں (MDF/NDF) میں اصل ڈیٹا پیجز کو اپ ڈیٹ کرنے سے پہلے ڈسک پر موجود فزیکل لاگ فائل میں منتقل کی جاتی ہے۔ یہ ACID (ایٹمسٹی، کنسسٹنسی، آئسولیشن، ڈیوریبلٹی) کی تعمیل کی ضمانت دیتا ہے، اس بات کو یقینی بناتا ہے کہ کریش کی صورت میں، ایس کیو ایل سرور ٹرانزیکشنز کو دوبارہ چلا (roll forward) یا منسوخ (roll back) کر سکے۔

ورچوئل لاگ فائلز (VLFs) اور سرکلر لاگنگ

اندرونی طور پر، فزیکل ٹرانزیکشن لاگ فائل (LDF) کو چھوٹے، منطقی حصوں میں تقسیم کیا جاتا ہے جنہیں ورچوئل لاگ فائلز (VLFs) کہتے ہیں۔ ٹرانزیکشن لاگ سرکلر (گول) انداز میں کام کرتا ہے۔ جیسے جیسے لاگ ریکارڈز لکھے جاتے ہیں، وہ ایک VLF کو بھرتے ہیں اور اگلے کی طرف بڑھتے ہیں۔

جب لاگ فزیکل فائل کے آخر تک پہنچ جاتا ہے، تو یہ شروع میں واپس آنے کی کوشش کرتا ہے۔ تاہم، یہ صرف تب ہی VLF کو اوور رائٹ کر سکتا ہے اگر وہ VLF غیر فعال (inactive) کے طور پر نشان زد ہو۔ اگر تمام VLFs فعال ہیں (یعنی ان میں ایسے لاگ ریکارڈز ہیں جن کی ایس کیو ایل سرور کو ابھی بھی ضرورت ہے)، تو لاگ واپس نہیں آ سکتا۔ اگر آٹو-گروتھ فعال ہے اور ڈسک کی جگہ دستیاب ہے، تو فزیکل فائل بڑھ جاتی ہے۔ اگر ڈسک بھری ہوئی ہے یا آٹو-گروتھ محدود ہے، تو آپ کو ایرر 9002 کا سامنا کرنا پڑتا ہے۔

لاگ ٹرنکیشن بمقابلہ لاگ شرنکنگ

ایک عام غلط فہمی یہ ہے کہ لاگ کو ٹرنکیٹ کرنے سے فزیکل فائل کا سائز کم ہو جاتا ہے۔
* لاگ ٹرنکیشن (Log Truncation): فعال VLFs کو غیر فعال کے طور پر نشان زد کرنے کا عمل، جس سے جگہ دوبارہ استعمال کے لیے دستیاب ہو جاتی ہے۔ یہ ڈسک پر LDF فائل کے سائز کو کم نہیں کرتا ہے۔
* لاگ شرنکنگ (Log Shrinking): LDF فائل کے سائز کو جسمانی طور پر کم کرنے اور آپریٹنگ سسٹم کو جگہ واپس کرنے کا عمل۔

فل ریکوری ماڈل میں، لاگ ٹرنکیشن صرف تب ہوتی ہے جب ٹرانزیکشن لاگ کا بیک اپ کامیابی کے ساتھ مکمل ہو جائے (بشرطیکہ کوئی اور عمل لاگ کو فعال نہ رکھے ہوئے ہو)۔

"ٹرانزیکشن لاگ فل” ایرر (ایرر 9002) کی تشخیص

جب لاگ بھر جاتا ہے، تو آپ کا پہلا قدم اندھا دھند ڈسک کی جگہ بڑھانا یا فائلوں کو شرنک کرنا نہیں ہونا چاہیے۔ آپ کو یہ شناخت کرنا ہوگا کہ لاگ ٹرنکیٹ کیوں نہیں ہو سکتا۔ ایس کیو ایل سرور sys.databases کیٹلاگ ویو کے ذریعے آپ کو یہ بتانے کے لیے ایک بلٹ ان میکانزم فراہم کرتا ہے کہ لاگ کے دوبارہ استعمال کو کیا چیز روک رہی ہے۔

رکاوٹ کی شناخت کے لیے درج ذیل T-SQL کمانڈ چلائیں:

SELECT 
    name AS DatabaseName, 
    recovery_model_desc AS RecoveryModel, 
    log_reuse_wait_desc AS LogReuseWaitReason
FROM sys.databases
WHERE name = 'YourDatabaseName';

آپ درج ذیل کمانڈ کا استعمال کرتے ہوئے اپنے ٹرانزیکشن لاگز کے موجودہ جگہ کے استعمال کو بھی چیک کر سکتے ہیں:

DBCC SQLPERF(LOGSPACE);

عام log_reuse_wait_desc حالتیں

1. LOG_BACKUP: ڈیٹا بیس فل یا بلک-لاگڈ ریکوری ماڈل میں ہے، اور حال ہی میں ٹرانزیکشن لاگ کا بیک اپ نہیں لیا گیا ہے۔ یہ سب سے عام وجہ ہے۔
2. ACTIVE_TRANSACTION: ایک طویل عرصے سے چلنے والی ٹرانزیکشن (مثال کے طور پر، ایک بڑا انڈیکس ری بلڈ یا کوئی بھولی ہوئی غیر کمٹڈ ٹرانزیکشن) لاگ کو فعال رکھے ہوئے ہے۔
3. REPLICATION / CDC: ٹرانزیکشنل ریپلیکیشن یا چینج ڈیٹا کیپچر (CDC) فعال ہے، اور لاگ ریڈر ایجنٹ نے ابھی تک ٹرانزیکشنز پر عمل نہیں کیا ہے۔
4. AVAILABILITY_REPLICA: AlwaysOn اویلیبلٹی گروپ میں، ایک سیکنڈری ریپلیکا منقطع ہے یا بہت آہستہ مطابقت پذیر (synchronizing) ہو رہا ہے، جس کی وجہ سے پرائمری ریپلیکا کو لاگ ریکارڈز کو تب تک برقرار رکھنا پڑتا ہے جب تک کہ وہ سیکنڈری پر محفوظ نہ ہو جائیں۔

فوری بحالی کی حکمت عملی: پروڈکشن میں مسئلے کو حل کرنا

واپس آنے والے log_reuse_wait_desc پر منحصر ہے، آپ کا ہنگامی ردعمل مختلف ہوگا۔ یہاں سب سے عام منظرناموں کے لیے فوری بحالی کی حکمت عملی دی گئی ہے۔

منظرنامہ 1: لاگ بیک اپ غائب یا ناکام (LOG_BACKUP)

اگر ویٹ ٹائپ LOG_BACKUP ہے، تو حل سیدھا ہے: آپ کو ٹرانزیکشن لاگ کا بیک اپ لینا ہوگا۔

BACKUP LOG [YourDatabaseName] 
TO DISK = 'N:BackupsYourDatabaseName_EmergencyLog.trn' 
WITH COMPRESSION, STATS = 10;

ایک بار بیک اپ مکمل ہونے کے بعد، غیر فعال VLFs ٹرنکیٹ ہو جائیں گے، اور ایس کیو ایل سرور معمول کے مطابق کام شروع کر دے گا۔ اگر آپ کی بیک اپ ڈرائیو بھری ہوئی ہے، تو آپ کو عارضی نیٹ ورک شیئر یا نل ڈیوائس (null device) پر بیک اپ لینے کی ضرورت پڑ سکتی ہے (اس کی انتہائی حوصلہ شکنی کی جاتی ہے جب تک کہ ڈیٹا بیس آسانی سے دوبارہ تیار نہ کیا جا سکے، کیونکہ یہ لاگ چین کو توڑ دیتا ہے):

-- انتباہ: یہ لاگ چین کو توڑ دیتا ہے اور پوائنٹ-ان-ٹائم ریکوری کو متاثر کرتا ہے۔
-- صرف تب استعمال کریں جب بالکل ضروری ہو اور فوراً بعد ایک مکمل (FULL) بیک اپ لیں۔
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

منظرنامہ 2: طویل عرصے سے چلنے والی فعال ٹرانزیکشنز (ACTIVE_TRANSACTION)

اگر کوئی ایک ٹرانزیکشن گھنٹوں سے چل رہی ہے، تو یہ پوری مدت کے لیے لاگ ٹرنکیشن کو روکتی ہے۔ سب سے پہلے، مجرم ٹرانزیکشن کی شناخت کریں:

DBCC OPENTRAN('YourDatabaseName');

یہ کمانڈ قدیم ترین فعال ٹرانزیکشن اور اس کی سرور پروسیس آئی ڈی (SPID) واپس کرتی ہے۔ آپ ڈائنامک مینجمنٹ ویوز (DMVs) کو استفسار کرکے اس بارے میں مزید تفصیلات حاصل کر سکتے ہیں کہ SPID کیا کر رہا ہے:

SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    r.start_time,
    r.status,
    r.command,
    t.text AS QueryText
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.session_id = <SPID_FROM_DBCC_OPENTRAN>;

اگر ٹرانزیکشن کوئی غلط استفسار یا رکا ہوا عمل ہے، تو آپ کو لاگ کو آزاد کرنے کے لیے اسے ختم کرنے کی ضرورت پڑ سکتی ہے۔

KILL <SPID>;

نوٹ: ایک بڑی ٹرانزیکشن کو ختم کرنے سے رول بیک شروع ہو جائے گا، جس میں کافی وقت لگ سکتا ہے اور یہ عارضی طور پر اضافی لاگ سرگرمی پیدا کرے گا۔ رول بیک کے دوران ایس کیو ایل سرور سروس کو دوبارہ شروع نہ کریں، ورنہ ڈیٹا بیس دوبارہ شروع ہونے پر ریکوری موڈ میں چلا جائے گا۔

منظرنامہ 3: ہنگامی جگہ کی تخصیص (ڈسک 100% بھری ہوئی ہے)

اگر LDF فائل نے پوری ڈرائیو استعمال کر لی ہے، تو آپ بیک اپ بھی نہیں چلا سکتے کیونکہ ایس کیو ایل سرور کو خود بیک اپ ایونٹ کو ریکارڈ کرنے کے لیے لاگ کی تھوڑی سی جگہ درکار ہوتی ہے۔ اس منظر نامے میں، آپ کو دستیاب جگہ والی دوسری ڈرائیو پر ایک ثانوی لاگ فائل شامل کرنی ہوگی۔

ALTER DATABASE [YourDatabaseName]
ADD LOG FILE 
(
    NAME = N'YourDatabaseName_Log2',
    FILENAME = N'E:TempLogsYourDatabaseName_Log2.ldf',
    SIZE = 5GB,
    MAXSIZE = 50GB,
    FILEGROWTH = 1GB
);

یہ فوری طور پر ایس کیو ایل سرور کو سانس لینے کی جگہ فراہم کرتا ہے۔ ایک بار جب ڈیٹا بیس آن لائن ہو جائے، تو ٹرانزیکشن لاگ کا بیک اپ لیں، ثانوی لاگ فائل کو خالی کریں، اور اسے ہٹا دیں:

-- 1. لاگ کو ٹرنکیٹ کرنے کے لیے لاگ بیک اپ لیں
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. عارضی لاگ فائل کو خالی کریں
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. عارضی لاگ فائل کو ہٹا دیں
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

ٹرانزیکشن لاگ کی روک تھام اور انتظام کے لیے بہترین طریقے

ردعمل پر مبنی ٹربل شوٹنگ تناؤ کا باعث بنتی ہے اور SLAs کو متاثر کرتی ہے۔ انٹرپرائز ڈیٹا بیس کے استحکام کے لیے فعال آرکیٹیکچرل اور آپریشنل بہترین طریقوں کو نافذ کرنا ضروری ہے۔

1. ایک مضبوط، خودکار بیک اپ حکمت عملی نافذ کریں

اگر کوئی ڈیٹا بیس فل ریکوری ماڈل میں ہے، تو ٹرانزیکشن لاگ کے بار بار بیک اپ لینا لازمی ہے۔ آپ کے ریکوری پوائنٹ آبجیکٹو (RPO) اور ٹرانزیکشن کے حجم کے لحاظ سے، لاگ بیک اپ ہر 5 سے 15 منٹ میں ہونے چاہئیں۔

CloudSave جیسے انٹرپرائز بیک اپ سلوشنز اس عمل کو نمایاں طور پر آسان بناتے ہیں۔ VDI (ورچوئل ڈیوائس انٹرفیس) کے ذریعے براہ راست ایس کیو ایل سرور کے ساتھ انضمام کرکے، CloudSave DBAs کو پالیسی پر مبنی، ہائی فریکوئنسی ٹرانزیکشن لاگ بیک اپ ترتیب دینے کی اجازت دیتا ہے۔ یہ یقینی بناتا ہے کہ لاگز مسلسل ٹرنکیٹ، محفوظ طریقے سے انکرپٹ، اور آف سائٹ یا ناقابل تبدیلی کلاؤڈ اسٹوریج میں محفوظ رہیں، جس سے پیچیدہ کسٹم ایس کیو ایل ایجنٹ جابز کی ضرورت کے بغیر LOG_BACKUP ویٹ اسٹیٹ کو روکا جا سکے۔

2. ٹرانزیکشن لاگ کا صحیح سائز رکھیں اور VLFs کا انتظام کریں

اپنے ٹرانزیکشن لاگ کے سائز کو منظم کرنے کے لیے آٹو-گروتھ پر انحصار کرنا ایک خطرناک اینٹی-پیٹرن ہے۔ آٹو-گروتھ آپریشنز مہنگے ہوتے ہیں اور جب تک ڈسک زیرو-انشیلائز نہیں ہوتی (جب تک کہ انسٹنٹ فائل انیشیلائزیشن فعال نہ ہو، جو لاگ فائلوں پر لاگو نہیں ہوتا) ٹرانزیکشن پروسیسنگ کو روک دیتے ہیں۔

مزید برآں، بار بار، چھوٹی آٹو-گروتھ (مثال کے طور پر، ایک وقت میں 10% یا 50MB بڑھنا) VLF فریگمنٹیشن کا باعث بنتی ہے۔ ہزاروں چھوٹی VLFs والا ٹرانزیکشن لاگ ڈیٹا بیس کے اسٹارٹ اپ کے اوقات، بیک اپ کی کارکردگی، اور ریپلیکیشن لیٹنسی کو بری طرح متاثر کرے گا۔

• لاگ کا پہلے سے سائز طے کریں: اپنے سب سے بڑے مینٹیننس آپریشنز (جیسے انڈیکس ری بلڈز) کا تجزیہ کریں اور LDF فائل کو پہلے سے سائز دیں تاکہ وہ بڑھے بغیر انہیں ایڈجسٹ کر سکے۔
• فکسڈ آٹو-گروتھ سیٹ کریں: آٹو-گروتھ کو فیصد سے فکسڈ سائز (مثال کے طور پر، 1GB یا 5GB) میں تبدیل کریں تاکہ یہ یقینی بنایا جا سکے کہ VLFs صحت مند سائز میں بنیں۔

آپ درج ذیل استفسار کا استعمال کرتے ہوئے اپنی VLF گنتی چیک کر سکتے ہیں (ایس کیو ایل سرور 2017+ کے لیے):

SELECT 
    db_name(database_id) AS DatabaseName,
    COUNT(vlf_sequence_number) AS VLF_Count
FROM sys.dm_db_log_info(DB_ID('YourDatabaseName'));

اگر آپ کی VLF گنتی 500 سے زیادہ ہے، تو پرسکون مدت کا انتظار کرنے، لاگ کو کم سے کم سائز تک شرنک کرنے، اور اسے دستی طور پر بڑے حصوں میں اس کے مطلوبہ سائز تک بڑھانے پر غور کریں۔

3. انڈیکس مینٹیننس آپریشنز کو بہتر بنائیں

انڈیکس ری بلڈز مکمل طور پر لاگڈ آپریشنز ہیں، یہاں تک کہ بلک-لاگڈ ریکوری ماڈل میں بھی (انڈیکس کی قسم پر منحصر ہے)۔ 500GB انڈیکس کو دوبارہ بنانے سے کم از کم 500GB ٹرانزیکشن لاگ ریکارڈز پیدا ہوں گے۔

مینٹیننس کے دوران لاگ کے پھیلاؤ کو کم کرنے کے لیے:
* انڈیکس کو دوبارہ بناتے وقت SORT_IN_TEMPDB = ON کا استعمال کریں۔ یہ چھانٹنے کے مرحلے کو TempDB پر منتقل کرتا ہے، جس سے صارف کے ڈیٹا بیس کے ٹرانزیکشن لاگ پر بوجھ کم ہوتا ہے۔
* جہاں ممکن ہو انڈیکس ری بلڈز سے انڈیکس ری آرگنائز پر سوئچ کریں، کیونکہ ری آرگنائزیشنز زیادہ لاگ-ایفیشینٹ ہوتی ہیں اور انہیں پورے آپریشن کو رول بیک کیے بغیر روکا جا سکتا ہے۔
* بڑے DELETE یا UPDATE آپریشنز کو بیچ کریں۔ ایک ٹرانزیکشن میں 10 ملین قطاریں حذف کرنے کے بجائے، انہیں 50,000 کے بیچ میں حذف کریں، کمٹ کریں اور بیچوں کے درمیان لاگ بیک اپ کو لاگ ٹرنکیٹ کرنے کی اجازت دیں۔

4. ہائی اویلیبلٹی اور ریپلیکیشن ٹوپولوجیز کی نگرانی کریں

AlwaysOn اویلیبلٹی گروپس میں، پرائمری ریپلیکا اپنے لاگ کو تب تک ٹرنکیٹ نہیں کر سکتا جب تک کہ لاگ ریکارڈز تمام سنکرونس اور اسنکرونس سیکنڈری ریپلیکاز پر محفوظ نہ ہو جائیں۔

اگر کوئی سیکنڈری ریپلیکا آف لائن ہو جاتا ہے، یا اگر نیٹ ورک بینڈوتھ پرائمری کی ٹرانزیکشن جنریشن کی رفتار کے ساتھ نہیں چل سکتی، تو پرائمری کی سینڈ کیو (send queue) بڑھ جائے گی، اور لاگ بھر جائے گا (AVAILABILITY_REPLICA ویٹ ٹائپ)۔

SQLServer:Replica > Log Send Queue پرفارمنس کاؤنٹر کے لیے مضبوط نگرانی نافذ کریں۔ اگر کوئی سیکنڈری ریپلیکا مستقل طور پر ضائع ہو جاتا ہے، تو آپ کو اسے اویلیبلٹی گروپ سے ہٹانا ہوگا یا پرائمری لاگ کو ٹرنکیٹ ہونے کی اجازت دینے کے لیے ڈیٹا کی نقل و حرکت کو معطل کرنا ہوگا۔

نتیجہ

بھرے ہوئے ٹرانزیکشن لاگ کا سامنا کرنا ڈیٹا بیس ایڈمنسٹریٹرز کے لیے ایک آزمائش ہے، لیکن اس کا مطلب طویل ڈاؤن ٹائم نہیں ہونا چاہیے۔ رائٹ-اوہیڈ لاگنگ اور VLFs کے میکانکس کو سمجھ کر، آپ sys.databases کا استعمال کرتے ہوئے تیزی سے بنیادی وجہ کی تشخیص کر سکتے ہیں اور فوری بحالی کی درست حکمت عملی لاگو کر سکتے ہیں۔

طویل مدتی استحکام ردعمل پر مبنی اصلاحات سے دور ہونے پر منحصر ہے۔ اپنی لاگ فائلوں کو پہلے سے سائز دینا، مینٹیننس کے معمولات کو بہتر بنانا، اور سخت، خودکار لاگ بیک اپ شیڈولز کو نافذ کرنے کے لیے CloudSave جیسے انٹرپرائز-گریڈ بیک اپ پلیٹ فارمز کا استعمال کرنا اس بات کو یقینی بنائے گا کہ آپ کے ٹرانزیکشن لاگز صحت مند، ٹرنکیٹڈ، اور ہائی-تھرو پٹ پروڈکشن ورک لوڈز کو سپورٹ کرنے کے لیے تیار رہیں۔