Clearing transaction logs

• v_krishnan

  SSChasing Mays

  Points: 616

  More actions

  August 6, 2003 at 1:12 am

  #81365

  Hi,

  How can I clear the transactions logs? Basically I want to free up the space used by the ldf files.

  Thanks,

  Krishnan

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  August 6, 2003 at 1:19 am

  #468753

  Hi Krishnan,

  quote:

  ---

  How can I clear the transactions logs? Basically I want to free up the space used by the ldf files.

  ---

  take a look at BOL for transaction logs -> shrinking or truncating.

  Gives an explanation about how, why and what

  Cheers,

  Frank

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• skasarla

  Mr or Mrs. 500

  Points: 549

  More actions

  August 6, 2003 at 9:54 am

  #468860

  Hi Krishnan,

  Shrinking transaction Log is an iterative process as the active transaction log may be present. I have successfully used the following sequence of steps to backup and then shrink the TLog.

  ========================================

  BACKUP LOG dbname WITH TRUNCATE_ONLY

  GO

  CHECKPOINT

  GO

  DBCC SHRINKFILE ('file_name', size in MB)

  go

  --- It should work here, if not given back any space from the ldf then repeat the steps again.

  BACKUP LOG dbname WITH TRUNCATE_ONLY

  GO

  CHECKPOINT

  go

  DBCC SHRINKFILE ('file_name', size in MB)

  GO

  By now it should force the log entries to disk and freeup dirty buffers/pages to free the space on the physical Log file.

  Have a good one

  -Sravan

• MegaDBA

  Hall of Fame

  Points: 3518

  More actions

  August 7, 2003 at 7:32 am

  #468982

  Also, what version of SQL Server are you running? If you are running SQL 7.0, there is a known issue regarding shrinking the transaction logs in this version. If you are running 7.0 read this:

  http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/Articles/q256/6/50.asp&NoWebContent=1

  If you are running 2000 you can use what skasarla gave you which is clean and to the point. Or you can use the Enterprise Manager GUI to shrink the dB files as well. Good luck!

  Aurora1

  
  

  Aurora