डेटाबेस प्रशासक (DBAs) र Microsoft SQL Server व्यवस्थापन गर्ने DevOps इन्जिनियरहरूका लागि, त्रुटि ९००२ (Error 9002): डेटाबेस ‘X’ को ट्रान्ज्याक्सन लग भरिएको छ (The transaction log for database ‘X’ is full) जत्तिको चिन्ताजनक चेतावनी अरू कमै हुन्छन्। जब ट्रान्ज्याक्सन लग भरिन्छ र बढ्न सक्दैन, डेटाबेस प्रभावकारी रूपमा ‘रिड-ओन्ली’ (read-only) बन्छ। सबै INSERT, UPDATE, र DELETE कार्यहरू रोकिन्छन्, एप्लिकेसन ट्रान्ज्याक्सनहरू असफल हुन्छन्, र उत्पादन प्रक्रिया ठप्प हुन्छ।
SQL Server ट्रान्ज्याक्सन लगको आधारभूत वास्तुकला बुझ्नु, मूल कारणको सही निदान गर्नु, र द्रुत रिकभरी प्रक्रियाहरू कार्यान्वयन गर्नु उच्च उपलब्धता (high availability) कायम राख्नका लागि महत्त्वपूर्ण सीपहरू हुन्। यो विस्तृत गाइडले ट्रान्ज्याक्सन लगको कार्यविधि, आपतकालिन अवस्थामा भरिएको लगलाई कसरी समाधान गर्ने, र यस्तो समस्या फेरि हुन नदिन अपनाउनुपर्ने उत्तम अभ्यासहरूका बारेमा चर्चा गर्दछ।
SQL Server ट्रान्ज्याक्सन लग वास्तुकला बुझ्दै
भरिएको ट्रान्ज्याक्सन लगलाई प्रभावकारी रूपमा समाधान गर्न, तपाईंले पहिले SQL Server ले कसरी डेटा लेख्छ र व्यवस्थापन गर्छ भन्ने कुरा बुझ्नुपर्छ।
राइट-अहेड लगिङ (Write-Ahead Logging – WAL)
SQL Server ले राइट-अहेड लगिङ (WAL) प्रोटोकल प्रयोग गर्छ। जब कुनै डेटा परिमार्जन हुन्छ, परिवर्तन पहिले मेमोरीमा रहेको ट्रान्ज्याक्सन लगमा लेखिन्छ, र त्यसपछि डेटाबेस फाइलहरू (MDF/NDF) मा वास्तविक डेटा पृष्ठहरू अपडेट हुनुभन्दा अघि डिस्कमा रहेको भौतिक लग फाइलमा फ्लश गरिन्छ। यसले ACID (Atomicity, Consistency, Isolation, Durability) अनुपालनको ग्यारेन्टी गर्छ, जसले गर्दा क्र्यास भएको अवस्थामा SQL Server ले ट्रान्ज्याक्सनहरूलाई पुन: प्ले (roll forward) वा पूर्ववत (roll back) गर्न सक्छ।
भर्चुअल लग फाइलहरू (VLFs) र सर्कुलर लगिङ
आन्तरिक रूपमा, भौतिक ट्रान्ज्याक्सन लग फाइल (LDF) लाई साना, तार्किक खण्डहरूमा विभाजन गरिएको हुन्छ जसलाई भर्चुअल लग फाइलहरू (VLFs) भनिन्छ। ट्रान्ज्याक्सन लग गोलाकार रूपमा सञ्चालन हुन्छ। लग रेकर्डहरू लेखिँदा, तिनीहरूले एउटा VLF भर्छन् र अर्कोमा सर्छन्।
जब लग भौतिक फाइलको अन्त्यमा पुग्छ, यसले सुरुमा फर्कने प्रयास गर्छ। यद्यपि, यसले VLF लाई ओभरराइट गर्न सक्छ यदि त्यो VLF निष्क्रिय (inactive) को रूपमा चिन्ह लगाइएको छ भने। यदि सबै VLFs सक्रिय छन् (अर्थात तिनीहरूमा अझै पनि SQL Server लाई आवश्यक पर्ने लग रेकर्डहरू छन्), लग फर्कन सक्दैन। यदि अटो-ग्रोथ (auto-growth) सक्षम छ र डिस्क स्पेस उपलब्ध छ भने, भौतिक फाइल बढ्छ। यदि डिस्क भरिएको छ वा अटो-ग्रोथ प्रतिबन्धित छ भने, तपाईंले त्रुटि ९००२ को सामना गर्नुहुन्छ।
लग ट्रन्केसन (Truncation) बनाम लग श्रिङ्किङ (Shrinking)
एउटा सामान्य गलत धारणा यो छ कि लग ट्रन्केट गर्दा भौतिक फाइलको आकार घट्छ।
* लग ट्रन्केसन: सक्रिय VLFs लाई निष्क्रियको रूपमा चिन्ह लगाउने प्रक्रिया, जसले गर्दा ठाउँ पुन: प्रयोगको लागि उपलब्ध हुन्छ। यसले डिस्कमा रहेको LDF फाइलको आकार घटाउँदैन।
* लग श्रिङ्किङ: LDF फाइलको आकार भौतिक रूपमा घटाउने र अपरेटिङ सिस्टमलाई ठाउँ फिर्ता गर्ने प्रक्रिया।
Full Recovery मोडमा, लग ट्रन्केसन केवल तब हुन्छ जब ट्रान्ज्याक्सन लग ब्याकअप सफलतापूर्वक पूरा हुन्छ (यदि अन्य कुनै प्रक्रियाले लगलाई सक्रिय राखेको छैन भने)।
“ट्रान्ज्याक्सन लग फुल” त्रुटि (त्रुटि ९००२) को निदान
जब लग भरिन्छ, तपाईंको पहिलो कदम अन्धाधुन्ध डिस्क स्पेस थप्ने वा फाइलहरू श्रिङ्क गर्ने हुनुहुँदैन। तपाईंले लग किन ट्रन्केट हुन सक्दैन भन्ने कुरा पहिचान गर्नुपर्छ। SQL Server ले sys.databases क्याटलग भ्यू मार्फत लग पुन: प्रयोग हुनबाट के कुराले रोकिरहेको छ भन्ने कुरा बताउने संयन्त्र प्रदान गर्दछ।
बोटलनेक (bottleneck) पहिचान गर्न निम्न 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 अवस्थाहरू
- LOG_BACKUP: डेटाबेस Full वा Bulk-Logged रिकभरी मोडमा छ, र ट्रान्ज्याक्सन लग ब्याकअप हालसालै लिइएको छैन। यो सबैभन्दा सामान्य कारण हो।
- ACTIVE_TRANSACTION: लामो समयसम्म चलिरहेको ट्रान्ज्याक्सन (जस्तै, ठूलो इन्डेक्स रिबिल्ड वा बिर्सिएको अनकमिटेड ट्रान्ज्याक्सन) ले लगलाई सक्रिय राखिरहेको छ।
- REPLICATION / CDC: ट्रान्ज्याक्सनल रिप्लिकेसन वा चेन्ज डेटा क्याप्चर (CDC) सक्षम छ, र लग रिडर एजेन्टले अझै ट्रान्ज्याक्सनहरू प्रशोधन गरेको छैन।
- AVAILABILITY_REPLICA: AlwaysOn उपलब्धता समूहमा, सेकेन्डरी रेप्लिका विच्छेद भएको छ वा धेरै ढिलो सिङ्क्रोनाइज भइरहेको छ, जसले गर्दा प्राइमरी रेप्लिकाले लग रेकर्डहरू सेकेन्डरीमा सुरक्षित नभएसम्म राख्न बाध्य हुन्छ।
द्रुत रिकभरी रणनीतिहरू: उत्पादनमा समस्या समाधान गर्ने
log_reuse_wait_desc को आधारमा, तपाईंको आपतकालीन प्रतिक्रिया फरक हुनेछ। यहाँ सबैभन्दा सामान्य परिदृश्यहरूका लागि द्रुत रिकभरी रणनीतिहरू छन्।
परिदृश्य १: हराएको वा असफल लग ब्याकअपहरू (LOG_BACKUP)
यदि वेट टाइप LOG_BACKUP छ भने, समाधान सरल छ: तपाईंले ट्रान्ज्याक्सन लग ब्याकअप गर्नुपर्छ।
BACKUP LOG [YourDatabaseName]
TO DISK = 'N:BackupsYourDatabaseName_EmergencyLog.trn'
WITH COMPRESSION, STATS = 10;
एकपटक ब्याकअप पूरा भएपछि, निष्क्रिय VLFs ट्रन्केट हुनेछन्, र SQL Server ले सामान्य सञ्चालन पुन: सुरु गर्नेछ। यदि तपाईंको ब्याकअप ड्राइभ भरिएको छ भने, तपाईंले अस्थायी नेटवर्क सेयर वा नल डिभाइसमा ब्याकअप गर्नुपर्ने हुन सक्छ (डेटाबेस सजिलै पुन: उत्पादन गर्न सकिने नभएसम्म यो अत्यधिक निरुत्साहित गरिन्छ, किनकि यसले लग चेन तोड्छ):
-- चेतावनी: यसले लग चेन तोड्छ र पोइन्ट-इन-टाइम रिकभरीमा सम्झौता गर्छ।
-- यदि एकदमै आवश्यक छ भने मात्र प्रयोग गर्नुहोस् र तुरुन्तै FULL ब्याकअप लिनुहोस्।
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';
परिदृश्य २: लामो समयसम्म चलिरहेको सक्रिय ट्रान्ज्याक्सनहरू (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 सेवा पुन: सुरु नगर्नुहोस्, अन्यथा डेटाबेस पुन: सुरु भएपछि रिकभरी मोडमा प्रवेश गर्नेछ।
परिदृश्य ३: आपतकालीन स्पेस आवंटन (डिस्क १००% भरिएको छ)
यदि 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 लाई राहत दिन्छ। एकपटक डेटाबेस अनलाइन भएपछि, ट्रान्ज्याक्सन लग ब्याकअप लिनुहोस्, सेकेन्डरी लग फाइल खाली गर्नुहोस्, र यसलाई हटाउनुहोस्:
-- १. लग ट्रन्केट गर्न लग ब्याकअप लिनुहोस्
BACKUP LOG [YourDatabaseName] TO DISK = '...';
-- २. अस्थायी लग फाइल खाली गर्नुहोस्
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);
-- ३. अस्थायी लग फाइल हटाउनुहोस्
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];
ट्रान्ज्याक्सन लग रोकथाम र व्यवस्थापनका लागि उत्तम अभ्यासहरू
प्रतिक्रियात्मक समस्या निवारण तनावपूर्ण हुन्छ र SLAs लाई असर गर्छ। उद्यम डेटाबेस स्थिरताको लागि सक्रिय वास्तुकला र परिचालन उत्तम अभ्यासहरू लागू गर्नु आवश्यक छ।
१. एक बलियो, स्वचालित ब्याकअप रणनीति लागू गर्नुहोस्
यदि डेटाबेस Full रिकभरी मोडमा छ भने, बारम्बार ट्रान्ज्याक्सन लग ब्याकअप अनिवार्य छ। तपाईंको रिकभरी पोइन्ट अब्जेक्टिभ (RPO) र ट्रान्ज्याक्सन भोल्युमको आधारमा, लग ब्याकअपहरू हरेक ५ देखि १५ मिनेटमा हुनुपर्छ।
CloudSave जस्ता इन्टरप्राइज ब्याकअप समाधानहरूले यो प्रक्रियालाई धेरै सरल बनाउँछन्। VDI (भर्चुअल डिभाइस इन्टरफेस) मार्फत SQL Server सँग सिधै एकीकृत भएर, CloudSave ले DBAs लाई नीति-संचालित, उच्च-फ्रिक्वेन्सी ट्रान्ज्याक्सन लग ब्याकअपहरू कन्फिगर गर्न अनुमति दिन्छ। यसले लगहरू निरन्तर ट्रन्केट हुने, सुरक्षित रूपमा इन्क्रिप्ट हुने, र अफ-साइट वा इम्युटेबल क्लाउड स्टोरेजमा भण्डारण हुने सुनिश्चित गर्दछ, जसले जटिल कस्टम SQL एजेन्ट कार्यहरूको आवश्यकता बिना नै LOG_BACKUP वेट स्टेटलाई रोक्छ।
२. ट्रान्ज्याक्सन लगको आकार मिलाउनुहोस् र VLFs व्यवस्थापन गर्नुहोस्
तपाईंको ट्रान्ज्याक्सन लगको आकार व्यवस्थापन गर्न अटो-ग्रोथमा भर पर्नु एक खतरनाक एन्टी-प्याटर्न हो। अटो-ग्रोथ कार्यहरू महँगो हुन्छन् र डिस्क जिरो-इनिसियलाइज हुँदा ट्रान्ज्याक्सन प्रशोधनलाई रोक्छन् (जबसम्म इन्स्ट्यान्ट फाइल इनिसियलाइजेसन सक्षम छैन, जुन लग फाइलहरूमा लागू हुँदैन)।
यसबाहेक, बारम्बार, साना अटो-ग्रोथहरू (जस्तै, एक पटकमा १०% वा ५०MB ले बढ्नु) ले VLF फ्र्याग्मेन्टेसन निम्त्याउँछ। हजारौं साना VLFs भएको ट्रान्ज्याक्सन लगले डेटाबेस स्टार्टअप समय, ब्याकअप प्रदर्शन, र रिप्लिकेसन विलम्बतालाई गम्भीर रूपमा घटाउँछ।
- लगको आकार पहिले नै निर्धारण गर्नुहोस्: तपाईंको सबैभन्दा ठूला मर्मत कार्यहरू (जस्तै इन्डेक्स रिबिल्ड) को विश्लेषण गर्नुहोस् र LDF फाइललाई बढ्न नदिई समायोजन गर्न पहिले नै आकार निर्धारण गर्नुहोस्।
- निश्चित अटो-ग्रोथ सेट गर्नुहोस्: अटो-ग्रोथलाई प्रतिशतबाट निश्चित आकारमा (जस्तै १GB वा ५GB) परिवर्तन गर्नुहोस् ताकि VLFs स्वस्थ आकारमा सिर्जना हुने सुनिश्चित होस्।
तपाईं निम्न क्वेरी प्रयोग गरेर आफ्नो VLF गणना जाँच गर्न सक्नुहुन्छ (SQL Server २०१७+ को लागि):
SELECT
db_name(database_id) AS DatabaseName,
COUNT(vlf_sequence_number) AS VLF_Count
FROM sys.dm_db_log_info(DB_ID('YourDatabaseName'));
यदि तपाईंको VLF गणना ५०० भन्दा बढी छ भने, शान्त अवधिको प्रतीक्षा गर्ने, लगलाई न्यूनतम आकारमा श्रिङ्क गर्ने, र यसलाई ठूला टुक्राहरूमा आवश्यक आकारमा म्यानुअल रूपमा बढाउने विचार गर्नुहोस्।
३. इन्डेक्स मर्मत कार्यहरू अप्टिमाइज गर्नुहोस्
इन्डेक्स रिबिल्डहरू पूर्ण रूपमा लग गरिएका कार्यहरू हुन्, Bulk-Logged रिकभरी मोडमा पनि (इन्डेक्स प्रकारको आधारमा)। ५००GB को इन्डेक्स रिबिल्ड गर्दा कम्तिमा ५००GB ट्रान्ज्याक्सन लग रेकर्डहरू उत्पन्न हुनेछन्।
मर्मतको समयमा लगको आकार बढ्न नदिन:
* इन्डेक्स रिबिल्ड गर्दा SORT_IN_TEMPDB = ON प्रयोग गर्नुहोस्। यसले सर्टिङ चरणलाई TempDB मा सार्छ, जसले प्रयोगकर्ता डेटाबेसको ट्रान्ज्याक्सन लगमा भार कम गर्छ।
* जहाँ सम्भव छ इन्डेक्स रिबिल्ड बाट इन्डेक्स रिअर्गनाइज मा स्विच गर्नुहोस्, किनकि रिअर्गनाइजेसनहरू बढी लग-कुशल हुन्छन् र सम्पूर्ण कार्यलाई रोल ब्याक नगरी अवरोध गर्न सकिन्छ।
* ठूला DELETE वा UPDATE कार्यहरूलाई ब्याच गर्नुहोस्। १० मिलियन पङ्क्तिहरू एकै ट्रान्ज्याक्सनमा मेटाउनुको सट्टा, तिनीहरूलाई ५०,००० को ब्याचमा मेटाउनुहोस्, कमिट गर्नुहोस् र ब्याचहरू बीचमा लग ब्याकअपहरूलाई लग ट्रन्केट गर्न अनुमति दिनुहोस्।
४. उच्च उपलब्धता र रिप्लिकेसन टोपोलोजीहरू निगरानी गर्नुहोस्
AlwaysOn उपलब्धता समूहहरूमा, प्राइमरी रेप्लिकाले आफ्नो लग तबसम्म ट्रन्केट गर्न सक्दैन जबसम्म लग रेकर्डहरू सबै सिङ्क्रोनस र असिन्क्रोनस सेकेन्डरी रेप्लिकामा सुरक्षित हुँदैनन्।
यदि सेकेन्डरी रेप्लिका अफलाइन भयो भने, वा नेटवर्क ब्यान्डविथले प्राइमरीको ट्रान्ज्याक्सन उत्पादन दरसँग तालमेल राख्न सकेन भने, प्राइमरीको सेन्ड क्यू (send queue) बढ्नेछ, र लग भरिनेछ (AVAILABILITY_REPLICA वेट टाइप)।
SQLServer:Replica > Log Send Queue प्रदर्शन काउन्टरको लागि बलियो निगरानी लागू गर्नुहोस्। यदि सेकेन्डरी रेप्लिका स्थायी रूपमा हरायो भने, तपाईंले यसलाई उपलब्धता समूहबाट हटाउनुपर्छ वा प्राइमरी लगलाई ट्रन्केट गर्न अनुमति दिन डेटा आन्दोलनलाई निलम्बन गर्नुपर्छ।
निष्कर्ष
भरिएको ट्रान्ज्याक्सन लगको सामना गर्नु डेटाबेस प्रशासकहरूको लागि एक सामान्य अनुभव हो, तर यसको मतलब लामो समयसम्म डाउनटाइम हुनुपर्छ भन्ने छैन। राइट-अहेड लगिङ र VLFs को कार्यविधि बुझेर, तपाईंले sys.databases प्रयोग गरेर मूल कारणको द्रुत निदान गर्न सक्नुहुन्छ र सही द्रुत रिकभरी रणनीति लागू गर्न सक्नुहुन्छ।
दीर्घकालीन स्थिरता प्रतिक्रियात्मक समाधानहरूबाट टाढा जानमा निर्भर गर्दछ। तपाईंको लग फाइलहरूको आकार पहिले नै निर्धारण गर्ने, मर्मत कार्यहरू अप्टिमाइज गर्ने, र कडा, स्वचालित लग ब्याकअप तालिकाहरू लागू गर्न CloudSave जस्ता इन्टरप्राइज-ग्रेड ब्याकअप प्लेटफर्महरू प्रयोग गर्नाले तपाईंको ट्रान्ज्याक्सन लगहरू स्वस्थ, ट्रन्केटेड, र उच्च-थ्रूपुट उत्पादन कार्यभारहरूलाई समर्थन गर्न तयार रहने सुनिश्चित गर्नेछ।
