Backup | stored Procedures
SET NOCOUNT on
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
Use "ABC"--the name of the database to manipulate
SELECT @LogicalFileName = ' Abc_log ',--log file name
@MaxMinutes =--Limit on time allowed to wrap log.
@NewSize = 500-the size of the log file you want to set (M)
--Setup/initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
From Sysfiles
WHERE name = @LogicalFileName
SELECT ' Original Size of ' + db_name () + ' LOG is ' +
CONVERT (VARCHAR, @OriginalSize) + ' 8K pages or ' +
CONVERT (VARCHAR (@OriginalSize *8/1024)) + ' MB '
From Sysfiles
WHERE name = @LogicalFileName
CREATE TABLE Dummytrans
(Dummycolumn char (8000) NOT NULL)
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 ' + db_name () + ' LOG is ' +
CONVERT (VARCHAR (), size) + ' 8K pages or ' +
CONVERT (VARCHAR (), (size*8/1024)) + ' MB '
From Sysfiles
WHERE name = @LogicalFileName
DROP TABLE Dummytrans
SET NOCOUNT off
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.