Ҳ̸Êýѧ¿ÎµÄÊýѧζ£¬Ò²Ì¸ÈçºÎËõСSQL

Ҳ̸ÈçºÎËõСSQL - Ó¦ÓÃÈí¼þ - µçÄԽ̳ÌÍø

Ҳ̸ÈçºÎËõСSQL

ÈÕÆÚ£º2006-06-04   ¼ö£º
¡¡¡¡Ç°¼¸ÌìÒ²Åöµ½ÈÕÖ¾Îļþ¹ý´óµÄÎÊÌ⣬Êý¾Ý¿âʵ¼Ê´óСΪ600M, ÈÕÖ¾Îļþʵ¼Ê´óСΪ33M, µ«ÈÕÖ¾ÎļþÕ¼ÓÿռäΪ2.8G!!!ÊÔÁ˶àÖÖ·½Ê½£¬SHIRNK DATABASE£¬ TRUNCATE LOG FILE, ¶¼Ã»°ì·¨½«ÎļþËõС¡£ÎÞÂÛÈçºÎ£¬ÕâÓ¦¸ÃËãSQL SERVERµÄÒ»¸öBUG°É¡£ºóÀ´ÕÒµ½ÏÂÃæµÄ´úÂ룬¾Í¿ÉÒÔ½«ÈÕÖ¾ÎļþËõСµ½×Ô¼ºÏëÒªµÄ´óСÁË¡£°Ñ´úÂëCOPYµ½²éѯ·ÖÎöÆ÷À£¬È»ºóÐÞ¸ÄÆäÖеÄ3¸ö²ÎÊý(Êý¾Ý¿âÃû£¬ÈÕÖ¾ÎļþÃû£¬ºÍÄ¿±êÈÕÖ¾ÎļþµÄ´óС)£¬ÔËÐм´¿É(ÎÒÒѾ­Óùý¶à´ÎÁË)-----SET NOCOUNT ONDECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INTUSE Marias -- Òª²Ù×÷µÄÊý¾Ý¿âÃûSELECT @LogicalFileName = 'Marias_log', -- ÈÕÖ¾ÎļþÃû@MaxMinutes = 10, -- Limit on time allowed to wrap log. @NewSize = 100 -- ÄãÏëÉ趨µÄÈÕÖ¾ÎļþµÄ´óС(M)-- Setup / initializeDECLARE @OriginalSize intSELECT @OriginalSize = size FROM sysfiles WHERE name = @LogicalFileNameSELECT 'Original Size of ' db_name() ' LOG is ' CONVERT(VARCHAR(30),@OriginalSize) ' 8K pages or ' CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) 'MB' FROM sysfiles WHERE name = @LogicalFileNameCREATE TABLE DummyTrans (DummyColumn char (8000) not null)DECLARE @Counter INT, @StartTime DATETIME, @TruncLog VARCHAR(255)SELECT @StartTime = GETDATE(), @TruncLog = 'BACKUP LOG ' db_name() ' WITH TRUNCATE_ONLY'DBCC SHRINKFILE (@LogicalFileName, @NewSize)EXEC (@TruncLog)-- Wrap the log if necessary.WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) AND (@OriginalSize * 8 /1024) > @NewSize BEGIN -- Outer loop. SELECT @Counter = 0 WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) BEGIN -- update INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans SELECT @Counter = @Counter 1 END EXEC (@TruncLog) END SELECT 'Final Size of ' db_name() ' LOG is ' CONVERT(VARCHAR(30),size) ' 8K pages or ' CONVERT(VARCHAR(30),(size*8/1024)) 'MB' FROM sysfiles WHERE name = @LogicalFileNameDROP TABLE DummyTransSET NOCOUNT OFF
±êÇ©£º