How to reduce the size of .LDF file?????

The LDF file is the LOG gile.

Before and AFTER reducing it's size, I suggest you backup the entire database.

Now to reduce it using Enterprise Manager:

- Open Enterprise Manager and expand the "databases" leaf in the Server you where the database resides

- Right click on the desired database

- Select All tasks -> Shrink database

- since you only want to shrink the log file, click on "Files..."

- Select the log file

- Select "Compress pages and then truncate free space from the file"

- Click ok

If using transact-SQL, you can use DBCC SHRINKFILE



That's all it takes.

Yours,

Alex

By the way,  here's an example of using DBCC SHRINKFILE to truncate the free space from the log file:

Supposing the Log (.LDF) file is named CDColl_Log.LDF, then the command goes like this:


DBCC SHRINKFILE (CDColl_Log, TRUNCATEONLY )

Yours,

Alex

dear simonet,
     I have tried ur idea, but irealize the physical size of the .LDF file doesn't reduce. Why???

Ok. maybe - just maybe - the file is already in its minimun allocated size. To check the allocated size open Enterprise Manager, Select the server and the database, right click the database and select "Properties"

In the Transaction Log page/tab, check what the Space allocated for this file is. Notice that the file cannot be shrunk to anything smaller than this number.

You can also use this page to determine how the log file is allowed to grow.

Another option (to the one I posted previously), is to entirely clean up the log file. However, before doing so, make you have a fresh backup of your DB.

Here's how to do it using Enterprise Manager:
- Open Enterprise Manager and expand the "databases" leaf in the Server you where the database resides

- Right click on the desired database

- Select All tasks -> Shrink database

- since you only want to shrink the log file, click on "Files..."

- Select the log file

- Select "Shrink file to " and then specify the minimun size allowed.

- click ok.

Yours,

Alex

This utility permit you to reduce in a few minutes the Log file for a database.

Run 3 minutes approximately if doesn´t stop you must stop manually, drop table t1 an see the size of log file.

--
 
dbcc shrinkfile(1,notruncate)
dbcc shrinkfile(2,truncateonly)
create table t1(char1 char(4000))
go
declare @i int
declare @byContador smallint
set @byContador = 1
select @i=0

while @byContador < 10000
 begin
   while @i<10000
     begin
       insert into t1 values ('a')
       select @i=@i+1
     end
   truncate table t1
   backup log tempdb with truncate_only
  set @byContador = @byContador + 1
 end
go  
drop table t1

Hi,

The quickest way to shrink a log file is to (carefully) detach the database, delete/move/rename the log file, and reattach the database when SQL will create an empty log file.

Regards
  David

RUN THE(MY)UTILITY IS QUITE SAFE.....

This utility automatically forces checkpoint to database and recalculates the logfile size at the same time.

Hi,

gchavezr:
On occassions this doesn't result in the log file shrinking immediately, and maybe not all the way back to the original size.

I try this when my clients don't have the time to let me detach the database.

Regards
  David

Hi gchavezr,

I checked out the link - thanks.

Like I said I've used both. Perhaps not with the shrinkfile lines at the top. I've taken a note of that and will try that next time.

Thanks for your input.

Regards
  David

Was a pleasure, my friend

gchavezr

Hi Kelvsat,
I hope ur issue is solved by now with the above recommendations. If not read below...

Explanation:
The technique explained above doesn't work in all the cases like it happened with me.  If the database ur talking abt is using Replication(Publisher) and ur log file size is shooting-up, then its bcoz of some error in Replication.

Solution: (issue this command from Profiler)
Backup log Truncate_only

If it gives an error saying that pending replication exists... Use the following command to reset these pending replication. With this you can manually tell the server that a transaction has been replicated.

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 1

and then go ahead Take a Full Backup(during which the log will be cleared) and Shrink the DB thru Enterprise Manager.

WOW !!! It worked...my 50MB DB bcame 1GB in a day...and this article is the result of that...

Rgds
Sukesh

Sorry it's not at my case.

Is your database running in a production environment?