Solved

truncate SQL 2008 database log file

Posted on 2016-10-21
3
67 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 26

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
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…

730 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