Friday, February 13, 2009

SQL Server: How to reduce LDF file size

Hi...

I use SQL Server 2005 in my development server and the Database files (.MDF & .LDF) were in C drive. As we were working on the development the LDF (Transaction log files) increased drastically.
So i thought its times to reduce it or later it might make the system slow or space crunch would be there.

The tried following options to reduce the LDF file:

1. Right click on Database -> Tasks -> Shrink -> Files -> Choose File type : Log > Selected Shrink action : Release unused space & later Reorganize pages, Both did not help as the log was more than 1 GB whihc is not so much because i have seen some people had it more 40 GB when log file was not monitored frequently.

2. Used DBCC shrinkfile some time before, which was also not helpful.

Then found from the forums & did the following which worked:

Steps:
1. Make the database offline.
2. Right click & did 'Detach...' for the respective database.
3. Went to the file system where the LDF file resides and moved to a safer place.
4. Then on databases folder (below the Server icon), did right click & clicked Attach.
5. In Attach database window, selected the database & attached it without LDF file. So that system automatically created a new one which is of very small size :)

Thus the issue was solved. I believe there are other options as well to reduce LDF files,
If you are aware of any other options feel free to write it down :)

[Update: July 12 2009]

Now there seems to be much more easier way to reduce LDF file :)
http://mohammedfauzi.blogspot.com/2009/07/moss-sharepointconfig-ldf-file-grows.html

Thanks
Fauzi

15 comments:

Anonymous said...

Hey this was really useful in reducing the space occupied by the Archive. Thanks!

Anonymous said...

Thank you very much for this information. It worked great!

Anonymous said...

Thank you very much for this information. It worked great!

Anonymous said...

Muchas gracias !

Anonymous said...

Great tip. Now if you could only post on how to optimize the size of the mdf file too.

Fauzi said...

Now there seems to be much more easier way to reduce LDF file :)

http://mohammedfauzi.blogspot.com/2009/07/moss-sharepointconfig-ldf-file-grows.html

Anonymous said...

Hi. This tip has helped me a lot. Thank u

ajit said...

Great Tip...It worked for me...But what might be the reason for increase in ldf file..In my case it was around 19 GB... Can't we reduce automatically the ldf files??? IS there any script which we can use to schedule the automatic reduction of ldf file?
Thanks in advance
Ajit

ajit said...

Great Tip...It worked for me...But what might be the reason for increase in the size of ldf..Is it possible to reduce the size of ldf autmatically...

Thanks in Advance
Ajit

Fauzi said...

@Ajit:

You can schedule the query given here....

http://mohammedfauzi.blogspot.com/2009/07/moss-sharepointconfig-ldf-file-grows.html

Hope it helps

Regards Fauzi

Unknown said...

thanks fauzi bhai


manish

Anonymous said...

Hi, how safe is this? I mean, what will I lose if I reset my LDF file back to start? Are there risks?

Thanks.

Anonymous said...

Thank you Very much it really helped allot..

Anonymous said...

If you delete ldf files won't it corrupt the actual database when re-attaching?

Fauzi said...

from my observation the db is working fine. any ways you backup .ldf file for backup.