MSSQL ट्रांजेक्शन लॉग फुल: रोकथाम और त्वरित रिकवरी रणनीतियाँ

Microsoft SQL Server का प्रबंधन करने वाले डेटाबेस एडमिनिस्ट्रेटर (DBAs) और DevOps इंजीनियरों के लिए, एरर 9002: डेटाबेस ‘X’ के लिए ट्रांजेक्शन लॉग भर गया है, से अधिक चिंताजनक शायद ही कोई अलर्ट होता है। जब ट्रांजेक्शन लॉग भर जाता है और बढ़ नहीं पाता है, तो डेटाबेस प्रभावी रूप से ‘रीड-ओनली’ (केवल पढ़ने योग्य) हो जाता है। सभी INSERT, UPDATE, और DELETE ऑपरेशन रुक जाते हैं, एप्लिकेशन ट्रांजेक्शन विफल हो जाते हैं, और प्रोडक्शन पूरी तरह ठप हो जाता है।

SQL Server ट्रांजेक्शन लॉग की अंतर्निहित वास्तुकला को समझना, मूल कारण का सटीक निदान करना, और त्वरित रिकवरी प्रक्रियाओं को निष्पादित करना उच्च उपलब्धता (high availability) बनाए रखने के लिए महत्वपूर्ण कौशल हैं। यह व्यापक गाइड ट्रांजेक्शन लॉग के कामकाज, आपात स्थिति में भरे हुए लॉग को हल करने के तरीके, और इसे दोबारा होने से रोकने के लिए आर्किटेक्चरल सर्वोत्तम प्रथाओं का पता लगाती है।

SQL Server ट्रांजेक्शन लॉग आर्किटेक्चर को समझना

भरे हुए ट्रांजेक्शन लॉग को प्रभावी ढंग से ठीक करने के लिए, आपको पहले यह समझना होगा कि SQL Server डेटा को कैसे लिखता और प्रबंधित करता है।

राइट-अहेड लॉगिंग (WAL)

SQL Server एक राइट-अहेड लॉगिंग (WAL) प्रोटोकॉल का उपयोग करता है। जब भी कोई डेटा संशोधन होता है, तो परिवर्तन पहले मेमोरी में ट्रांजेक्शन लॉग में लिखा जाता है, फिर डेटाबेस फ़ाइलों (MDF/NDF) में वास्तविक डेटा पेजों को अपडेट करने से पहले डिस्क पर भौतिक लॉग फ़ाइल में फ्लश किया जाता है। यह ACID (एटॉमिकिटी, कंसिस्टेंसी, आइसोलेशन, ड्यूरेबिलिटी) अनुपालन की गारंटी देता है, यह सुनिश्चित करते हुए कि क्रैश होने की स्थिति में, SQL Server ट्रांजेक्शन को फिर से चला (roll forward) या पूर्ववत (roll back) कर सके।

वर्चुअल लॉग फ़ाइलें (VLFs) और सर्कुलर लॉगिंग

आंतरिक रूप से, भौतिक ट्रांजेक्शन लॉग फ़ाइल (LDF) को छोटे, तार्किक खंडों में विभाजित किया जाता है जिन्हें वर्चुअल लॉग फ़ाइलें (VLFs) कहा जाता है। ट्रांजेक्शन लॉग गोलाकार (circularly) रूप से संचालित होता है। जैसे-जैसे लॉग रिकॉर्ड लिखे जाते हैं, वे एक VLF को भरते हैं और अगले पर चले जाते हैं।

जब लॉग भौतिक फ़ाइल के अंत तक पहुँचता है, तो यह शुरुआत में वापस आने का प्रयास करता है। हालाँकि, यह केवल तभी VLF को ओवरराइट कर सकता है यदि वह VLF निष्क्रिय (inactive) के रूप में चिह्नित हो। यदि सभी VLF सक्रिय हैं (अर्थात उनमें ऐसे लॉग रिकॉर्ड हैं जिनकी SQL Server को अभी भी आवश्यकता है), तो लॉग आगे नहीं बढ़ सकता। यदि ऑटो-ग्रोथ सक्षम है और डिस्क स्थान उपलब्ध है, तो भौतिक फ़ाइल बढ़ जाती है। यदि डिस्क भरी हुई है या ऑटो-ग्रोथ प्रतिबंधित है, तो आपको एरर 9002 का सामना करना पड़ता है।

लॉग ट्रंकेशन बनाम लॉग श्रिंकिंग

एक आम गलतफहमी यह है कि लॉग को ट्रंकेट करने से भौतिक फ़ाइल का आकार कम हो जाता है।
* लॉग ट्रंकेशन: सक्रिय VLF को निष्क्रिय के रूप में चिह्नित करने की प्रक्रिया, जिससे स्थान पुन: उपयोग के लिए उपलब्ध हो जाता है। यह डिस्क पर LDF फ़ाइल के आकार को कम नहीं करता है।
* लॉग श्रिंकिंग: LDF फ़ाइल के आकार को भौतिक रूप से कम करने और ऑपरेटिंग सिस्टम को स्थान वापस करने की प्रक्रिया।

फुल रिकवरी मॉडल में, लॉग ट्रंकेशन केवल तब होता है जब ट्रांजेक्शन लॉग बैकअप सफलतापूर्वक पूरा हो जाता है (यह मानते हुए कि कोई अन्य प्रक्रिया लॉग को सक्रिय नहीं रख रही है)।

“ट्रांजेक्शन लॉग फुल” एरर (एरर 9002) का निदान

जब लॉग भर जाता है, तो आपका पहला कदम बिना सोचे-समझे डिस्क स्पेस जोड़ना या फ़ाइलों को सिकोड़ना नहीं होना चाहिए। आपको यह पहचानना होगा कि लॉग ट्रंकेट क्यों नहीं हो पा रहा है। SQL Server 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 उपलब्धता समूह में, एक सेकेंडरी रेप्लिका डिस्कनेक्ट हो गई है या बहुत धीरे सिंक हो रही है, जिससे प्राइमरी रेप्लिका को लॉग रिकॉर्ड तब तक रखने पड़ते हैं जब तक कि वे सेकेंडरी पर हार्डन न हो जाएँ।

त्वरित रिकवरी रणनीतियाँ: प्रोडक्शन में समस्या का समाधान

वापस मिले log_reuse_wait_desc के आधार पर, आपकी आपातकालीन प्रतिक्रिया अलग-अलग होगी। यहाँ सबसे सामान्य परिदृश्यों के लिए त्वरित रिकवरी रणनीतियाँ दी गई हैं।

परिदृश्य 1: गायब या विफल लॉग बैकअप (LOG_BACKUP)

यदि वेट टाइप LOG_BACKUP है, तो समाधान सीधा है: आपको ट्रांजेक्शन लॉग का बैकअप लेना होगा।

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

एक बार बैकअप पूरा हो जाने पर, निष्क्रिय VLF ट्रंकेट हो जाएंगे, और SQL Server सामान्य संचालन फिर से शुरू कर देगा। यदि आपकी बैकअप ड्राइव भर गई है, तो आपको अस्थायी नेटवर्क शेयर या नल डिवाइस पर बैकअप लेने की आवश्यकता हो सकती है (अत्यधिक अनुशंसित नहीं है जब तक कि डेटाबेस आसानी से पुन: उत्पन्न न हो सके, क्योंकि यह लॉग चेन को तोड़ देता है):

-- चेतावनी: यह लॉग चेन को तोड़ता है और पॉइंट-इन-टाइम रिकवरी से समझौता करता है।
-- केवल तभी उपयोग करें जब बिल्कुल आवश्यक हो और तुरंत बाद एक पूर्ण (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>;

नोट: एक बड़े ट्रांजेक्शन को किल करने से रोलबैक शुरू हो जाएगा, जिसमें काफी समय लग सकता है और यह अस्थायी रूप से अतिरिक्त लॉग गतिविधि उत्पन्न करेगा। रोलबैक के दौरान SQL Server सेवा को पुनरारंभ न करें, अन्यथा डेटाबेस पुनरारंभ होने पर रिकवरी मोड में चला जाएगा।

परिदृश्य 3: आपातकालीन स्थान आवंटन (डिस्क 100% भरी हुई है)

यदि LDF फ़ाइल ने पूरी ड्राइव का उपभोग कर लिया है, तो आप बैकअप भी नहीं चला सकते क्योंकि SQL Server को स्वयं बैकअप इवेंट को रिकॉर्ड करने के लिए थोड़ी मात्रा में लॉग स्पेस की आवश्यकता होती है। इस परिदृश्य में, आपको उपलब्ध स्थान वाली किसी अन्य ड्राइव पर एक सेकेंडरी लॉग फ़ाइल जोड़नी होगी।

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

यह तुरंत SQL Server को सांस लेने की जगह प्रदान करता है। एक बार डेटाबेस ऑनलाइन हो जाने के बाद, ट्रांजेक्शन लॉग बैकअप लें, सेकेंडरी लॉग फ़ाइल को खाली करें, और इसे हटा दें:

-- 1. लॉग को ट्रंकेट करने के लिए लॉग बैकअप लें
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. अस्थायी लॉग फ़ाइल को खाली करें
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. अस्थायी लॉग फ़ाइल को हटा दें
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

ट्रांजेक्शन लॉग की रोकथाम और प्रबंधन के लिए सर्वोत्तम प्रथाएं

प्रतिक्रियाशील (Reactive) समस्या निवारण तनावपूर्ण होता है और SLA को प्रभावित करता है। उद्यम डेटाबेस स्थिरता के लिए सक्रिय आर्किटेक्चरल और परिचालन सर्वोत्तम प्रथाओं को लागू करना आवश्यक है।

1. एक मजबूत, स्वचालित बैकअप रणनीति लागू करें

यदि कोई डेटाबेस फुल रिकवरी मॉडल में है, तो बार-बार ट्रांजेक्शन लॉग बैकअप अनिवार्य है। आपके रिकवरी पॉइंट ऑब्जेक्टिव (RPO) और ट्रांजेक्शन वॉल्यूम के आधार पर, लॉग बैकअप हर 5 से 15 मिनट में होने चाहिए।

CloudSave जैसे एंटरप्राइज़ बैकअप समाधान इस प्रक्रिया को काफी सरल बनाते हैं। VDI (वर्चुअल डिवाइस इंटरफेस) के माध्यम से सीधे SQL Server के साथ एकीकृत होकर, CloudSave DBAs को नीति-संचालित, उच्च-आवृत्ति वाले ट्रांजेक्शन लॉग बैकअप को कॉन्फ़िगर करने की अनुमति देता है। यह सुनिश्चित करता है कि लॉग लगातार ट्रंकेट होते रहें, सुरक्षित रूप से एन्क्रिप्टेड हों, और ऑफ-साइट या इम्यूटेबल क्लाउड स्टोरेज में संग्रहीत हों, जिससे जटिल कस्टम SQL एजेंट जॉब्स की आवश्यकता के बिना LOG_BACKUP वेट स्टेट को रोका जा सके।

2. ट्रांजेक्शन लॉग का सही आकार निर्धारित करें और VLF का प्रबंधन करें

अपने ट्रांजेक्शन लॉग आकार को प्रबंधित करने के लिए ऑटो-ग्रोथ पर भरोसा करना एक खतरनाक एंटी-पैटर्न है। ऑटो-ग्रोथ ऑपरेशन महंगे होते हैं और डिस्क के शून्य-प्रारंभीकरण (zero-initialized) होने तक ट्रांजेक्शन प्रोसेसिंग को रोक देते हैं (जब तक कि इंस्टेंट फ़ाइल इनिशियलाइज़ेशन सक्षम न हो, जो लॉग फ़ाइलों पर लागू नहीं होता है)।

इसके अलावा, बार-बार, छोटी ऑटो-ग्रोथ (जैसे, एक बार में 10% या 50MB बढ़ना) VLF फ्रैगमेंटेशन की ओर ले जाती है। हजारों छोटे VLF वाले ट्रांजेक्शन लॉग डेटाबेस स्टार्टअप समय, बैकअप प्रदर्शन और रेप्लिकेशन विलंबता को गंभीर रूप से खराब कर देंगे।

• लॉग का पूर्व-आकार (Pre-size) निर्धारित करें: अपने सबसे बड़े रखरखाव कार्यों (जैसे इंडेक्स रिबिल्ड) का विश्लेषण करें और LDF फ़ाइल को बिना बढ़े उन्हें समायोजित करने के लिए पहले से आकार दें।
• निश्चित ऑटो-ग्रोथ सेट करें: ऑटो-ग्रोथ को प्रतिशत से बदलकर एक निश्चित आकार (जैसे 1GB या 5GB) करें ताकि यह सुनिश्चित हो सके कि VLF स्वस्थ आकार में बनाए गए हैं।

आप निम्नलिखित क्वेरी (SQL Server 2017+ के लिए) का उपयोग करके अपनी VLF गणना की जांच कर सकते हैं:

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 प्रदर्शन काउंटर के लिए मजबूत निगरानी लागू करें। यदि कोई सेकेंडरी रेप्लिका स्थायी रूप से खो जाती है, तो आपको इसे उपलब्धता समूह से हटा देना चाहिए या प्राइमरी लॉग को ट्रंकेट होने देने के लिए डेटा मूवमेंट को निलंबित कर देना चाहिए।

निष्कर्ष

भरा हुआ ट्रांजेक्शन लॉग डेटाबेस एडमिनिस्ट्रेटर के लिए एक कठिन परीक्षा की तरह है, लेकिन इसका परिणाम लंबे समय तक डाउनटाइम के रूप में होना जरूरी नहीं है। राइट-अहेड लॉगिंग और VLF के कामकाज को समझकर, आप sys.databases का उपयोग करके मूल कारण का तुरंत निदान कर सकते हैं और सही त्वरित रिकवरी रणनीति लागू कर सकते हैं।

दीर्घकालिक स्थिरता प्रतिक्रियाशील सुधारों से दूर जाने पर निर्भर करती है। अपनी लॉग फ़ाइलों का पूर्व-आकार निर्धारित करना, रखरखाव दिनचर्या को अनुकूलित करना, और सख्त, स्वचालित लॉग बैकअप शेड्यूल लागू करने के लिए CloudSave जैसे एंटरप्राइज़-ग्रेड बैकअप प्लेटफ़ॉर्म का उपयोग करना यह सुनिश्चित करेगा कि आपके ट्रांजेक्शन लॉग स्वस्थ, ट्रंकेटेड और उच्च-थ्रूपुट प्रोडक्शन वर्कलोड का समर्थन करने के लिए तैयार रहें।