Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

The "sql server database transaction log file" files *.ldf are consuming GBs of space on my hard drive. Is there are way to clear them off when needed? I don't run live database on my PC and i use my db only while development and testing. So, these transaction logs don't make sense to be persistent. Or, can i relocate this log path to some other drive as i don't have any space on my C drive to hold this.

many thanks!

share|improve this question

migrated from superuser.com Feb 16 '10 at 16:40

This question came from our site for computer enthusiasts and power users.

3 Answers 3

Do you need the ability to restore a database to any point in time, or is restoring only to the time the backup occurred good enough? If the later, then set the database(s) to use the Simple Recover Model. Once the model has been set to simple, you can then use the DBCC SHRINKFILE command to shrink the ldf files back to only a couple MB.

However, if you need point-in-time restore capability, then you'll need to start taking regular Full AND transaction log backups. The full backups are as you expect. The transaction log backups will copy the data from the transaction log to the backup, then truncate the transaction log. Once your logs are being backed up, you can go back with the SHRINKFILE command and shrink the transaction logs back to a reasonable size. You can read this article for a little more info on how to use maintenance plans to schedule your transaction log backups.

share|improve this answer
    
Good and complete answer. –  mfinni Feb 16 '10 at 17:47
    
@Evan: I changed the model to Simple, but not seeing any change in the disk space. When does the change take effect? –  pencilslate Feb 16 '10 at 17:59
2  
@pencilslate - did you also run DBCC SHRINKFILE? –  mfinni Feb 16 '10 at 18:01
    
@mfinni: Nope. Should i? –  pencilslate Feb 16 '10 at 18:11
1  
@pencilslate - Yes, you will need to use the DBCC SHRINKFILE command before any disk space is returned. This is by design, so that the size of the log file can remain relatively static (particularly when using a full recovery model), and reduce the I/O caused by the log file. –  Evan M. Feb 16 '10 at 19:14

You can change the database to the Simple recovery model. The default of Full recovery model will only reduce the transaction logs after a full DB backup has been taken. If you have SQL Server 2000, you can also use the BACKUP LOG log_name WITH NO_LOG command which will mostly empty the log ready for shrinking.

share|improve this answer

Maybe you can just try to Shrink them. MS Sql allows to Shirnk Files and Databases also. In SQLMGM studio right click on database and inside tasks you should have option Shirnk. Choice Database and see will it fit your needs for Free space.

alt text

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.