How to reduce SQL Server 2008 transaction log file

Can someone please advise?

I have a small database running on SQL Server 2008 Std in a desktop. 4-5 users use the database everyday during the week. Now, the hard drive space is reducing rapidly on the desktop. After looking at C Drive, I found the Log file has become too large ( close to 200GB). Now, I do a full backup of the database every night to an external hard drive attached to the desktop computer. I need to get the space back on that computer. Can someone please advise what would be the best way to do that?

- if I can reduce the LOG file with/without taking down the database?
- since I have full backup, can I just start refresh the Log file?

Thanks in advance.
Who is Participating?
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
Have a read of this article:

Your log file will grow until you take a log backup.


Swap to Simple Recovery Mode
Shrink Log file
Switch back to Full Recovery Mode
Take immediate full backup
Start taking regular log backups

If you're not backing up the transaction log, then it will just keep on growing ....
Vikas GargBusiness Intelligence DeveloperCommented:

1) Convert the Recovery Model to Simple Recovery

2) Start Taking Transaction Log Backup

    Create Sample Database in FULL RECOVERY Model
    Take Full Backup (full backup is must for taking subsequent backup)
    Repeat Following Operation
        Take Log Backup
        Insert Some rows
        Check the size of Log File
    Clean Up
Vitor MontalvãoMSSQL Senior EngineerCommented:
I do a full backup of the database every night to an external hard drive attached to the desktop computer
Full backup do not clean the transaction log file. You need to add to your backup plan a job that runs periodically (every hour or two) so it can truncate the transaction log.
Take in consideration that having a database in Simple Recovery model doesn't allows you to restore to a point in time. You can only do that with Full Recovery model.

NOTE: After the first transaction log backup you can shrink the file.
Alpesh PatelConnect With a Mentor Assistant ConsultantCommented:
For shortest solution.

Detach your database.
Delete/rename log file
attach database (Remove missing Log file)
Attach process will create new log file for you.
Byas_SahaAuthor Commented:
Thanks to everyone who replied. I used Steve Wales's solution to solve my issue. Thanks again.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.