truncate Log

• sahana

  SSCrazy

  Points: 2530

  More actions

  May 26, 2005 at 2:49 pm

  #66285

  when I execute  DBCC ShrinkFILE I am getting the following results.

  DbId  Field CurrentSize MinimunSize Usedpages Estimated Pages

  5       2     166712      128            166712      128

  I took a transactional bakup before doing the above step and I took a transactional backup again afther the above step.

  SO when I run the following command

  DBCC SQLPERF(LOGSPACE)

  It showz Logsize as 2000 MB and Log Space used as 5.541523

  I wanted to reduce the log size to 200 MB. How can I do that.

  Thnaks.

• noeld

  SSC Guru

  Points: 96590

  More actions

  May 26, 2005 at 3:02 pm

  #561338

  dbcc shrinkfile (database_log_file_name, 200)

   

  
  * Noel

• Yelena Varshal

  SSC-Dedicated

  Points: 34286

  More actions

  May 26, 2005 at 3:14 pm

  #561342

  Do not forget that when you backup or truncate log active transactions are not truncated.  Do

  DBCC LOGINFO

  What is the status? Is there a lot statuses that are not 0? If yes, then go to Current Activity window and see who is connected to the database. I had exectly the case last week and I found out that the developer who could not shrink log still had a hanged job with long-lasting transaction running. We stopped the job, most of the statuses in DBCC LOGINFO reset itself from 2 to 0 and we were able to shrink the log.

  Yelena

  Regards,Yelena Varsha

• Leo Leong

  SSCommitted

  Points: 1615

  More actions

  May 27, 2005 at 12:45 am

  #561371

  you can use this command:

  BACKUP LOG db_name WITH TRUNCATE_ONLY

  DBCC SHRINKFILE (log_file_name, file_size)

  this will truncate your log file as you want.

  however, please check Books Online for detail description regarding WITH TRUNCATE_ONLY.

  Leo

• barsuk

  SSChampion

  Points: 12280

  More actions

  May 27, 2005 at 12:07 pm

  #561568

  You might want to run DBCC UpdateUsage (Your DB ) after that.