Also talk about how to shrink SQL Server log files

Source: Internet
Author: User
Tags file size getdate log
Server

A few days ago also encountered a large log file problem, the actual size of the database is 600M, the actual size of the log file is 33M, but the log files occupy a space of 2.8G!!!
Try a variety of ways, Shirnk DATABASE, TRUNCATE LOG file, there is no way to reduce the file. Anyway, this should be a bug in SQL Server.

Then find the following code, you can reduce the log file to the size you want. Copy the code into the Query Analyzer, and then modify the 3 parameters (database name, log file name, and target log file size) and run it (I've used it many times)
-----
SET NOCOUNT on
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT


use     marias             -the database name to be manipulated
select  @LogicalFileName = ' Marias_log ', --log file name
@MaxMinutes = 10,               -Limit on time allowed to wrap log.
        @NewSize = 100                  -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)


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 ' + 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




Related Article

Contact Us

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.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.