लेन-देन लॉग अंतरिक्ष से बाहर क्यों बढ़ता है या चलता रहता है?


207

यह अधिकांश मंचों और पूरे वेब पर एक सामान्य प्रश्न लगता है, यह यहाँ कई स्वरूपों में पूछा जाता है जो आमतौर पर इस तरह से ध्वनि करते हैं:

SQL सर्वर में -

  • लेन-देन लॉग बढ़ने के कुछ कारण क्या हैं?
  • मेरी लॉग फ़ाइल इतनी बड़ी क्यों है?
  • इस समस्या को होने से रोकने के लिए कुछ तरीके क्या हैं?
  • जब मैं अपने आप को अंतर्निहित कारण के साथ ट्रैक पर लाता हूं और अपनी लेन-देन लॉग फ़ाइल को एक स्वस्थ आकार में रखना चाहता हूं तो मैं क्या करूं?
  0

वास्तव में संक्षिप्त उत्तर है: डेटाबेस को ** सिंपल ** मोड (न कि ** फुल ** मोड) में डालें।यदि आप अपने पूर्ण रात्रिकालीन बैकअप के बीच दिन में कई लेन-देन लॉग बैकअप नहीं कर रहे हैं: आपको ** पूर्ण ** मोड की आवश्यकता नहीं है। 07 dec. 162016-12-07 14:48:40

  0

@IanBoyd - निश्चित रूप से इसका सबसे सरल उत्तर है।कुंजी, हालांकि, इसका मतलब है कि हो रही है।मैं जवाब में उस पर मारा।अफसोस की बात है कि बहुत से लोग या तो इसे कभी भी संबोधित नहीं करते हैं या बस बिना समझे सरलता से आगे बढ़ते हैं।मैं अपने उत्तर को सरल मोड पर हिट करने के लिए पहले थोड़ा संपादित करूँगा, हालाँकि .. 07 dec. 162016-12-07 14:53:55

270

एक छोटा उत्तर:

आपके पास शायद या तो एक लंबे समय तक चलने वाला लेनदेन चल रहा है (इंडेक्स मेंटेनेंस? बिग बैच डिलीट या अपडेट?) या आप "डिफ़ॉल्ट" में हैं (डिफ़ॉल्ट रूप से इसका मतलब नीचे है) रिकवरी मोडFullऔर ए नहीं लिया हैलॉग बैकअप(या उन्हें अक्सर पर्याप्त नहीं ले रहे हैं)।

यदि यह एक पुनर्प्राप्ति मॉडल समस्या है, तो सरल उत्तर स्विच किया जा सकता हैSimpleपुनर्प्राप्ति मोड यदि आपको समय पर पुनर्प्राप्ति और नियमित लॉग बैकअप में बिंदु की आवश्यकता नहीं है।कई लोग, हालांकि, वसूली मॉडल को समझने के बिना अपना जवाब देते हैं।यह समझने के लिए पढ़ें कि यह क्यों मायने रखता है और फिर तय करें कि आप क्या करते हैं।आप बस लॉग बैकअप लेना शुरू कर सकते हैं और अंदर रह सकते हैंFullवसूली।

अन्य कारण हो सकते हैं लेकिन ये सबसे आम हैं।यह उत्तर सबसे आम दो कारणों में गोता लगाने लगता है और आपको क्यों और कैसे पीछे कारणों के साथ-साथ कुछ अन्य कारणों की पड़ताल के बारे में कुछ जानकारी देता है।


एक लंबा जवाब:बढ़ते रहने के लिए लॉग क्या कारण हो सकते हैं?कई कारण हैं, लेकिन आमतौर पर ये कारण निम्न दो पैटर्न के होते हैं: पुनर्प्राप्ति मॉडल के बारे में गलतफहमी है या लंबे समय तक चलने वाले लेनदेन हैं।जानकारी के लिए आगे पढ़ें।

शीर्ष कारण 1/2: रिकवरी मॉडल को समझना नहीं

(में रहनापूर्ण पुनर्प्राप्ति मोडऔर नहीं ले रहा हैलॉग इन करें- यह सबसे आम कारण है - इस मुद्दे का अनुभव करने वाले अधिकांश लोग हैं।)

हालांकि यह उत्तर SQL सर्वर पुनर्प्राप्ति मॉडल में एक गहरा गोता नहीं है, लेकिन पुनर्प्राप्ति मॉडल का विषय इस समस्या के लिए महत्वपूर्ण है।

SQL सर्वर में, तीन हैंrecovery models:

  • Full,
  • Bulk-Loggedतथा
  • Simple

हम अनदेखा कर देंगेBulk-Loggedअभी के लिए हम यह कहेंगे कि यह एक हाइब्रिड मॉडल है और अधिकांश लोग जो इस मॉडल में हैं, एक कारण और रिकवरी मॉडल को समझते हैं।

जिन दो की हम परवाह करते हैं और उनकी उलझनें इस मुद्दे वाले अधिकांश मामलों का कारण हैंSimpleतथाFull

प्रवेश: सामान्य में वसूली

इससे पहले कि हम पुनर्प्राप्ति मॉडल के बारे में बात करें: चलो सामान्य रूप से वसूली के बारे में बात करते हैं।यदि आप इस विषय के साथ और भी गहरे जाना चाहते हैं, तो बस पढ़ेंPaul Randal's blogऔर उस पर जितने चाहें उतने पोस्ट।इस प्रश्न के लिए, हालांकि:

  1. क्रैश/रिस्टार्ट रिकवरी
    लेन-देन लॉग फ़ाइल का एक उद्देश्य के लिए हैदुर्घटना/पुनः आरंभ पुनर्प्राप्ति।किसी कार्य को आगे बढ़ाने और रोल बैक करने के लिए जो क्रैश या रीस्टार्ट होने से पहले या (आगे चलकर/फिर से रोलिंग) किया गया था और जो काम शुरू किया गया था, लेकिन क्रैश या रीस्टार्ट (रोलिंग बैक/पूर्ववत) के बाद समाप्त नहीं हुआ था।यह लेन-देन लॉग का काम है कि एक लेनदेन शुरू हुआ लेकिन कभी भी समाप्त नहीं हुआ (लुढ़का हुआ वापस लौटा या क्रैश/पुनरारंभ लेनदेन होने से पहले हुआ)।उस स्थिति में यह कहना लॉग का काम है"अरे .. यह वास्तव में कभी खत्म नहीं हुआ, चलो इसे वापस रोल करें"वसूली के दौरान।यह देखना भी लॉग का काम है कि आपने कुछ खत्म किया है और आपके क्लाइंट एप्लिकेशन को बताया गया था कि यह समाप्त हो गया है (भले ही यह आपकी डेटा फ़ाइल के लिए अभी तक कठोर नहीं हुआ था) और कहें"अरे .. यह वास्तव में हुआ, चलो इसे आगे रोल करें, चलो इसे ऐसे बनाते हैं जैसे अनुप्रयोगों को लगता है कि यह था"पुनः आरंभ करने के बाद।अब और भी है लेकिन यही मुख्य उद्देश्य है।

  2. प्वाइंट इन टाइम रिकवरी
    लेन-देन लॉग फ़ाइल का दूसरा उद्देश्य हमें पुनर्प्राप्त करने की क्षमता देने में सक्षम होना हैकोई निश्चित समयएक डेटाबेस में "उफ़" के कारण या डेटाबेस की डेटा और/या लॉग फ़ाइलों को शामिल करने में एक हार्डवेयर विफलता की स्थिति में एक रिकवरी बिंदु की गारंटी देने के लिए।यदि इस लेन-देन लॉग में लेन-देन का रिकॉर्ड होता है जो पुनर्प्राप्ति के लिए शुरू और समाप्त हो गया है, तो SQL सर्वर इस डेटाबेस का उपयोग करने के लिए इस जानकारी का उपयोग कर सकता है, जहां यह समस्या होने से पहले था।लेकिन यह हमेशा हमारे लिए एक उपलब्ध विकल्प नहीं है।उस काम के लिए हमें अपने डेटाबेस को सही में रखना होगावसूली मॉडल, और हमें लेना होगालॉग बैकअप

रिकवरी मॉडल

वसूली मॉडल पर:

  • सरल रिकवरी मॉडल
    तो उपरोक्त परिचय के साथ, इसके बारे में बात करना सबसे आसान हैSimple Recoveryपहले मॉडल।इस मॉडल में, आप SQL सर्वर को बता रहे हैं:"मैं दुर्घटना और पुनः प्राप्ति के लिए अपने लेनदेन लॉग फ़ाइल का उपयोग करके आपके साथ ठीक हूं ..."(आप वास्तव में वहाँ कोई विकल्प नहीं है। देखोACID propertiesऔर यह समझ में जल्दी आना चाहिए।)"... लेकिन एक बार जब आपको उस क्रैश/पुनर्प्राप्ति पुनर्प्राप्ति उद्देश्य के लिए इसकी आवश्यकता नहीं होती है, तो आगे बढ़ें और लॉग फ़ाइल का पुन: उपयोग करें।"

    SQL सर्वर सिंपल रिकवरी में इस रिक्वेस्ट को सुनता है और यह केवल उन जानकारियों को रखता है जिन्हें इसे क्रैश/रिस्टार्ट रिकवरी करने की आवश्यकता होती है।एक बार SQL सर्वर सुनिश्चित हो जाता है कि यह पुनर्प्राप्त हो सकता है क्योंकि डेटा को डेटा फ़ाइल (अधिक या कम) तक कठोर किया जाता है, जिस डेटा को हार्ड किया गया है वह लॉग में अब आवश्यक नहीं है और ट्रंकेशन के लिए चिह्नित है - जिसका अर्थ है कि इसका पुनः उपयोग किया जाता है।

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

सिंपल से फुल पर स्विच करने पर एक गोटचा होता है।

यहां नियम और अपवाद हैं।हम नीचे लंबे समय तक चलने वाले लेन-देन के बारे में गहराई से बात करेंगे।

लेकिन पूर्ण पुनर्प्राप्ति मोड को ध्यान में रखने के लिए एक चेतावनी यह है: यदि आप बस में स्विच करते हैंFull Recoveryमोड, लेकिन एक प्रारंभिक पूर्ण बैकअप, SQL सर्वर कभी नहीं ले जाएगानहींअपने अनुरोध का सम्मान करेंFull Recoveryआदर्श।आपका लेन-देन लॉग जारी रहेगा जैसा कि इसमें हैSimpleजब तक आप पूर्ण पुनर्प्राप्ति मॉडल पर नहीं जाते हैं और अपना पहला लेते हैंFull Backup

लॉग बैकअप के बिना पूर्ण पुनर्प्राप्ति मॉडल खराब है।

तो, यह अनियंत्रित लॉग वृद्धि का सबसे आम कारण है?उत्तर: बिना लॉग बैकअप के पूर्ण पुनर्प्राप्ति मोड में होना।

ऐसा होता हैसबलोगों का समय।

यह इतनी सामान्य गलती क्यों है?

हर समय ऐसा क्यों होता है?क्योंकि प्रत्येक नए डेटाबेस को मॉडल डेटाबेस को देखकर अपनी प्रारंभिक पुनर्प्राप्ति मॉडल सेटिंग मिलती है।

मॉडल की प्रारंभिक पुनर्प्राप्ति मॉडल सेटिंग हमेशा होती हैFull Recovery Model- जब तक और जब तक कि कोई बदल न जाए।तो आप कह सकते हैं "डिफ़ॉल्ट रिकवरी मॉडल" हैFull।बहुत से लोगों को इसके बारे में जानकारी नहीं है और उनके डेटाबेस में चल रहा हैFull Recovery Modelकोई लॉग बैकअप के साथ, और इसलिए लेन-देन लॉग फ़ाइल आवश्यकता से बहुत बड़ी है।यही कारण है कि चूक को बदलना महत्वपूर्ण है जब वे आपके संगठन और इसकी जरूरतों के लिए काम नहीं करते हैं)

बहुत कम लॉग बैकअप के साथ पूर्ण पुनर्प्राप्ति मॉडल खराब है।

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

मुझे कैसे पता चलेगा कि मुझे किस लॉग बैकअप आवृति की आवश्यकता है?

आपको दो चीजों को ध्यान में रखते हुए अपनी लॉग बैकअप आवृत्ति पर विचार करने की आवश्यकता है:

  1. रिकवरी की जरूरत- यह उम्मीद पहले होनी चाहिए।इस घटना में कि आपके लेन-देन लॉग हाउस में ड्राइव खराब हो जाती है या आपको गंभीर भ्रष्टाचार मिलता है जो आपके लॉग बैकअप को प्रभावित करता है, कितना डेटा खो सकता है?यदि वह संख्या 10-15 मिनट से अधिक नहीं है, तो आपको चर्चा के अंत में हर 10-15 मिनट में लॉग बैकअप लेना होगा।
  2. लॉग ग्रोथ- यदि आपका संगठन अधिक डेटा खोने के लिए ठीक है, तो उस दिन आसानी से फिर से बनाने की क्षमता के कारण, आप 15 मिनट से कम अक्सर लॉग बैकअप के लिए ठीक हो सकते हैं।हो सकता है कि आपका संगठन हर 4 घंटे में ठीक हो।लेकिन आपको यह देखना होगा कि आप 4 घंटे में कितने लेन-देन करते हैं।क्या लॉग को उन चार घंटों में बढ़ते रहने की अनुमति देगा जो लॉग फ़ाइल को बहुत बड़ा बनाते हैं?क्या इसका मतलब है कि आपके लॉग बैकअप में बहुत लंबा समय लगेगा?

शीर्ष कारण 2/2: लंबे समय तक चलने वाले लेन-देन

("मेरा पुनर्प्राप्ति मॉडल ठीक है! लॉग अभी भी बढ़ रहा है!")

यह अनियंत्रित और अनियंत्रित लॉग वृद्धि का कारण भी हो सकता है।कोई फर्क नहीं पड़ता वसूली मॉडल, लेकिन यह अक्सर के रूप में आता है"लेकिन मैं साधारण रिकवरी मॉडल में हूं - मेरा लॉग अभी भी क्यों बढ़ रहा है ?!"

यहां कारण सरल है: यदि एसक्यूएल इस लेनदेन लॉग का उपयोग पुनर्प्राप्ति उद्देश्यों के लिए कर रहा है जैसा कि मैंने ऊपर वर्णित किया है, तो इसे एक लेनदेन की शुरुआत में वापस देखना होगा।

यदि आपके पास एक ऐसा लेनदेन है जिसमें बहुत समय लगता है या बहुत सारे बदलाव होते हैं, तो लॉग किसी भी बदलाव के लिए चेकपॉइंट पर नहीं काट सकता है जो अभी भी खुले लेनदेन में हैं या जो उस लेनदेन के शुरू होने के बाद से शुरू हुए हैं।

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

मैं इन लंबे समय तक चलने वाले लेनदेन के बारे में क्या कर सकता हूं?

आप अपने आप को यहाँ द्वारा बचा सकते हैं:

  • सबसे खराब स्थिति के लिए अपने लॉग फ़ाइल को उचित रूप से आकार देना - जैसे आपके रखरखाव या ज्ञात बड़े ऑपरेशन।और जब आप अपनी लॉग फाइल बढ़ाते हैं तो आपको इस पर गौर करना चाहिएguidance(और दो लिंक वह आपको भेजता है) किम्बर्ली ट्रिप द्वारा।यहां राइट साइजिंग सुपर क्रिटिकल है।
  • लेन-देन के अपने उपयोग को देखना।अपने एप्लिकेशन सर्वर में लेन-देन शुरू न करें और SQL सर्वर के साथ लंबी बातचीत करना शुरू कर दें और एक बहुत लंबा खुला छोड़ दें।
  • यह देखनालेन-देन निहित हैआपके डीएमएल बयानों में।उदाहरण के लिए:UPDATE TableName Set Col1 = 'New Value'एक लेन-देन है।मैंने नहीं डालाBEGIN TRANवहाँ और मेरे पास नहीं है, यह अभी भी एक लेनदेन है जो बस स्वचालित रूप से किया जाता है जब किया जाता है।इसलिए यदि बड़ी संख्या में पंक्तियों पर परिचालन किया जाता है, तो उन कार्यों को अधिक प्रबंधनीय विखंडू में बैचने पर विचार करें और लॉग को पुनर्प्राप्त करने का समय दें।या इससे निपटने के लिए सही आकार पर विचार करें।या शायद एक थोक लोड विंडो के दौरान रिकवरी मॉडल को बदलते हुए।

क्या ये दो कारण लॉग शिपिंग के लिए भी लागू होते हैं?

संक्षिप्त उत्तर: हाँ।नीचे लंबा जवाब।

सवाल:"मैं लॉग शिपिंग का उपयोग कर रहा हूं, इसलिए मेरे लॉग बैकअप स्वचालित हैं ... मैं अभी भी लेन-देन लॉग वृद्धि क्यों देख रहा हूं?"

उत्तर: पर पढ़ें।

लॉग शिपिंग क्या है?

लॉग शिपिंग केवल ऐसा लगता है जैसे - आप DR लेनदेन के लिए अपने लेनदेन लॉग बैकअप को किसी अन्य सर्वर पर भेज रहे हैं।कुछ आरंभीकरण है लेकिन उसके बाद प्रक्रिया काफी सरल है:

  • एक सर्वर पर लॉग इन करने के लिए एक नौकरी,
  • उस लॉग बैकअप और कॉपी करने के लिए एक नौकरी
  • वसूली के बिना इसे बहाल करने का काम (या तोNORECOVERYयाSTANDBY) गंतव्य सर्वर पर।

अगर आपकी योजना के अनुसार चीजें नहीं होती हैं, तो निगरानी और सतर्क करने के लिए कुछ काम भी हैं।

कुछ मामलों में, आप केवल लॉग शिपिंग को दिन में एक बार या हर तीसरे दिन या सप्ताह में एक बार करना चाहते हैं।यह ठीक है।लेकिन अगर आप सभी नौकरियों (लॉग बैकअप और कॉपी नौकरियों सहित) पर यह बदलाव करते हैं, तो इसका मतलब है कि आप लॉग बैकअप लेने के लिए उस समय का इंतजार कर रहे हैं।इसका मतलब है कि आपके पास बहुत अधिक वृद्धि होगी - क्योंकि आप हैंपूर्ण बैकअप मोड में लॉग बैकअप के बिना- और इसका मतलब यह भी है कि एक बड़ी लॉग फ़ाइल को कॉपी करने के लिए।आपको केवल नौकरी की अनुसूची को संशोधित करना चाहिए और लॉग बैकअप और प्रतियां अधिक लगातार आधार पर होने चाहिए, अन्यथा आप इस उत्तर में वर्णित पहले मुद्दे से पीड़ित होंगे।


स्थिति कोड के माध्यम से सामान्य समस्या निवारण

इन दोनों के अलावा और भी कारण हैं, लेकिन ये सबसे आम हैं।कारण चाहे जो भी हो: एक तरीका है जिससे आप इस अस्पष्टीकृत लॉग वृद्धि/छंटनी की कमी के अपने कारण का विश्लेषण कर सकते हैं और देख सकते हैं कि वे क्या हैं।

क्वेरी करकेsys.databasesकैटलॉग दृश्य आप जानकारी देख सकते हैं जिस कारण से आपकी लॉग फ़ाइल truncate/reuse पर प्रतीक्षा कर रही है।

एक कॉलम कहा जाता हैlog_reuse_waitकारण कोड की एक लुकिंग आईडी और ए के साथlog_reuse_wait_descप्रतीक्षा कारण के विवरण के साथ कॉलम।संदर्भित पुस्तकों से ऑनलाइन लेख अधिकांश कारण हैं (जिन्हें आप देखने की संभावना रखते हैं और जिन्हें हम कारणों के बारे में बता सकते हैं। लापता लोग या तो उपयोग से बाहर हैं या आंतरिक उपयोग के लिए) प्रतीक्षा के बारे में कुछ नोट्स के साथ।इटैलिक:

  • 0 = कुछ नहीं
    यह क्या लगता है .. इंतज़ार नहीं किया जाना चाहिए

  • 1 = चौकी
    चौकी लगने का इंतजार।ऐसा होना चाहिए और आपको ठीक होना चाहिए - लेकिन बाद में जवाब या संपादन के लिए यहां देखने के लिए कुछ मामले हैं।

  • 2 = लॉग बैकअप
    आप एक लॉग बैकअप के आने की प्रतीक्षा कर रहे हैं।या तो आपने उन्हें निर्धारित किया है और यह जल्द ही होगा, या आपको यहां वर्णित पहली समस्या है और अब आप इसे ठीक करना जानते हैं

  • 3 = सक्रिय बैकअप या पुनर्स्थापना
    डेटाबेस पर एक बैकअप या पुनर्स्थापना कार्रवाई चल रही है

  • 4 = सक्रिय लेनदेन
    एक सक्रिय लेनदेन है जिसे पूरा करने की आवश्यकता है (किसी भी तरह -ROLLBACKयाCOMMIT) लॉग से पहले बैकअप लिया जा सकता है।इस उत्तर में वर्णित यह दूसरा कारण है।

  • 5 = डेटाबेस मिररिंग
    या तो एक दर्पण एक उच्च प्रदर्शन मिररिंग स्थिति में कुछ विलंबता के पीछे या पीछे हो रहा है या दर्पण किसी कारण से रुका हुआ है

  • 6 = प्रतिकृति
    प्रतिकृति के साथ समस्याएँ हो सकती हैं जो इसका कारण बनती हैं - जैसे लॉग रीडर एजेंट नहीं चल रहा है, यह सोचकर एक डेटाबेस यह प्रतिकृति के लिए चिह्नित है कि अब नहीं है और विभिन्न अन्य कारण हैं।आप इस कारण को भी देख सकते हैं और यह पूरी तरह से सामान्य है क्योंकि आप सिर्फ सही समय देख रहे हैं, जैसे कि लॉग रीडर द्वारा लेनदेन किया जा रहा है

  • 7 = डेटाबेस स्नैपशॉट निर्माण
    आप एक डेटाबेस स्नैपशॉट बना रहे हैं, यदि आप एक स्नैपशॉट बनाया जा रहा है तो आप इसे सही समय पर देखेंगे

  • 8 = लॉग स्कैन
    मुझे अभी तक इस मुद्दे के साथ हमेशा के लिए सामना करना पड़ रहा है।यदि आप लंबे समय तक और बार-बार पर्याप्त देखते हैं तो आप ऐसा हो सकते हैं, लेकिन यह अत्यधिक लेन-देन लॉग वृद्धि का कारण नहीं होना चाहिए, जो मैंने देखा है।

  • 9 = एक ऑल्टरऑन उपलब्धता समूह द्वितीयक प्रतिकृति इस डेटाबेस के लेन-देन लॉग रिकॉर्ड को संबंधित माध्यमिक डेटाबेस पर लागू कर रही है।अभी तक स्पष्ट विवरण के बारे में ..

+1

पृष्ठ विभाजन लॉगिंग को बढ़ाएगा।एक महत्वपूर्ण कारण (मेरे अनुभव में) जिसे बड़ी वृद्धि के लिए उल्लेख नहीं किया गया है जिसे बार-बार सिकुड़ने की आवश्यकता हो सकती है जिसे मेरे बहुत सारे मामलों में हल किया गया है ताकि उचित FillFactor mgmt सहित उचित सूचकांक विकल्पों का उपयोग किया जा सके।मैं नज़दीकी अवलोकन के साथ, निम्नलिखित सेटिंग्स का उपयोग करता हूं।एफएफ सेटिंग्स: (0/100) उच्च-रीड्स/लो राइट्स के साथ टेबल, (90) थोड़ा संशोधित के लिए, (80) मध्यम-रीड्स/कम-मेड लिखते हैं, (70) हाई-राइट्स, (60) मैं शायद ही इस तक पहुंच पाता हूं स्तर या कुछ और गलत हो सकता है।डेटा वॉल्यूम से मेल खाते इंडेक्स मैनेजमेंट शेड्यूल का ठीक से इस्तेमाल करें। 08 oct. 152015-10-08 19:31:03


98

चूँकि मैं over on Stack Overflow किसी भी जवाब से वास्तव में संतुष्ट नहीं हूँ, जिसमें सबसे भारी-भरकम सुझाव भी शामिल है, और क्योंकि कुछ चीजें हैं जिन्हें मैं संबोधित करना चाहूंगा कि माइक का जवाब नहीं है, मैंने सोचा कि मैं अपना इनपुट यहाँ प्रदान करूँगा भी।मैंने इस जवाब की एक प्रति वहां भी रखी।

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

सबसे पहले, एक पूर्ण बैकअप लें

अपने डेटाबेस में कभी भी बदलाव न करें, यह सुनिश्चित करने के बिना कि आप इसे बहाल कर सकते हैं, कुछ गलत होना चाहिए।

अगर आप पॉइंट-इन-टाइम रिकवरी की परवाह करते हैं

(और पॉइंट-इन-टाइम रिकवरी से मेरा मतलब है कि आप एक पूर्ण या अंतर बैकअप के अलावा किसी भी चीज़ को पुनर्स्थापित करने में सक्षम होने के बारे में परवाह करते हैं।)

संभवतः आपका डेटाबेस FULL रिकवरी मोड में है।यदि नहीं, तो सुनिश्चित करें कि यह है:

ALTER DATABASE yourdb SET RECOVERY FULL;

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

DECLARE @path NVARCHAR(255) = N'\\backup_share\log\yourdb_' 
    + CONVERT(CHAR(8), GETDATE(), 112) + '_'
    + REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','')
    + '.trn';

BACKUP LOG foo TO DISK = @path WITH INIT, COMPRESSION;

ध्यान दें कि \\backup_share\ एक अलग मशीन पर होना चाहिए जो एक अलग अंतर्निहित भंडारण उपकरण का प्रतिनिधित्व करता है।इन्हें एक ही मशीन (या एक अलग मशीन में जो एक ही अंतर्निहित डिस्क्स का उपयोग करता है, या एक अलग वीएम जो एक ही भौतिक होस्ट पर है) का समर्थन करता है, वास्तव में आपकी मदद नहीं करता है, क्योंकि यदि मशीन ऊपर उड़ती है, तो आपने अपना डेटाबेस खो दिया है और इसके बैकअप।आपके नेटवर्क इन्फ्रास्ट्रक्चर के आधार पर यह स्थानीय रूप से बैकअप के लिए अधिक समझ में आता है और फिर उन्हें पर्दे के पीछे एक अलग स्थान पर स्थानांतरित कर सकता है;या तो मामले में, आप उन्हें प्राथमिक डेटाबेस मशीन से जितनी जल्दी हो सके निकालना चाहते हैं।

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

ध्यान दें कि सिकुड़ने से पहले लॉग को दो बार बैकअप लेना पड़ सकता है (धन्यवाद रॉबर्ट)।

तो, आपको अपनी लॉग फ़ाइल के लिए एक व्यावहारिक आकार के साथ आने की आवश्यकता है।यहां कोई भी आपको यह नहीं बता सकता है कि आपके सिस्टम के बारे में बहुत कुछ जानने के बिना क्या है, लेकिन अगर आप लॉग फ़ाइल को बार-बार सिकोड़ रहे हैं और यह फिर से बढ़ रहा है, तो एक अच्छा वॉटरमार्क शायद सबसे बड़े से 10-50% अधिक है। ।मान लीजिए कि 200 एमबी आता है, और आप चाहते हैं कि बाद में कोई भी ऑटोग्रॉथ इवेंट 50 एमबी का हो, तो आप लॉग फाइल का आकार इस तरह से समायोजित कर सकते हैं:

USE [master];
GO
ALTER DATABASE Test1 
    MODIFY FILE
    (NAME = yourdb_log, SIZE = 200MB, FILEGROWTH = 50MB);
GO

ध्यान दें कि यदि लॉग फ़ाइल वर्तमान में> 200 एमबी है, तो आपको इसे पहले चलाने की आवश्यकता हो सकती है:

USE yourdb;
GO
DBCC SHRINKFILE(yourdb_log, 200);
GO

यदि आप समय-समय पर पुनर्प्राप्ति के बारे में परवाह नहीं करते हैं

यदि यह एक परीक्षण डेटाबेस है, और आप पॉइंट-इन-टाइम रिकवरी के बारे में परवाह नहीं करते हैं, तो आपको यह सुनिश्चित करना चाहिए कि आपका डेटाबेस SIMPLE रिकवरी मोड में है।

ALTER DATABASE yourdb SET RECOVERY SIMPLE;

डेटाबेस को SIMPLE रिकवरी मोड में डालने से यह सुनिश्चित हो जाएगा कि SQL सर्वर सभी लेन-देन का रिकॉर्ड रखने के लिए लॉग फ़ाइल (अनिवार्य रूप से निष्क्रिय लेनदेन को चरणबद्ध तरीके से) का फिर से उपयोग करता है (जैसे FULL रिकवरी जब तक आप लॉग को वापस नहीं करते) ।CHECKPOINT ईवेंट लॉग को नियंत्रित करने में मदद करेंगे और सुनिश्चित करें कि यह बढ़ने की आवश्यकता नहीं है जब तक कि आप CHECKPOINT बीच बहुत अधिक टी-लॉग गतिविधि उत्पन्न न करें।

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

USE yourdb;
GO
CHECKPOINT;
GO
CHECKPOINT; -- run twice to ensure file wrap-around
GO
-- 200 MB
DBCC SHRINKFILE(yourdb_log, 200);
GO

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

कुछ चीजें जो आप नहीं करना चाहते हैं

  • TRUNCATE_ONLY विकल्प के साथ लॉग इन करें और फिर SHRINKFILE।एक के लिए, यह TRUNCATE_ONLY विकल्प को हटा दिया गया है और अब SQL सर्वर के वर्तमान संस्करणों में उपलब्ध नहीं है।दूसरा, यदि आप FULL पुनर्प्राप्ति मॉडल में हैं, तो यह आपकी लॉग श्रृंखला को नष्ट कर देगा और एक नए, पूर्ण बैकअप की आवश्यकता होगी।

  • डेटाबेस को अलग करें, लॉग फ़ाइल को हटा दें और फिर से संलग्न करें।मैं इस बात पर जोर नहीं दे सकता कि यह कितना खतरनाक हो सकता है।आपका डेटाबेस वापस नहीं आ सकता है, यह संदिग्ध के रूप में सामने आ सकता है, आपको एक बैकअप (यदि आपके पास एक है, आदि) को वापस करना पड़ सकता है।

  • "सिकुड़ डेटाबेस" विकल्प का उपयोग करेंDBCC SHRINKDATABASE और रखरखाव योजना विकल्प बुरे विचार हैं, खासकर यदि आपको वास्तव में केवल लॉग समस्या का समाधान करने की आवश्यकता है।DBCC SHRINKFILE या ALTER DATABASE ... MODIFY FILE (ऊपर उदाहरण) का उपयोग करके उस फ़ाइल को लक्षित करें जिसे आप स्वतंत्र रूप से समायोजित और समायोजित करना चाहते हैं।

  • लॉग फ़ाइल को 1 MB तक सिकोड़ें।यह आकर्षक लग रहा है क्योंकि, अरे, SQL सर्वर मुझे कुछ परिदृश्यों में ऐसा करने देगा, और इसे मुक्त करने वाले सभी स्थान को देखेगा!जब तक आपके डेटाबेस को केवल पढ़ा जाता है (और यह है, तो आपको इसे ALTER DATABASE का उपयोग करके चिह्नित करना चाहिए), यह बिल्कुल कई अनावश्यक विकास घटनाओं को जन्म देगा, क्योंकि रिकवरी मॉडल की परवाह किए बिना लॉग को वर्तमान लेनदेन को समायोजित करना होगा।उस स्थान को अस्थायी रूप से मुक्त करने का क्या मतलब है, बस एसक्यूएल सर्वर इसे धीरे-धीरे और दर्द से वापस ले सकता है?

  • एक दूसरी लॉग फ़ाइल बनाएँ।यह उस ड्राइव के लिए अस्थायी रूप से राहत प्रदान करेगा जिसने आपकी डिस्क को भर दिया है, लेकिन यह बैंड-सहायता के साथ छिद्रित फेफड़े को ठीक करने की कोशिश कर रहा है।आपको किसी अन्य संभावित समस्या को जोड़ने के बजाय समस्याग्रस्त लॉग फ़ाइल से सीधे निपटना चाहिए।कुछ लेनदेन लॉग गतिविधि को एक अलग ड्राइव पर पुनर्निर्देशित करने के अलावा, एक दूसरी लॉग फ़ाइल वास्तव में आपके लिए कुछ भी नहीं करती है (एक दूसरी डेटा फ़ाइल के विपरीत), क्योंकि केवल एक फ़ाइल का उपयोग कभी भी किया जा सकता है।Paul Randal also explains why multiple log files can bite you later

सक्रिय होना

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

यहां सबसे खराब संभव सेटिंग्स 1 एमबी विकास या 10% वृद्धि हैं।मजाकिया तौर पर, ये SQL सर्वर के लिए डिफॉल्ट हैं (जो मैंने शिकायत की है और asked for changes to no avail) - डेटा फ़ाइलों के लिए 1 एमबी, और लॉग फ़ाइलों के लिए 10%।पूर्व इस दिन और उम्र में बहुत छोटा है, और बाद वाला हर बार लंबी और लंबी घटनाओं की ओर जाता है (कहते हैं, आपकी लॉग फ़ाइल 500 एमबी है, पहली वृद्धि 50 एमबी है, अगली वृद्धि 55 एमबी है, अगली वृद्धि 60.5 एमबी है , इत्यादि - और धीमी आई/ओ पर, मेरा विश्वास करो, आप वास्तव में इस वक्र को नोटिस करेंगे)।

आगे की पढाई

कृपया यहाँ मत रोको;लॉग फ़ाइल के सिकुड़ने के बारे में बहुत कुछ सलाह देने के बाद भी आप बुरी तरह से खराब हैं और संभावित रूप से विनाशकारी भी हैं, लेकिन कुछ लोग ऐसे भी हैं जो डिस्क स्पेस को खाली करने की तुलना में डेटा अखंडता की अधिक परवाह करते हैं।


21

आप अपनी लॉग फ़ाइल की सामग्री भी देख सकते हैं।ऐसा करने के लिए, आप अनिर्दिष्ट का उपयोग कर सकते हैंfn_dblog, या लेन-देन लॉग रीडर, जैसेApexSQL Log

यह सूचकांक पुनर्गठन को प्रदर्शित नहीं करता है, लेकिन यह सभी डीएमएल और विभिन्न डीडीएल घटनाओं को दर्शाता है:ALTER,CREATE,DROP, ट्रिगर इनेबल/डिसेबल, ग्रांट/रिवोक परमिशन, ऑब्जेक्ट रिनेम।

ApexSQLLogProject.temp - ApexSQL.log

अस्वीकरण: मैं एक सहायक अभियंता के रूप में एपेक्सक्यूएल के लिए काम करता हूं


1

यह लगभग सभी डीबीए के लिए सबसे अधिक बार सामना किया जाने वाला मुद्दा है जहां लॉग बढ़ता है और डिस्क को भरता है।

• लेन-देन लॉग बढ़ने के कुछ कारण क्या हैं?

  1. लंबे सक्रिय लेनदेन
  2. हाई लॉगिंग ट्रांजेक्शन जैसे कि इंडेक्स रीइंवेस्ट, री-ऑर्गन, बल्क इंसर्ट, डिलीटेस आदि।
  3. किसी भी हा जैसे प्रतिकृति, मिररिंग कॉन्फ़िगर किया गया है जो लॉग रखता है और इसे लॉग स्पेस को रिलीज़ करने की अनुमति नहीं देता है

• मेरी लॉग फ़ाइल इतनी बड़ी क्यों है?

के लिए जाँच करेंlog_reuse_wait_desc कॉलम मेंsys.databasesयह पता करने के लिए कि ट्रंकिंग से लॉग को क्या रखा गया है:

select name, log_reuse_wait_desc 
from sys.databases

• इस समस्या को होने से रोकने के लिए कुछ तरीके क्या हैं?

लॉग बैकअप आपको लॉग ग्रोथ को नियंत्रित करने में मदद करेगा जब तक कि कुछ ऐसा न हो जो लॉग को पुन: उपयोग करने से रोक रहा हो।

• जब मैं अपने आप को अंतर्निहित कारण के साथ ट्रैक पर लाता हूं और अपनी लेनदेन लॉग फाइल को एक स्वस्थ आकार में रखना चाहता हूं तो मैं क्या करूं?

यदि आपने पहचान लिया है कि वास्तव में क्या कारण है तो नीचे दिए गए पृष्ठ के अनुसार इसे ठीक करने का प्रयास करें।

https://www.brentozar.com/archive/2016/03/my-favorite-system-column-log_reuse_wait_desc/

जब तक किसी असामान्य स्थिति के लिए लॉग विकास के साथ काम करने का सबसे अच्छा तरीका है उचित लॉग बैकअप शेड्यूल करना।