Transaction Logs growing too large

Question

Transaction Logs growing too large

amira

SSChasing Mays

Points: 628
More actions
May 22, 2003 at 10:32 am

#106157

Hi all the SQL 2K Experts,
Hope this is not too simplistic of a question. I have several databases that their LDF files are getting out-of-hand. I tried setting a limit, hoping that it will remove data and add new data while keeping the file within the size limit. But instead, it prevented DTS and other transactions from working with a message of Trans log if Full!!
Shrinking, by definition (removing extra space) doesn't make sence and doesn't help much.
What does everone else do with these files?
Thanks
A.

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

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

Robert Lu SSCrazy Points: 2488 More actions · Answer 1

If you don't backup trans log file, go to EM

database-proterty-option-recovery mode, select simple. Then the trans log file will be truncated and shrinked automatically. If you want to back up trans log file, when it's growing too large, you can run

dbcc shrinkfile(filename, truncateonly) first

then run dbcc shrinkfile(filename).

Try it.

Robert

doggarts SSC Enthusiast Points: 149 More actions · Answer 2

We keep the size of the log down and the recovey easy by setting up a database maintenance task to back up the transaction logs at regular intervals. Depending on the size and activity of the database, this interval varies between 15mins to an hour.

amira SSChasing Mays Points: 628 More actions · Answer 3

Thanks for the feedback. I think I am going to detach the databases and reattach them without using the log file, so a new one will be created. I wonder if checking the auto-shrink option will prevent future growth.

A.

parasu Default port Points: 1400 More actions · Answer 4

Hi,

Run the following statements. This would reduce the size of the Log files. It removed all the unused space in the log file.

Rename the databasenames from Northwind to ur corresponding database whose ldf file is huge.

*********

use Northwind

go

DBCC SHRINKFILE ( Northwind_Log , TRUNCATEONLY )

BACKUP LOG Northwind WITH TRUNCATE_ONLY

GO

--drop table mytable

CREATE TABLE MyTable (

MyField VARCHAR(10)

, PK INT )

INSERT Mytable (PK) VALUES (1)

go

SET NOCOUNT ON

DECLARE @index INT

SELECT @index = 0

WHILE (@Index < 200)

BEGIN

UPDATE MyTable

SET MyField = MyField

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

SELECT @index = @index + 1

END

SET NOCOUNT OFF

GO

DBCC SHRINKFILE ( Northwind_LOg , TRUNCATEONLY )

BACKUP LOG Northwind WITH TRUNCATE_ONLY

**********

Regards

Parasu Raman

Regards
Parasu Raman