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

x
?
Solved

truncate SQL 2008 database log file

Posted on 2016-10-21
3
Medium Priority
?
87 Views
Last Modified: 2016-10-27
hi,

my database's log file is very big now, up to 100G, I am going to use this script to truncate the file. what is the Advantage and disadvantage by use this script? I heard after truncate the Log file, there will be affecting the data recovery, what's that mean?

ALTER DATABASE database SET RECOVERY SIMPLE
DBCC SHRINKFILE('database_log', 0, TRUNCATEONLY)

thanks
0
Comment
Question by:Simon Chen
[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
3 Comments
 
LVL 20

Expert Comment

by:Russ Suter
ID: 41854028
Bloated log files are a very common problem in SQL Server. It comes from having set the recovery mode incorrectly for the backup strategy you are using. Chances are if you have a huge log file then you're not backing up the database. Therefore running the above script will ONLY be an advantage. You'll lose backup history (which apparently you don't have anyway).
0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 2000 total points
ID: 41854482
The only reason why a LOG file will grow indefinitely is the omission of performing log file backups. If a log file is backed up regularly then the space inside the file is released for reuse so it will not grow after a certain point.

If you set the recovery model to simple than the log files CANNOT be backed up but it will be truncated every time a transaction finishes and it is committed so it will never grow.  The last data you can recover will be in this case the last FULL backup. If you do one FULL backup a day in the case of database failure you can potentially loose 1 day worth of data.

The FULL recovery mode is to be able to bring your to a loss at the last minute if you do a log backup every minute. In that case usually you do a FULL backup at a larger interval, DIFF backup at a smaller interval and LOG backup every certain amount of minutes.

If your business is fine with doing one FULL backup a day then your solution is fine, if that is not the case than you either have to do more FULL backups a day, which usually is difficult if the database is large, or go back to FULL recovery and with a backup plan in place that includes DIFF and LOG backups

I recommend the last solution.
0
 
LVL 4

Expert Comment

by:Daniel Jones
ID: 41854840
Try this
  • Backup database
  • Detach database
  • Rename Log file
  • Attach database
  • New log file will be recreated
  • Delete Renamed Log file.

You can change recovery model from full to simple. This will prevent log bloat.

Alternatively

ALTER DATABASE ExampleDB SET RECOVERY SIMPLE
DBCC SHRINKFILE('ExampleDB_log', 0, TRUNCATEONLY)

Open in new window

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

722 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