truncate (log.LDF) file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

truncate (log.LDF) file

Author

Topic

eia72
Starting Member

9 Posts

Posted - 2006-02-22 : 02:30:30

how can i truncate logfile (log.LDF) to a smallest posible size?
please help, im running out of hard disk space

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-22 : 02:37:14

Refer to the sticky http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210. There are quite a number of thread on this topic

----------------------------------
'KH'
It is inevitable

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2006-02-22 : 05:20:55

***************
Method 1
***************

Take transaction log backups. This will remove all inactive entry from the log file. This helps to control the growth in the database log file.

If the database is extensivly used then increase the number of backups for example take transaction log backup in every 10 mins.

***************
Method 2
***************

Changing the recovery model to simple or bulklog will also affect but there are other risks involved in it. Check books online for more information

***************
Method 3
***************

Execute the below command

DUMP TRAN <DATABASE NAME> WITH NO_LOG

This will dump the entire transaction log and create a new one.

Regards
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

dturner
Starting Member

24 Posts

Posted - 2006-10-02 : 13:34:53

Thanks Sachin,
This has helped out another user :)

The world has more information to offer than I can hold in my head

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-10-02 : 14:17:10

it should be noted that once you backup the tlog the datafile does not shrink. You must manually shrink the tlog datafile in order for you to reclaim space in your filesystem.

the other thing to point out is that shrinking your datafiles like this is typically a bad thing to do on a regular basis. To avoid getting into the situation you need to schedule regular tlog backups - hourly, every half hour or whatever.

you also need to evaluate if Full recovery model is appropriate for your database. Sometimes the simple recovery model will suffice - using the simple model eliminates the need for tlog backups. The downside to simple recovery model is that you lose the ability to perform a point in time recovery.

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources