Large LDF file of an inactive DB

Question

Post reply

Large LDF file of an inactive DB

Dinu E Anastasiu

Ten Centuries

Points: 1177
More actions
February 8, 2006 at 1:04 pm

#170575

SQL Server 2000 on Windows 2003

We have a large DB (1.4GB) with an LDF file of 1.8 GB.

We deleted 1 mil. rows and then stopped updating it. We continued running the maintenance plan which saves the database weekly and the transactions daily, in the hope that this will bring down the size of the LDF file. It doesn't.

What else can I do to reduce the size of the LDF file?

Thanks,

Dinu

Viewing 6 posts - 1 through 5 (of 5 total)

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

TriggerMe SSChampion Points: 11752 More actions · Answer 1

TriggerMe

SSChampion

Points: 11752

February 8, 2006 at 3:18 pm

#619646

One way to do this is to DETACH the Database and ATTACH it again with only the mdf file that way it will create a new ldf with a size of 2 MB.

See BOL for sp_attach_single_file_db

Kindest Regards,

Jeswanth Jaishanker SSCommitted Points: 1869 More actions · Answer 2

Jeswanth Jaishanker

SSCommitted

Points: 1869

February 9, 2006 at 3:04 am

#619709

Hi,

Run teh Following commands

BACKUP LOG <DBNAME> WITH TRUNCATE_ONLY

DBCC SHRINKFILE (<DB_LOG FILE NAME>, 2)

This Should reduce the file to 2 MB The Normal Log backup wont Truncate the File. else you can change the recovery model to simple shrink the file.

Jeswanth

--------------------------------

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 3

Log files will only shrink up to the last active log chunk.

so it may be needed to run the shrink-file x-times.

-- Shrink_TrxLog.SQL

--INF: How to Shrink the SQL Server 7.0 Transaction Log

-- take a look at http://support.microsoft.com/support/kb/articles/q256/6/50.asp?id=256650&SD

--

-- select db_name()

-- select * from sysfiles

USE Your_Database_Name -- This is the name of the database for which the log will be shrunk.

go

SET NOCOUNT ON

DECLARE @LogicalFileName sysname,

@MaxMinutes INT,

@NewSize INT

-- *** MAKE SURE TO CHANGE THE NEXT 3 LINES WITH YOUR CRITERIA. ***

SELECT @LogicalFileName = 'Your_log', -- Use sp_helpfile to identify the logical file name that you want to shrink.

@MaxMinutes = 10, -- Limit on time allowed to wrap log.

@NewSize = 100 -- in MB

-- Setup / initialize

DECLARE @OriginalSize int

SELECT @OriginalSize = size -- in 8K pages

FROM sysfiles

WHERE name = @LogicalFileName

SELECT 'Original Size of ' + db_name() + ' LOG is ' +

CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +

CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'

FROM sysfiles

WHERE name = @LogicalFileName

CREATE TABLE DummyTrans

(DummyColumn char (8000) not null)

-- Wrap log and truncate it.

DECLARE @Counter INT,

@StartTime DATETIME,

@TruncLog VARCHAR(255)

SELECT @StartTime = GETDATE(),

@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'

-- Try an initial shrink.

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) -- the log has not shrunk

AND (@OriginalSize * 8 /1024) > @NewSize -- The value passed in for new size is smaller than the current size.

BEGIN -- Outer loop.

SELECT @Counter = 0

WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))

BEGIN -- update

INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.

DELETE DummyTrans

SELECT @Counter = @Counter + 1

END -- update

EXEC (@TruncLog) -- 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 DummyTrans

PRINT '*** Perform a full database backup ***'

SET NOCOUNT OFF

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Youshen Grasshopper Points: 14 More actions · Answer 4

Hi

Change your recovery option to Simple this will prevent the log from capturing all the data and filling up

this option should only be used if there is no transactional backup required or point in time recovery

or you could

use the following command to shrink the log file

sp_helpdb <DBNAME>

use <DBNAME>

backup log <DBNAME> WITH NO_LOG

dbcc shrinkfile (<DBLOGFILENAME_log>,0)

Regards

YRN

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 5

"Change your recovery option to Simple this will prevent the log from capturing all the data and filling up" ....

Nop.

It will depend on your sql-transaction-size. The log will expand if it needs to do so for capturing a huge transaction (for integrity sake). The shrink afterwards will not be automatic.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me