What happens if we truncate a log file

Question

Hi,
We have a log file of sql server 2005 database and the log file for that is database is 230GB.. what happens if we truncate that file???

Are there any pros or cons or any pitfalls that we would run into if we truncate those files..

Any help will be appreciated...

Thanks,
KAren

Answer 1

What is the recovery model of that database?

SELECT name, recovery_model_desc
  FROM sys.databases
 WHERE name = 'YOUR_DATABASE_NAME'

If FULL, have log backups every been taken?

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 2

The recovery_model_desc is Full...i think they have taken the log back before...

Answer 3

I would see why this log file has become 230GB. possibilities are:

1. you may not have tlog backup scheduled. please check the frequecy of tlog bkp.
2. check the size of the tables and check index.
3. big transactions.

as even if you trncate the log and the above things are their possilities that you will get the same size soon. so if everything is good try to increase the volume.

check thier are somany blogs by Paul and other experts on this.
http://www.sqlskills.com/BLOGS/PAUL/category/Shrink.aspx
http://www.sqlskills.com/BLOGS/PAUL/category/Transaction-Log.aspx

HTH.
Vinay

---

Vinay Thakur http://vinay-thakur.spaces.live.com/ http://twitter.com/ThakurVinay

Answer 4

The recovery_model_desc is Full...i think they have taken the log back before...

What is the result of this query?

;with maxcte as
(
SELECT MAX(backup_set_id) backup_set_id
  FROM msdb.dbo.backupset

-- Enter your database name here
 WHERE database_name = 'Test'
)  
SELECT backup_finish_date, type
  FROM msdb.dbo.backupset a
  JOIN maxcte b
    on a.backup_set_id = b.backup_set_id

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 5

The question now is:

What types of backup do you want to do for this database? Do you wan tto perform log backups?

If not, set recovery model to simple, wait a while and then shrink the log file to a reasonable size (details on shrinking log file in here http://www.karaszi.com/SQLServer/info_dont_shrink.asp).

If you do want to take log backups for the database you first need to determine whether it is acceptable to have a broken log backup chain (since your most recent log backup). If it is, then set to simple, shrink to reasonable size, full, do db backup and then log backups. If not, then just do a log backup (which likely will be huge), then possibly shrink and keep doing regular log backups.

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 6

When you truncate the transaction log, you remove your ability to perform a point in time recovery of your database, unless you follow the truncation with a FULL backup and begin to take transaction log backups at an appropriate frequency.  The key thing here is that if you do this, and you change nothing after the fact, you will have a problem again.  If you don't care about point in time recovery, set your database to SIMPLE recovery, and then change the recovery of Model to SIMPLE as well.  The reason I say to change model to SIMPLE is that it is the template used when you create a new database, and changing it makes it so all new databases are created with SIMPLE recovery.

In addition, if your log has grown on its own to be 230GB, you probably have a problem with excessive virtual log files (VLF's).  You can see this by running DBCC LOGINFO.  If you have more than 100 VLF's, you need to resize the log manually to minimize the VLF count.  You can do this by switching to SIMPLE recovery, shirnking the log file using DBCC SHRINKFILE, and then growing the Log file in 8GB chunks to the size necessary for your application.  This results in 16 VLF's that are 512MB in size for each of the 8GB chunks you add to the file.

I'd recommend that you read the Transaction Log posts on Kimberly Tripps blog which covers the above information as well as lots of other important information surrounding the transaction log:

http://sqlskills.com/BLOGS/KIMBERLY/category/Transaction-Log.aspx

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 7

Hi,

If you not worried about transaction log backup and want to quickly recover space...., sometime it looks impossible to shrink the Truncated Log file... so go for:

a. Detach the database

b. Rename the log file

c. Attach the database without the log file

d. Delete the log file

e. Set the database recovery to 'simple'

__________________________
Eyal.

 

 

 

Answer 8

Hi,

If you not worried about transaction log backup and want to quickly recover space...., sometime it looks impossible to shrink the Truncated Log file... so go for:

a. Detach the database

b. Rename the log file

c. Attach the database without the log file

d. Delete the log file

e. Set the database recovery to 'simple'

__________________________
Eyal.

NEVER EVER DO THIS TO A PRODUCTION DATABASE.  THIS IS THE WORST POSSIBLE RECOMMENDATION THERE IS!!!


There is always another way to fix this problem.  Your recommendation is against all established best practices.  You should never separate the log file from the database.  Should something occur where the database is left in a dirty state, the log is required for Roll Forward/Rollback operations to make the database consistent.  If it can't do this you have a corrupt database.

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 9

<<NEVER EVER DO THIS TO A PRODUCTION DATABASE.  THIS IS THE WORST POSSIBLE RECOMMENDATION THERE IS!!!

I agree with Jonathan. We see countless of posts in various forums with inaccessible database because the log file as "lost". Jus because SQL Server might be able to re-create the log file in some circustances doesn't mean it always can. I like to think of deleting ldf files as playing Russian roulette. I don't do that.

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 10

Looking at the log size, I think the production environment is prone to have IO problems. There could have been lot of logical file fragmentation of transaction log. Please check he log fragmentation and take steps fast to reduce the size and have better maintenance. Below is what I mean the better maintenance of database and log based on the projected/anticipated database size:

1. Schedule full database backup at specific intervals (depending on DB size/backup duration/acceptable data loss/Restoring the environment in case of disauster) Assuming 3 days for each full DB backup
2. Have incremental transactional log back up. Have appropriate sizing in place to make sure that the transaction log file doesnt grow HUGE in size. The database sizing comes into picture while doing capacity planning
3. Ensure that the log file auto growth is not enabled. IT would be better to be handled manually/auto growth to specific size based on the database sizing that is done during capacity planning. This will help in controlling the transaction log growth and consequences becuz of the log file growth.

These are few points that I can think of in the current context. HTH

Regards,

---

Phani Note: Please mark the post as answered if it answers your question.