Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to reduce SQL Server 2008 transaction log file

Posted on 2015-02-18
5
Medium Priority
?
212 Views
Last Modified: 2015-02-23
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.
0
Comment
Question by:Byas_Saha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 23

Accepted Solution

by:
Steve Wales earned 1400 total points
ID: 40618188
Have a read of this article: http://www.experts-exchange.com/Database/MS-SQL-Server/A_11077-How-to-shrink-a-bloated-log-file.html

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

Alternatively:

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 ....
0
 
LVL 15

Expert Comment

by:Vikas Garg
ID: 40618267
Hello,

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
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40618535
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.
0
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 600 total points
ID: 40620498
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.
0
 

Author Closing Comment

by:Byas_Saha
ID: 40627339
Thanks to everyone who replied. I used Steve Wales's solution to solve my issue. Thanks again.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question