.NET Tips: كاهش حجم لاگ‌ فايل‌هاي اس‌كيوال سرور 2005 و 2008

نمي‌دونم تابحال به صورت جدي با SharePoint مايكروسافت كار كرديد يا نه؟ اين برنامه كه عمده كارهاي خودش رو با SQL server انجام ميده در طول يك روز ممكن است تا 80 گيگ log file اس‌كيوال سرور توليد كند و بعد از چند روز اگر به همين صورت به حال خود رها شود (كه عموما هم به همين صورت است!) ممكن است ديگر قادر به استفاده از سرور به دليل پر شدن درايوي كه لاگ فايل‌ها در آن ذخيره مي‌شوند نباشيد.
همچنين رشد tempdb نيز توسط اين برنامه بسيار چشم‌گير است. بنابراين هميشه به‌خاطر داشته باشيد محل قرارگيري tempdb و همچنين محل قرارگيري لاگ فايل‌ها (كه هر دو قابل تنظيم هستند) را در درايوهايي قرار دهيد كه حداقل 100 گيگ فضاي خالي در آنها موجود باشد.
با استفاده از اسكريپت زير مي‌شود حجم لاگ فايل‌هاي اس كيوال سرور را به حداقل رساند و نفس راحتي كشيد! اين مساله اگر جدي گرفته نشود واقعا تبديل به يك كابوس مي‌شود!
اسكريپت زير كليه ديتابيس‌هاي موجود را يافته و shrink مي‌كند. قسمت offline و online كردن آن هم به اين خاطر است كه ارتباط تمام كاربران متصل را به صورت آني قطع مي‌كند (يكي از چندين روش موجود براي kill كردن كاربران است). (يك stored procedure از آن درست كنيد و با تعريف يك job جديد در اس كيوال سرور ، اين stored procedure را براي مثال هر روز ساعت 3 بامداد به صورت خودكار اجرا كنيد)

Declare @database nvarchar(1000)
Declare @tsql nvarchar(4000)
Declare DatabaseCursor Cursor
Local
Static
For
select name from master.dbo.sysdatabases
open DatabaseCursor
fetch next from DatabaseCursor into @database

while @@fetch_status = 0
begin
 print 'database:' + @database
 if @database not in ('tempdb','master','model','msdb')
 begin
  SET @tsql = 'use master;
     alter database ['+@database+'] set offline with rollback immediate;
     alter database ['+@database+'] set online; 
   DECLARE @dbLogName nvarchar(500) ;  
   Use  ['+@database+']  ;
   select @dbLogName = rtrim(ltrim(name)) from sysfiles WHERE FILEID=2;
   ALTER DATABASE ['+@database+'] SET SINGLE_USER ; 
   DBCC SHRINKFILE(@dbLogName , 2) ; 
   BACKUP LOG  ['+@database+']  WITH TRUNCATE_ONLY ; 
   DBCC SHRINKFILE(@dbLogName , 2) ; 
   ALTER DATABASE ['+@database+']  SET MULTI_USER ;' 
   exec(@tsql)
 end
 fetch next from DatabaseCursor into @database
end

close DatabaseCursor

deallocate DatabaseCursor

اسكريپت فوق با SQL Server 2005 سازگار است اما در SQL Server 2008 منسوخ شده است! (قسمت truncate كردن)
نسخه سازگار با SQL server 2008 آن به صورت زير است:

Declare @database nvarchar(1000)
Declare @tsql nvarchar(4000)
Declare DatabaseCursor Cursor
Local
Static
For
select name from master.dbo.sysdatabases
open DatabaseCursor
fetch next from DatabaseCursor into @database

while @@fetch_status = 0
begin
 print 'database:' + @database
 if @database not in ('tempdb','master','model','msdb')
 begin
  SET @tsql = 'use master;
     alter database ['+@database+'] set offline with rollback immediate;
     alter database ['+@database+'] set online; 
   DECLARE @dbLogName nvarchar(500) ;  
   Use  ['+@database+']  ;
   select @dbLogName = rtrim(ltrim(name)) from sysfiles WHERE FILEID=2;
   ALTER DATABASE ['+@database+'] SET RECOVERY SIMPLE;
   ALTER DATABASE ['+@database+'] SET SINGLE_USER ; 
   DBCC SHRINKFILE(@dbLogName , 2) ;   
   ALTER DATABASE ['+@database+'] SET MULTI_USER ;
   ALTER DATABASE ['+@database+'] SET RECOVERY FULL;' 
   exec(@tsql)
 end
 fetch next from DatabaseCursor into @database
end

close DatabaseCursor

deallocate DatabaseCursor

ماخذ اصلي مورد استفاده:
http://go.microsoft.com/fwlink/?LinkId=111531&clcid=0x409

۱۳۸۷/۰۸/۰۶

كاهش حجم لاگ‌ فايل‌هاي اس‌كيوال سرور 2005 و 2008