समस्या: TestDB डेटाबेस के लिए डेटाबेस इंटीग्रिटी जॉब IOPS समस्या के कारण विफल हो रही थी जिसका आकार 2 TB से अधिक है। बड़ी फ़ाइल आकार के कारण, डेटाबेस को मैनेज करना मुश्किल हो रहा है।
दृष्टिकोण: इसका निवारण करने के लिए, हमने 2 डेटा फाइलों के बीच डेटा को विभाजित करने का निर्णय लिया। तो ड्राइव स्पेस और डेटा फाइल की वर्तमान स्थिति नीचे दी गई है:
हमारा डेटा फाइल N:\ ड्राइव में होस्टेड है और हम उसी स्थान पर एक और फाइल बनाएंगे। हमारा दृष्टिकोण emptyfile कमांड का उपयोग करके डेटा ट्रांसफर शुरू करना और क्वेरी को बीच में मैन्युअली रोककर डेटा मूवमेंट को जबरन रोकना है। कृपया ध्यान दें कि क्वेरी को बीच में मैन्युअली रोकने का डेटाबेस (इंटीग्रिटी/कंसिस्टेंसी) पर कोई प्रभाव नहीं पड़ेगा। फिर हम खाली स्थान को पुनः प्राप्त करने के लिए mdf फाइल को श्रिंक करेंगे।
समाधान: एक ही SQL सर्वर डेटा फाइलों के बीच डेटा को विभाजित करने के लिए नीचे दिए गए चरणों का पालन करें। सबसे पहले, हमें एक सेकेंडरी डेटा फाइल जोड़ने की आवश्यकता है जिसमें हम अपना डेटा डालेंगे। इसे ndf (नेक्स्ट डेटा फाइल) के रूप में जोड़ा जाएगा। TestDB डेटाबेस पर अतिरिक्त डेटा फाइल जोड़ने के लिए नीचे स्क्रिप्ट चलाएं
USE [master] GO ALTER DATABASE [TestDB] ADD FILE ( NAME = N'TestDB_1', FILENAME = N'N:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestDB_1.ndf' , SIZE = 209715200KB , FILEGROWTH = 5242880KB ) TO FILEGROUP [PRIMARY]GO एक बार जब आप इस स्क्रिप्ट को निष्पादित करते हैं, तो यह N:\ ड्राइव में TestDB_1 नामक एक नई डेटा फाइल जोड़ देगा और आकार 200 GB होगा (हमने इसे अपने डेटाबेस के संदर्भ में लिया)। हमने 5 GB की फाइल वृद्धि सेट की है और डेटाफाइल को प्राइमरी फाइल ग्रुप में जोड़ा जाएगा।
अब, डेटा फाइल जोड़ने के बाद TestDB डेटाबेस पर DBCC emptyfile ऑपरेशन शुरू करें। सिंटैक्स होगा:
use YOURDATABASE go dbcc shrinkfile(‘mdfFileName’,emptyfile)तो हमारे मामले में यह होगा:
USE [TestDB] go DBCC shrinkfile ('TestDB',emptyfile)यहां TestDB उस फाइल का लॉजिकल नाम है जिससे हम डेटा निकालना चाहते हैं यानी हमारी mdf फाइल। अब एक बार जब हमने इस ऑपरेशन को शुरू किया, तो हमें ट्रैक रखने की आवश्यकता है कि mdf से ndf में कितना डेटा स्थानांतरित हुआ है। आप इसके लिए नीचे दिए गए क्वेरी का उपयोग कर सकते हैं:
USE [TestDB] GO SELECT [TYPE] = A.TYPE_DESC ,[FILE_Name] = A.name ,[FILEGROUP_NAME] = fg.name ,[File_Location] = A.PHYSICAL_NAME ,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0) ,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)) ,[USEDSPACE_%] = CAST((CAST(FILEPROPERTY(A.name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(A.size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) ,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0) ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100) ,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -' WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted' ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END + CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id order by A.TYPE desc, A.NAME;हमारे दृष्टिकोण के लिए, हम चाहते थे कि ndf लगभग 500 GB पर हो, इसलिए एक बार जब ndf इस आकार तक पहुंच जाता है, तो हम emptyfile ऑपरेशन को रोक सकते हैं। एक बार emptyfile ऑपरेशन रुक जाने के बाद, हमें नीचे दिए गए क्वेरी का उपयोग करके mdf में खाली स्थान को मैन्युअली पुनः प्राप्त करने की आवश्यकता है:
DBCC Shrinkfile('TestDB', 1500000) -- हमें आकार को छोटे टुकड़ों में बदलने की आवश्यकता है
अब हमारा mdf 2 TB था, हमने 500 GB को ndf में स्थानांतरित किया, इसलिए 500 GB mdf से पुनः प्राप्त करने योग्य है, जिसे हमने ऊपर क्वेरी का उपयोग करके पुनः प्राप्त किया।
हम अपने स्टोरेज के अनुसार ऑपरेशन को बीच में मैन्युअली रोककर और फिर से स्पेस को पुनः प्राप्त करके डेटा को डेटाफाइलों के बीच स्थानांतरित करने के लिए इस चरण को कई बार दोहरा सकते हैं।
mdf पर emptyfile का उपयोग करते समय एक बात ध्यान देने योग्य है कि आप फ़ाइल आईडी 1 के साथ प्राइमरी डेटा फाइल की सामग्री को पूरी तरह से खाली नहीं कर पाएंगे। फ़ाइल आईडी नंबर प्राप्त करने के लिए, यह स्क्रिप्ट चलाएं।
select file_id, name,physical_name from sys.database_filesयहां, इस उदाहरण में, फ़ाइलनाम "mo" है और file_id 1 है। जब आप mo नामक फ़ाइल को खाली करने का प्रयास करते हैं जिसकी file_id 1 है, तो आप इस एरर मैसेज का सामना करेंगे।
ऐसा इसलिए है क्योंकि मूल फ़ाइल के भीतर सिस्टम जानकारी है, जिसे खाली नहीं किया जा सकता। लेकिन, यदि आप अन्य डेटा फ़ाइल "mo2data" पर समान कमांड आज़माते हैं, तो empty file कमांड सफल होगा।
निष्कर्ष
एक बार यह डेटा मूवमेंट एक्टिविटी पूरी हो जाने के बाद, कृपया डेटाबेस मेंटेनेंस जॉब्स चलाएं: – इंडेक्स ऑप्टिमाइज़ जॉब – इंटीग्रिटी चेक जॉब – फुल डेटाबेस बैकअप जॉब।
कोई भी टिप्पणी करने या प्रश्न पूछने के लिए फीडबैक टैब का उपयोग करें। आप हमारे साथ बातचीत भी शुरू कर सकते हैं।
