آزاد كردن Pageهاي خالي بدون Shirink Database

[A.Farzin]

با سلام
من یک دیتابیس 2000 دارم که حجم واقعی دیتاها و ایندکسهای آن حدود 30GB است ولی در حال حاضر حدود 70GB فضای هارد را اشغال کرده است.
این دیتابیس شدیداً Fragment شده بود که با DBCC DBREINDEX رفع شد.
ولی حالا شدیداً با محدودیت فضا روی هارد مواجه‌ام. با وجود آنکه Recovery Model روی حالت Simple است باز هم کاربران با پیغام log file is full ... مواجه می‌شوند.
از آنجا که Shirink Database، دیتابیس را Fragmentمجدداً می‌کند از آن استفاده نکرده ولی همیشه مجبورم با DBCC SHRINKFILE فقط فایل Log را جمع و جور کنم تا کاربران بتوانند کار کنند.
چه راههائی برای مرتب کردن دیتاها در Pageها وجود دارد تا این 40GB فضای الکی اشغال شده آزاد شود (آنچه در زیر آمده به ترتیب مقدورترین روشها برای من است. فرض کنید امکان افزایش هارد وجود ندارد):
الف) آخرین Full Backup دیتابیس را Restore کنم؛
ب) از Shirink Database استفاده کنم؛
ج) یک Database جدید بسازم و داده‌ها را به آن Export کنم؛
د) دیتابیس را به 2005، attach کنم و از Reorganize استفاده کنم و با همان 2005 کار کنم؛
ه) سایر روشهایی که من بلد نیستم.

ممنون

[AminSobati]

سلام دوست عزیزم،
Backup/Restore کمکی در رفع این مشکل نمیکنه. Export هم زمان قابل توجهی میبره. بهترین کار این هست که یک Filegroup جدید بسازین و تمام جداول و ایندکسها رو به اون منتقل کنین (به کمک CREATE INDEX....WITH DROP_EXISTING). حالا اگر فایل قبلی رو Shrink کنین به حجم بسیار کوچکی میرسه، ضمن اینکه همه ایندکسها Rebuild شده اند و در Filegroup جدید منظم هستند

[A.Farzin]

با عرض سلام خدمت استاد عزیز،
طبق فرموده همه ایندکسها را به FileGroup جدید بردم. در مورد جداولی که Relation نداشتند، PKها و CONSTRAINT UNIQUE CLUSTEREDها را ابتدا DROP و مجدداً در FileGroup جدید ADD کردم.
ولی در مورد جداولی که Relation دارند، این روش برای CONSTRAINT PRIMARY KEY CLUSTERED و همچنین CONSTRAINT UNIQUE CLUSTERED قابل انجام نیست. لطفاً راهنمایی فرمائید که چگونه باید عمل کرد که ارتباطات یا Relationهای بین جداول به هم نخورد.
ممنون

[AminSobati]

من متوجه نشدم، آیا از CREATE INDEX....WITH DROP_EXISTING استفاده کردین یا جداگانه Drop کردین؟

[A.Farzin]

با عرض ادب،
الف) برای Indexها اعم از Clustered یا NonClustered از CREATE INDEX....WITH DROP_EXISTING استفاده کردم. ولی این روش برای PKها شاید به دلیل اینکه نیاز به ALTER TABLE داشتند ممکن نبود.
ب) در مورد PKها و CONSTRAINT UNIQUE CLUSTEREDها:

ب-1) اگر Table فاقد Relation بود، ابتدا DROP و سپس در FG جدید ADD کردم.
ب-2) ولی اگر Table دارای Relation بود، گیر کردم چون هیچکدام از دو روش عمل نمی کند و خطای ارتباط با FK می‌داد. امکان حذف RELATIONها و تعرف مجدد آنها هم نیست.
لطفاً راهنمایی فرمائید که در حالت ب-2 فوق چگونه باید عمل کرد که ارتباطات یا Relationهای بین جداول به هم نخورد.
ممنون

[AminSobati]

در مورد PKها عبارت Unique رو رعایت کردین؟



create unique clustered index pk_orders on orders(orderid) with (drop_existing=on)

[A.Farzin]

با سلام
به مدد لطف استاد محترم جناب آقاي ثباتي، مشکل حل شد.

برای PKها (با قید CLUSTERED یا NONCLUSTERED بسته به مورد) چنین عمل کردم:

CREATE UNIQUE NONCLUSTERED INDEX PKix ON tbl
(
[fld] ASC
)
WITH DROP_EXISTING
ON [NewFG]
GO

برای UNIQUE Keysها (با قید CLUSTERED یا NONCLUSTERED بسته به مورد) چنین عمل کردم:

CREATE UNIQUE CLUSTERED INDEX UKix ON tbl
(
[fld1] ASC,
[fld2] ASC,
[fld3] ASC,
[fld4] ASC
)
WITH DROP_EXISTING
ON [NewFG]
GO
ممنون

[mehdi.mousavi]

سلام.
سوالی برام پیش اومده و اونم اینکه آیا ابزاری برای نمایش میزان Fragmentation وجود داره؟ یا اینکار رو باید در Interval های مشخصی تکرار کرد تا از عدم گسستگی در فایل اطمینان حاصل کنیم یا ابزاری وجود داره که بگه مثلا 75% Fragmentation داریم و حالا وقتشه که ...

ممنون.

[AminSobati]

سلام،
از نسخه 2005 به بعد میتونین از تابع sys.dm_db_index_physical_stats استفاده کنین. در نسخه های قدیمی تر از DBCC ShowContig

[A.Farzin]

با سلام

من یک اشتباهی کردم و آن اینکه بعد از ساخت ایندکسها در فایل گروپ جدید، به دلیلی که در تاپیک زیر اشاره شده است،
http://www.barnamenevis.org/forum/showthread.php?t=201980
آمدم کل ایندکسها را از نو DBREINDEX کردم (SQL 2000).
حالا حجم فایلهای ایندکس نزدیک به دو برابر شده است. Shrink هم که نمی‌شود کرد!!
حالا چکار کنم؟
الف) کلیه ایندکسها را DROP کرده بعداً فایل را Shrink کنم و سپس ایندکسها را بسازم که در این صورت امکان حدف ایندکسهای PK به دلیل ارتباطات میسر نیست.
ب) کلیه ایندکسها را در یک فایل گروپ جدیدتر DBREINDEX کنم و بعد از اتمام کار فایل گروپ قبلی را حذف کنم.
ج) ....
ممنون

[AminSobati]

تنها راهش انتقال به Filegroup جدیده. البته فضایی که بعد از Rebuild بوجود میاد جای نگرانی نداره چون فضای خالی شده به مرور توسط اطلاعات جدید مورد استفاده قرار میگیره و تا مدتها افزایش فضای فایل نخواهید داشت