Solved

truncate SQL 2008 database log file

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Webservices in T-SQL 3 31
Simple SQL query from two tables 13 54
date diff with Fiscal Calendar 4 30
Loops and updating in SQL Query 9 29
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
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…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

773 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