Solved

truncate SQL 2008 database log file

Posted on 2016-10-21
3
81 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 500 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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

630 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