7、日志清除</p> SET NOCOUNT ON
$ @4 G0 N! d& w& T' K6 J5 V DECLARE @LogicalFileName sysname,
9 [/ u% Z: c! j: Z( ?1 H1 v @MaxMinutes INT,
* ?5 h$ q% K4 T% G5 T4 M! [ @NewSize INT
0 {! {; M6 R5 u$ i+ c USE tablename -- 要操作的数据库名
8 }7 q' _ h+ L. W, }9 t) Y0 E SELECT @LogicalFileName = 'tablename_log', -- 日志文件名
/ V& M: _* Z4 O9 ^& n @MaxMinutes = 10, -- Limit on time allowed to wrap log.
9 z, n5 A) O# t2 V @NewSize = 1 -- 你想设定的日志文件的大小(M). G/ S4 K% x9 F9 D
Setup / initialize* c( F- ~) h4 i: v! \7 C) Q' R
DECLARE @OriginalSize int0 E( P# D- B; H% S5 e
SELECT @OriginalSize = size9 z7 e% Q C$ u/ h* O! I6 R/ R& ~
FROM sysfiles
! n/ R0 M( ^. i: I; g WHERE name = @LogicalFileName
& _, X, C& M Y( |# V SELECT 'Original Size of ' + db_name() + ' LOG is ' +
3 H# R; [& t, I# I! K5 S8 G* } CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +) { s% E% P6 K
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB', F) N0 a( Y. \3 E1 W
FROM sysfiles( T' `( F7 p+ @3 m' P4 s7 V9 z: e
WHERE name = @LogicalFileName- P3 \0 G0 S2 u1 M7 h
CREATE TABLE DummyTrans: V; r( ]8 l2 g8 l- Q2 L) y
(DummyColumn char (8000) not null)1 U, I/ z9 }0 R/ b ~) |& o
DECLARE @Counter INT,3 u8 l* L/ e. {7 l. U* u" B7 I! e
@StartTime DATETIME,4 B: K* a3 j6 ?" t9 @* G# g& A- k
@TruncLog VARCHAR(255)
. w6 {5 N% @1 }6 h: J& e* T SELECT @StartTime = GETDATE(),
1 J$ K2 l% ?5 a; z- X2 p @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'# }$ s% [* m# Z( F
DBCC SHRINKFILE (@LogicalFileName, @NewSize) Z/ T: Z3 k+ j% v' \7 C3 _
EXEC (@TruncLog): Q! n; [. y% t7 }
-- Wrap the log if necessary.
& K8 I+ S0 z/ O" B/ } WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
1 o8 i" h' E' v6 i3 p" Q. V ?6 a# J% V AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)9 q6 B9 v r/ `; w2 r x' s
AND (@OriginalSize * 8 /1024) > @NewSize
# x2 v) J9 N; ^& g BEGIN -- Outer loop.) z7 C1 h$ Q$ y$ J6 Q
SELECT @Counter = 0
) R% t' P; }. ]! q2 z WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
: k' U" d* P$ n BEGIN -- update
E+ r% J* C; {; \. j/ i INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans8 K2 P. U6 S1 t$ U3 Z% m6 M
SELECT @Counter = @Counter + 1
/ O9 _$ o: @0 v) ~, N9 C END2 q! l5 o! n \( B! E3 A
EXEC (@TruncLog)
: ?% d, V7 p* W7 D' z& B END! z" h: Y! j0 V$ {; u/ \
SELECT 'Final Size of ' + db_name() + ' LOG is ' +4 X& b+ W9 {: }& X4 r* u5 D
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +6 P. C& A) y0 \
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'& v6 `- W' a; S: p0 I( R4 Y
FROM sysfiles |