SQL Server Administration

[Reza_Yarahmadi]

Detach و Attach کردن دیتابیس
Attach يعني برقراري مجموعه ايي از فايلها به عنوان دیتابیس زير نظر SQL Server.
به عبارت ديگر تعدادي فايل را به SQL Server معرفي كرده و چنانچه ارتباطات بين اين فايلها طوري باشد كه به عنوان SQL Server جديد معرفي شود . دستور Attach آنها را بصورت دیتابیس معرفي مي كند.
برای Attach کردن یک مجموعه فایل روی پوشه Databases راست کلیک کرده و گزینه …Attach را انتخاب کنید و در پي آن با انتخاب گزينه Add به پنجره زير مي رسيم.

ضمیمه 68340

با انتخاب Add بايستي اولين فايل دیتابیس را به SQL Server معرفي كرد ، سپس SQL Server بطور اتوماتيك از مسير موجود ، فايلهاي بعدي دیتابیس را كامل مي كند. در اين هنكام در قسمت پايين اين صفحه اطلاعات شناسايي شده توسط SQL Server نمايان مي شود . در اين حالت مي توان دیتابیس را Attach كرد.

نكته :

Detach / attach بهترين روش براي جابجايي محل فيزيكي دیتابیس مي باشد.

استفاده از SP هاي سيستمي براي Attach كردن دیتابیس ها
براي Attach كردن دیتابیس از دستورات زير مي توان استفاده كرد.

SP_Attach_DB
اين SP داراي 17 ورودي است كه اولی نام دیتابیس و 16 پارامتر بعد به عنوان 16 فايل دیتابیس قابل معرفي مي باشند.
چنانچه 16 پارامتر Null باشند دیتابیس را با تمامي فايلها در موقعيتي كه قبلا در آن بوده است Default Path) Attach مي كند و در صورتي كه بخواهيد دیتابیسی با حداكثر 16 فايل در مسير فيزيكي جديد Attach نماييد می توانید با ذكر مسير جديد براي هر يك از فايل ها دیتابیس را Attach نماييد.

مثلا :

SP_Attach_DB  'DbName' ,

                          'C:\Data\dbName_Data.MDF' ,

                          'E:\Data\ dbName _Data1.MDF ,

                          'D:\Data\ dbName _Data2.MDF ,

                          'C:\Data\ dbName _Log.LDF

در اين وضعيت با Copy كردن آن فايلها در موقعيت هاي مشخص شده ميتوان دیتابیس را Attach كرد.

از اين دستور براي دیتابیس های با فايل هاي بيشتر از 16 نمي توان استفاده كرد و بايستي از دستور Create Database ….. For Attach استفاده كرد.

SP_Attach_Single_File_DB
اين دستور مشابه دستور فوق مي باشد وتنها فرقي كه با آن دارد ، نمي توان مسير فيزيكي فايلها را تغيير داد و بايستي در مسير پيش فرض آنها دیتابیس را Attach كرد از اين دستور براي بازسازي دیتابیس هايي كه تنها يك Log File دارند مي توان استفاده كرد.
اين دستور برای Attach كردن دیتابیس هايي كه قابلیت Replicartion را دارند قابل استفاده است

SP_Attach_Single_File_DB 

                    ' dbName' , 'C:\Data\dbName_Data.MDF'

Create Database ... For Attach
دستور عمومي براي Attach كردن دیتابیس از طريق ايجاد دیتابیس مي باشد

 
Create Database dbName
     
On(
     
      FileName =   'C: \ …… ',
     
      FileName =   'C: \ …… ',
     
      FileName =   'C: \ …… ',
     
      FileName =   'C: \ …… ',
     
      FileName =   'C: \ …… ',
     
      FileName =   'C: \ …… ',
     
      FileName =   'C: \ …… ',
     
      FileName =   'C: \ …… ',
     
      ) 
     
      For Attach   | For Attach_Rebuil_Log
 

گزاره Attach_Rebuil_Log براي ايجاد دیتابیس در زماني است كه بخواهيد دیتابیس را بدون استفاده از Attach Log ، كنيد . در اين حالت Log بازسازي خواهد شد و دیتابیس در اين شرايط Attach مي شود. براي استفاده از اين امكان بايد پيشنيازهاي زير را در نظر گرفت :

1. دیتابیس بصورت نرمال Shutdownشده باشد.
2. تمامي MDF , NDF ها موجود باشند.

Detach يعني قطع ارتباط و مديريت SQL Server به عنوان RDBMS با مجموعه فايل های یک دیتابیس.
با اجرا Detach براي دیتابیس ، تنها ارتباط مديريت SQL Server با فايلها برداشته و فايل هاي دیتابیس به عنوان فايلهاي مجرد باقي مي مانند.
براي Detach كردن روی دیتابیس مورد نظر راست کلیک کرده و از منوی Tasks گزینه Detach را انتخاب کنید و در پنجره بعد OK.

ضمیمه 68341

• براي Detach كردن دیتابیس بايستي Connection هاي دیتابیس همگي قطع شده باشند و براي اينكه ابتدا Connection ها قطع شوند گزينه Drop Connection را انتخاب كنيد .

Shrink کردن دیتابیس و فایل
Shrink به معناي كوچك كردن و آب رفتن مي باشد . اين عمل در كارايي و نگهداري بهينه دیتابیس بسيار موثر است. قبل از توضیح دادن مبحث Shrink بهتر است ابتدا با معماری فیزیکی Transaction Log آشنا شویم.

معماری فیزیكی Transaction Log
Transaction Log می‌تواند با یك یا چند فایل فیزیكی پیاده سازی گردد. منطقا" Transaction Log از تعدادی ركورد پشت سر هم و رشته‌ای تشكیل می‌شود و بصورت فیزیكی این ركوردهای پشت سر هم باید بصورت كارا در یك مجموعه از فایلهای فیزیكی پیاده‌سازی گردند.

SQL Server هر فایل فیزیكی را به قسمتهای مجازی تقسیم می‌كند. این قسمتهای مجازی اندازه ثابتی نداشته و تعداد ثابتی در هر فایل فیزیكی نیز ندارند. اندازه این قسمتهای مجازی بصورت پویا هنگام ایجاد و یا گسترش Transaction Log توسط SQL Server مشخص می‌گردند. SQL Server همواره سعی می‌كند تعداد كمتری از قسمتهای مجازی را نگهداری كند. اندازه این قسمتهای مجازی هنگام تعریف اندازه Transaction Log و همچنین اندازه افزایش آن مشخص می‌گردد. تعداد و اندازه قسمتهای مجازی توسط خود SQL Server بصورت پویا مشخص می‌گردد و قابل تعریف و تنظیم توسط كاربر سیستم نیستند. تنها زمانی كه قسمتهای مجازی بر كارائی سیستم تاثیر می‌گذارند زمانی است كه اندازه Transaction Log و مقدار افزایش آن با مقادیر كم تعریف شوند. در این صورت اگر Transaction Log با تعداد زیادی افزایش (با حجم كم) بزرگ شود، تعداد زیادی قسمت مجازی ایجاد خواهد گردید كه در نتیجه سرعت ترمیم را كاهش می‌دهند. بنابراین توصیه می‌گردد كه سعی شود اندازه فایل Transaction Log نزدیك به نیاز واقعی تعریف شود و همچنین مقادیر توسعه اندازه Transaction Log بزرگ در نظر گرفته شوند.

ذخیره اطلاعات در Transaction Log بصورت چرخشی است. برای مثال یك Transaction Log كه به چهار قسمت مجازی تقسیم شده را در نظر بگیرید ، هنگام ایجاد بانك اطلاعاتی فایل منطقی Log از ابتدای فایل فیزیكی Log آغاز می‌شود. و ركوردهای جدید به انتهای فایل منطقی Log اضافه می‌شوند و گسترش به سمت انتهای فایل فیزیكی Log ادامه می‌یابد. با انجام یك عمل Shrinking قبل از قسمت مجازی شامل MinLSN حذف می‌گردند.
هنگامیكه انتهای فایل منطقی Log به انتهای فایل فیزیكی Log برسد ، ركوردهای جدید بصورت چرخشی دوباره از ابتدای فایل فیزیكی Log ذخیره می‌شوند.
این چرخش تا هنگامیكه انتهای فایل منطقی Log به ابتدای فایل فیزیكی Log نرسد، ادامه خواهد داشت. درصورتیكه ركوردهای Transaction قدیمی Shrink شوند اغلب فضای كافی برای ركوردهای جدید Transaction تا Checkpoint بعدی وجود خواهد داشت و Transaction Log هیچگاه پر نمی‌شود.

درصورتیكه انتهای فایل منطقی Log به ابتدای فایل فیزیكی Log برسد ، یعنی Transaction Log پر گردد ، دو حالت ممكن است رخ دهد:

• اگر گزینه گسترش خودكار (autogrow) برای Transaction Log فعال باشد و فضای كافی بر روی دیسك وجود داشته باشد ، اندازه فایل فیزیكی Log به اندازه تعریف شده افزایش (growth_increment) ، افزایش یافته و ركوردهای جدید تراكنش در فضای اضافه شده ذخیره می‌شوند.
• اگر گزینه گسترش خودكار (autogrow) برای Transaction Log فعال نباشد و یا فضای كافی بر روی دیسك سخت به مقدار افزایش تعیین شده (growth_increment) وجود نداشته باشد، خطای شماره 1105 روی خواهد داد.

در صورتیكه Transaction Log دارای چندین فایل فیزیكی باشد، فایل منطقی گزارش قبل از چرخش ابتدا به ترتیب بر روی تمام فایلهای فیزیكی گسترش خواهد یافت و در انتها به ابتدای فایل فیزیكی اول چرخش خواهد نمود.

Shrinking
اندازه فایل Transaction Log بصورت فیزیكی در حالتهای زیر كاهش می‌یابد:

• هنگام اجرای دستور DBCC SHRINKDATABASE.
• هنگام اجرای دستور DBCC SHRINKFILE با اشاره به فایل Transaction Log مورد نظر.
• هنگام اجرای دستور خودكار كوچك شدن (autoshrink).

عمل كم كردن حجم Transaction Log به عمل Truncate آن بستگی دارد. Truncate شدن Transaction Log موجب كم شدن اندازه فیزیكی فایل آن نمی‌گردد ، بلكه اندازه فایل منطقی گزارش را كم می‌كند و قسمتهای مجازی فایل گزارش را كه اطلاعات فایل منطقی را در خود نگهداری نمی‌كنند غیر فعال می‌كند. واحد كاهش اندازه فیزیكی Transaction Log به اندازه یك قسمت مجازی می‌باشد. برای مثال در یك Transaction Log 600 مگا بایتی كه به شش قسمت مجازی 100 مگا بایتی تقسیم شده واحد كاهش اندازه فیزیكی آن در اندازه‌های 100 مگا بایتی می‌باشد و اندازه آن برای مثال می‌تواند به 500 یا 400 مگا بایت كاهش یابد و هیچگاه اندازه آن در این حالت به اندازه‌های 433 و یا 525 مگا بایت كاهش پیدا نمی‌كند.

قسمتهای مجازی Transaction Log كه حاوی گزارش منطقی تراكنشها هستند هیچگاه نمی‌توانند آزاد گردند. و درصورتیكه تمام قسمتهای مجازی نگهدارنده گزارش منطقی تراكنشها باشند Shrink كردن نمی‌تواند انجام گیرد. علاوه بر این عمل Shrink كردن تنها در صورتی انجام خواهد شد كه قسمتهای مجازی غیر فعال در انتهای فایل فیزیكی Transaction Log قرار گرفته باشند. هنگام كم شدن اندازه فیزیكی Transaction Log فضاهای خالی باید از انتهای فایل فیزیكی آزاد شوند و قسمتهای مجازی غیرفعال انتهای فایل به اندازه مورد درخواست كاربر آزاد خواهند گردید. اندازه مورد درخواست كاربر (target_size) به سمت قسمت مجازی بعدی نزدیكترین محدوده قسمت مجازی موجود گرد خواهد گردید. برای مثال اگر كاربر مقدار فضای 325 مگا بایت را برای اندازه Transaction Log ای با اندازه 600 مگا‌‌ بایت و قسمتهای مجازی 100 مگا بایتی مشخص كند ، دو قسمت مجازی آخر در صورت غیر فعال بودن حذف شده و اندازه فایل به 400 مگا بایت كاهش می‌یابد.

دستورات DBCC SHRINKDATABASE و DBCC SHRINKFILE اندازه فیزیكی Transaction Log را بصورت زیر كم می‌كنند.

• اگر هیچ قسمتی از فایل منطقی گزارش موجود در قسمتهای مجازی ورای اندازه مشخص شده توسط كاربر نبوده و قسمتهای مجازی بالاتر از فضای درخواستی كاربر غیرفعال باشند ، عمل Shrinking با حذف این قسمتهای مجازی غیر فعال انجام خواهد گردید.
• اگر قسمتی از فایل منطقی گزارش در قسمتهای مجازی بالای اندازه مشخص شده توسط كاربر باشد. SQL Server هر اندازه از فضا را كه بتواند ( قسمتهای مجازی غیر فعال در انتهای فایل فیزیكی) آزاد می‌كند و یك پیام نیز جهت راهنمائی درباره اعمالی كه می‌توان با انجام دادن آنها فایل منطقی را از قسمتهای مجازی انتهای فایل خارج نمود ، ارائه می‌نماید. بعد از انجام آن عملیات با اجرای مجدد دستور DBCC فضای باقی مانده آزاد می‌گردد.

برای مثال فرض كنید یك فایل گزارش با حجم 600 مگا بایت و 6 قسمت مجازی كه شروع فایل منطقی آن در قسمت مجازی 3 و انتهای فایل منطقی آن در قسمت مجازی 4 قرار دارد، دستور DBCC SHRINKFILE با target_size = 255 MB را اجرا كنیم.
قسمتهای مجازی 5 و 6 با توجه به اینكه حاوی هیچ قسمتی از فایل منطقی نیستند و در انتهای فایل فیزیكی واقع شده‌اند ، بلافاصله آزاد می‌شوند. برای رسیدن به اندازه مورد درخواست قسمت مجازی 4 نیز باید آزاد گردد ولی با توجه به اینكه این قسمت حاوی قسمتی از فایل منطقی گزارش می‌باشد این امر ممكن نخواهد بود. بعد از آزادسازی قسمتهای 5 و 6 مدیر بانك اطلاعاتی SQL Server فضای خالی باقی مانده از قسمت مجازی 4 را با ركوردهای ساختگی(dummy) پر می‌كند. این عمل باعث می شود كه انتها فایل منطقی گزارش بر ابتدای قسمت مجازی 1 منطبق گردد. و اطلاعات تمام تراكنشهای اصلی موجود در قسمت مجازی 4 به قسمت مجازی 1 منتقل شوند.
دستورDBCC SHRINKFILE همچنین یك پیام مبنی بر اینكه تمام فضای مورد درخواست آزاد شده است و اینكه برای آزاد سازی احتمالی بقیه فضا باید از دستور BACKUP LOG استفاده نمود ، نمایش می‌دهد. بعد از انتقال قسمت فعال فایل گزارش به قسمت مجازی 1 ، دستور BACKUP LOG قسمت مجازی شماره 4 را می‌برد (truncate).
با توجه به اینكه قسمت مجازی 4 دیگر هیچ قسمتی از فایل منطقی گزارش را نگهداری نمی‌كند و بریده شده است ، در صورت اجرای مجدد دستور DBCC SHRINKFILE با target_size 255 مگا بایت ، قسمت مجازی 4 هم آزاد شده و اندازه فایل فیزیكی گزارش تراكنشها به اندازه مورد درخواست كاهش خواهد یافت.


Automatic Database Shrink
اين امكان براي زماني است كه دیتابیس داراي فضاخالي باشد و بصورت اتوماتيك عمليات Shrink انجام مي شود. اين امكان بطور پيش فرض غير فعال است و فعال كردن آن روي كار كاربران تاثيري نمي گذارد زيرا اين مورد در خارج از فضاي كاري كاربران انجام مي شود.

Manual Shrink Database
امكان انجام Shrink روي دیتابیس ها بصورت دستي مقدور مي باشد . براي اين كار

ضمیمه 68342

براي زماني كه از گزينه Database استفاده مي كنيم :

ضمیمه 68343

* اين گزينه در مواقعي استفاده مي شود كه بخواهيم قبل از ShrinkPage كردن Data Page ها ، آنها را مرتب كنند. اين امكان در كارايي دیتابیس بسیار موثر مي باشد.
** مقدار فضاي خالي بر حسب درصد از سايز فايل پس از اتمام عمل Shrink بايستي تنظيم شود .

نكته :

طبق تعريف Shrink ، همزمان با مرتب سازي Data Page ها ، page هاي خالي ميان Page هاي دیتابیس به انتهاي فايل منتقل خواهد شد و در زمان Shrink فضاي خالي انتهاي فايل بريده شده و Page ها دقيقا به اندازه واقعي Shrink خواهد شد.

تنظيم اين مقدار برابر با 0% براي دیتابیس هايي كه Data Entry دارند و Data هاي آنها بصورت سريالي وارد دیتابیس نمي شود كاهش سرعت را به همراه دارد. به همين دليل توصيه مي شود تا بين 5% الي 10% فضاي خالي را براي دیتابیس در نظر بگيريد.

براي زماني كه از گزينه File استفاده مي كنيم :

ضمیمه 68344

1. نام دیتابیسي كه Shrink خواهد شد.
2. نوع فايلي كه مي خواهيم Srhink نماييم اين نوع يا Data و يا Log مي باشد
3. انتخاب File Group براي انتخاب فايل مربوطه
4. انتخاب فايلي كه مي خواهيم Shrink File كنيم
5. اندازه فعلي و ميزان فضاي خالي در فايل دیتابیس
6. حافظه آزاد شده از انتهاي فايل ها را به سيستم عامل باز گرداند
7. فايل را تا مينيمم ممكن كوچك كند
8. در سطح يك File Group مي توان اطلاعات را يكپارچه كرد و فايلهاي اضافي در File Group را پاك كرد

Shrinking the Transaction Log
براي Shrink كردن log File از حالت Shrink File استفاده مي كنيم. نكته آنكه براي Shrink كردنLog فايل بايستي از خالي بودن Log File مطمئن شويم . در اين حالت براي خالي كردن Log File بهتر است از Log File ، Backup گرفته و سپس Log File را Shrink كرد.

استفاده از دستورات براي Shrink كردن

DBCC ShrinkDatabase ('DatabaeName ' , درصد كوچك شدن)

مثال:

DBCC ShrinkDatabase (dbName, 10)

اين دستور را مي توان روي Master اجرا كرد.
براي Shrink File از دستور زير استفاده مي شود.

DBCC ShrinkFile ( نام فايل , %)

مثال:

 DBCC SHRINKFILE (DataFile1, 7) 

دستور فوق را بايد روي دیتابیس مورد نظر اجرا كرد .