Solved

truncate SQL 2008 database log file

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL 10 35
query question 12 32
SQL Script to Remove Data from Two Joined Tables 1 19
how to restore or keep sql2000  backups useful... 2 12
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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

808 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