CREATE PROCEDURE Sp_ShrinkLog
(@LogFvarchar(200), @NSize INT)
AS
--BY ELI L.
-- DATE 08/2002
-- DESC: attempt to shrink the log file to a specific size
--gets logical file name and attempt size
SET NOCOUNT ON
DECLARE @MaxMin INT, @LogicalFileName sysname,@origs int
DECLARE @Cnt INT,@StartTime DATETIME, @Truncl varchar(255)
SELECT @LogicalFileName = @LogF
SELECT @MaxMin = 2
SELECT @origs = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size OF ' + db_name() + ' LOG IS ' +
CONVERT(varchar(30),@origs) + ' 8K pages or ' +
CONVERT(varchar(30),(@origs*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DumpTrn(DumCol varchar (8000) NOT null)
SELECT @StartTime = GETDATE(),
@Truncl = 'BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY'
DBCC SHRINKFILE (@LogicalFileName, @NSize)
EXEC (@Truncl)
WHILE @MaxMin > DATEDIFF (mi, @StartTime, GETDATE())
AND @origs =(SELECT size FROM sysfiles WHERE name = @LogicalFileName)
AND (@origs * 8 /1024) > @NSize
BEGIN
set @Cnt = 0
WHILE ((@Cnt < @origs / 16) AND (@Cnt < 50000))
BEGIN -- UPDATE
INSERT DumpTrn VALUES ('Fill Log') -- Because it IS a varchar field it inserts 8000 bytes.
DELETE DumpTrn
set@Cnt = @Cnt + 1
END-- UPDATE
EXEC (@Truncl) -- See IF a trunc OF the log shrinks it.
END-- OUTER loop
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 = @LogicalFileName
DROP TABLE DumpTrn
PRINT '*** Perform a FULL DATABASE BACKUP ***'
/*
Sp_ShrinkLog 'AMOISRM_Log',1
*/
SET NOCOUNT OFF
GO
用法示例：Sp_ShrinkLog 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\test_P.LDF',10
|
