How to reduce TLog size?

Question

How to reduce TLog size?

ads

Newbie

Points: 4
More actions
December 17, 2003 at 12:04 pm

#76098

I am on SQL 7.0.
I have a Tlog of 450 MB, out of which only 20 MB is being used.
It is set to autogrow & so I guess at one point of time the TLog grew to 450 but now it is back to 20 MB.
I took a log backup with BACKUP LOG Test WITH TRUNCATE_ONLY
& then tried to shrink the log file with DBCC SHRINKFILE (test_Log,truncateonly)...
but none of these had any affect on the log size.
i did a dbcc loginfo & it showed me 194 rows (VLFs)all with fileid 2.
there r no current processes on the database.
if i run a DBCC OPENTRAN('Test')
i get
Server: Msg 7969, Level 16, State 1, Line 1
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I am confused. since there are no open or current processes, why are there 194 VLFs?
& what is in the T Log that is not being truncated??
Any help is appreciated!

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

You need to wrap the active point of the log back to one of the first VLFs before you can delete subsequent VLFs. See the following article for an explanation and script to free up the later VLFs:

http://support.microsoft.com/?id=256650

--Jonathan

ads Newbie Points: 4 More actions · Answer 2

Thank you for your response!

I tried running the following script & it worked for me !:)

-- Script downloaded 12/18/2003 3:17:58 PM

-- From The SQL Server Worldwide User's Group (www.sswug.org)

-- Scripts provided AS-IS without warranty of any kind use at your own risk

-- dba3_Shrinking_MsSqlServer7_0_Logs_demo

-- occasionally log files need to be reduced to free up disk space.

-- However, if you run a DBCC SHRINKFILE , BACKUP LOG, the log may not

-- truncate if there are open transactions near the end of an active

-- portion of the virtual log file. For Log information execute:

DBCC LOGINFO('Pubs')

-- 1

-- The minimum recovery LSN (log sequence number) in the log has status = 2;

-- this prevents the log file from reducing in size. The following steps

-- will prompt a shrink process to proceed somewhat more promptly.

DBCC SHRINKFILE ('DBLogFile', TRUNCATEONLY)

BACKUP LOG 'DBName' WITH TRUNCATE_ONLY

-- 2

-- Create a dummy table execute dummy inserts to move the active VLF

CREATE TABLE DummyTable (

DummyColumn VARCHAR(10)

, PK INT )

INSERT DummyTable (PK) VALUES (1)

GO

-- 3

-- Insert into DummyTable to create transactions:

SET NOCOUNT ON

DECLARE @vi INT

SELECT @vi = 0

WHILE (@vi < 50000)

BEGIN

UPDATE DummyTable

SET DummyColumn = DummyColumn

WHERE PK = 1 /* Some criteria to restrict to one row. */

SELECT @vi = @vi + 1

END

SET NOCOUNT OFF

-- 4

-- Once the log has been truncated, portions near the front of the

-- log may be cycled and reused. The dummy transactions, allow SQL Server to

-- reuse the "dead" space at the beginning of the log, instead of "growing"

-- the log file. The Min LSN with a staus = 2 will then shift to the unused

-- portions of the log file. The VLFs should then be marked as unused and

-- be deleted following a DBCC SHRINKFILE & BACKUP LOG. VLF removal is what

-- actually decreases the physical log file size. Re-execute shrinkfile / truncate:

DBCC SHRINKFILE ('DBLogFile', TRUNCATEONLY)

BACKUP LOG 'DBName' WITH TRUNCATE_ONLY

-- 5

-- Check the size of the log file to determine if the file has shrunk.

-- Re-run the script as may be necessary in order to reduce the size of the logfile.