Truncate log file (.ldf) in SQL Server 2008

Question

Dear All,

Please advise how to truncate .ldf file in SQL Server 2008 as size of file is very large..

Database is full recovery model.

---

-- Kind Regards Sandeep

Answer 1

                      There is some way to truncate log files only. with below mentioned way.

1) . Log backup with truncate only.

2) . Detach the database and attache the with out log files. or after detaching the database you can find the mdf, ldf files . So you can rename the log file (ldf) now you can attach with out log file. continuesly  maintain log backup with truncate method. 

  Example : 

                             

<p>ALTER DATABASE pubs SET RECOVERY FULL
BACKUP DATABASE pubs TO DISK = 'C:\x.bak' WITH INIT
BACKUP LOG pubs TO DISK = 'C:\x.bak'
BACKUP LOG pubs WITH TRUNCATE_ONLY
GO
BACKUP LOG pubs TO DISK = 'C:\x.bak' --fails!<span style="font-family:inherit"> </span></p>

------------------------------------------------------------------------

  Regard's | Ramu A | MCTS, MCITP | twitter |

Answer 2

Hi Ramu ,

Thanks for your reply.

So i should run  commands in following order

BACKUP DATABASE pubs TO DISK = 'C:\x.bak' WITH INIT
BACKUP LOG pubs TO DISK = 'C:\x.bak'
BACKUP LOG pubs WITH TRUNCATE_ONLY

will this reduce the size of .ldf file and there will be no issue in full and differential backups.

---

-- Kind Regards Sandeep

Answer 3

HI sandeep,

BACKUP LOG pubs WITH TRUNCATE_ONLY

is not a supported in SQL Server 2008. it works with SQL Server 2000/2005

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 4

 

        Dear sandeep ,

             If you satisfied my reply please mark as Answer  it is very useful for me.

   

------------------------------------------------------------------------

  Regard's | Ramu A | MCTS, MCITP | twitter |

 

Answer 5

Hi Ramu,

Then how can i reduce the size of log file..

---

-- Kind Regards Sandeep

Answer 6

HI did you have replication in the database id yes then run this command

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,     @time = 0, @reset = 1

if replication is not there then.check my blog

http://sqlservr.blog.com/2012/06/26/best-practice-to-shrink-in-logldf-file-in-sql-server/

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 7

Ramu,

In regard to your post:

1) . Log backup with truncate only.

This is no longer supported and while it can be run, results in a NOOP.

Detach the database and attache the with out log files. or after detaching the database you can find the mdf, ldf files . So you can rename the log file (ldf) now you can attach with out log file. continuesly maintain log backup with truncate method.

Don't EVER, EVER, EVER do this to truncate or shrink the log, EVER! This is inviting logical inconsistencies into your database. When crash recovery is run on the database it won't be able to find the log file and issues could most certainly arise.

If you want to resize the log and the database is in the full recovery model, first take a log backup (if needed for recovery purposes - which means ALWAYS) then switch the recovery model from Full to Simple. Issue some checkpoint commands to force the truncation of the log. Use DBCC Shrinkfile on the log to shrink the log down (depending on the number of vlfs and their size this will change what number is actually used) and then manually grow the log back to have a good mix of vlf size and number of vlfs. Once this is done, if the ful recover model is needed, switch to the ful recovery model and immediately take a full or differential backup to restart the lsn chain.

-Sean

---

Sean Gallardy | Blog | Twitter

Answer 8

Hi there,

interesting conversation by sean. He is correct if you do attach an empty file you may lead in to consistency issues at later stage. I took this tip from one of the SQL DBA guru, the bast way is take a log backup to the disk that will clear your transaction log. Delete the file after the completion form the disk if you don't want to keep it. So that SQL server do not miss any thing.

BACKUP LOG [databasename] TO  DISK = N'location'
GO

i am sure you must have excess disk space to hold this file...if your answer is no you should seriously look into your space management.

good luck

kumar

Answer 9

USE [master]
GO
ALTER DATABASE DB_Name SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE DB_Name SET RECOVERY SIMPLE 
GO
USE DB_Name 
GO
DBCC SHRINKFILE (N'DB_Name _LOG' , 10, TRUNCATEONLY)
GO

USE [master]
GO
ALTER DATABASE DB_Name SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE DB_Name SET RECOVERY FULL 
GO

First ,you need to execute BACKUP DATABASE to take a full database backup 
Second, run these statements 
Finally,perform a full database backup because the log chain is broke

Answer 10

but this is not a best practice,

changing the recovery modes of databse will reset the ln number of the database.

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 11

Dear All,

Please advise by running command BACKUP LOG [databasename] TO  DISK = N'location' will it reduce the size of .ldf file.

---

-- Kind Regards Sandeep

Answer 12

Hi Sandeep,

I agree to Sean Gallardy.

Now, First to be able to shrink the log you need to see how much free space you have on the Log File.

First see how much log space is used by running DBCC SQLPERF (LOGSPACE)

If you see there is enough free space and if you really dont need such a big log file, then you can actually use DBCC Shrinkfile command to remove the log file. (Though frequent shrinking is not a good practice)

If you dont have enough free space, then run this command.

select name, log_reuse_wait_desc from sys.databases   

Based on the output you will know what you need to do to shrink the log file. Have a look at this log

http://msdn.microsoft.com/en-us/library/ms345414%28v=sql.105%29.aspx

By taking the log backup you might *NOT ALWAYS* be able to shrink the log. You will only be able to shrink if the actual reason why the transaction log file is not re-usable is due to the lack of proper log backups.

HTH

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

Answer 13

Sandeep,

Taking a log backup with NEVER reduce the SIZE of the log, it will however - if possible - inactive vlfs inside the log file. The only way to reduce the size of a log file is to run DBCC SHRINKFILE on the log. The reason why I am putting the backup log step in is two fold. The first is because you should always have known good backups that can get you back to any point in your recovery needs. The second is that it will inactivate any vlfs that can be which will hopefully be near the beginning of the log file. The switch to the simple recovery model means that the log can be truncated (the vlfs can be inactivated) by different means, one of which is a checkpoint. Once there are no more vlfs at the end of the log that are active, it is possible to shrink the log file to reclaim space back to the OS.

-Sean

---

Sean Gallardy | Blog | Twitter

Answer 14

Ramesh,

It will break the log chain, but that's why a log backup should be taken before the switch and either a full or differential backup after it has been switched back. If left in simple, there would be no point as the log will automatically be truncated.

-Sean

---

Sean Gallardy | Blog | Twitter

Answer 15

Hi

Either run a backup log or do a checkpoint, followed  by a dbcc shrinkfile. once it is done and the file has been set to your desired size, make sure you amend your backup strategy, otherwise it will start growing again.

You should not have to change the recovery model. if you do, you will be breaking any backup/restore strategy.

Make sure you are using the correct recovery model an plan the correct strategy around it.

Regards

Rui

Answer 16

Dear All,

Thanks for your replies.

So it means i should

1. Take log backup

2. Change the recovery model to simple.

3. Then run SHRINKFILE command to reduce to file size on disk.

4. Change back to full recovery model.

5. Then take full backup of database again.

Please advise if above procedure is correct

---

-- Kind Regards Sandeep

Answer 17

Sandeep,

Yes, but add in:

2.1 CHECKPOINT

2.2 CHECKPOINT

By issuing the checkpoint command when the database is in the simple recovery model will allow for the vlfs to (if possible) be inactivated.

-Sean

---

Sean Gallardy | Blog | Twitter

Answer 18

Please advise do i need to give checkpoint duration with CHECKPOINT and how should i user it.

As i haven't used it before.

---

-- Kind Regards Sandeep

Answer 19

Open up a query to the database and execute this twice:

CHECKPOINT

That's it.

-Sean

---

Sean Gallardy | Blog | Twitter

Answer 20

http://www.karaszi.com/SQLServer/info_large_transaction_log_file.asp

---

Tibor Karaszi, SQL Server MVP | web | blog