7、日志清除 SET NOCOUNT ON5 L3 n; {) I! g: o
DECLARE @LogicalFileName sysname,
( ?9 e$ d( n# u1 j4 z) b @MaxMinutes INT,
% {1 @, F5 G* ^5 N @NewSize INT' T7 a5 F: J. y8 M) _& \3 o: N; O
USE tablename -- 要操作的数据库名
8 ?5 v. P0 M% E5 S; Z SELECT @LogicalFileName = 'tablename_log', -- 日志文件名
: \! }# I9 O: r" {9 v' o+ S @MaxMinutes = 10, -- Limit on time allowed to wrap log.
: E* R9 R# H% J( T" x* _1 |" k! R @NewSize = 1 -- 你想设定的日志文件的大小(M). E7 T; i ]! g. D" V2 M3 r
Setup / initialize4 w4 _: n2 @; `% i) [. K8 s
DECLARE @OriginalSize int: \6 ?9 Q5 }) ~+ g4 n
SELECT @OriginalSize = size
3 R$ k9 `$ p4 X9 Z8 |+ s3 x2 \ FROM sysfiles( T$ K$ O) o/ q4 X/ l% ~
WHERE name = @LogicalFileName
- D, T' d k& M, D j& f" c7 Z SELECT 'Original Size of ' + db_name() + ' LOG is ' +
! u2 }% B* G3 A& Y* f3 v7 ~3 A CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
( Y% Q3 W+ Y5 e% {9 O CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
7 `% J6 U2 X" o! [/ | FROM sysfiles3 v }3 m0 E+ O
WHERE name = @LogicalFileName
, `2 f. l* Q! ~$ V" ^" ?5 t# q | CREATE TABLE DummyTrans& W3 f2 f8 q, Y" z
(DummyColumn char (8000) not null)/ f, i# ]) `' m$ i( \+ B H
DECLARE @Counter INT,* p% ]9 A7 R) b6 n
@StartTime DATETIME,
, _2 l- [3 y+ o) b' v) h) e @TruncLog VARCHAR(255), {. N. d3 I9 ^* D3 ^, I
SELECT @StartTime = GETDATE(),3 S; K3 r9 s2 R9 r; l
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'' R" {9 g; A/ p* l% M
DBCC SHRINKFILE (@LogicalFileName, @NewSize)% K: h2 a. V" \
1 D: w- z! o/ H0 j0 |4 P, n$ n EXEC (@TruncLog) |