Log file

• markwiowavet

  SSC Rookie

  Points: 41

  More actions

  July 16, 2004 at 1:44 pm

  #83322

  Hi

  What I have is sql server 2000 sp3 on an win2000.   I have a log file that is 10G and I can not get it to shrink back to normal size, which is about 500m.  Here is what I have done.

  1. Reboot the server.

  2. BACKUP LOG [Inv] WITH TRUNCATE_ONLY.

  3. DBCC SHRINKFILE (N'Inv_Log')

  Any ideas? 

  Thanks

  Mark

• Yelena Varshal

  SSC-Dedicated

  Points: 34286

  More actions

  July 16, 2004 at 2:47 pm

  #514956

  Mark,

  See

  http://www.bstconsultants.com/tsug/Dec99/REDUCING_THE_SIZE_OF_THE_LOG_FILE.htm

  Yelena

  Regards,Yelena Varsha

• Steven.

  SSCrazy Eights

  Points: 9806

  More actions

  July 17, 2004 at 1:58 am

  #514976

  The other way would be to

  1. backup the database (a must).

  2. Detach the database (using EM or sp_detach_db)

  3. Move the log file elsewhere eg d:\temp

  4. Attach the database (using EM or sp_attach_single_file_db)

  5. Once you are happy, delete the old log file

  Note: When using EM ensure you choose the correct owner for the database)

  This way SQL Server recreates the log file (1MB in size)

  Steven

• Derrick Leggett

  SSCrazy

  Points: 2778

  More actions

  July 17, 2004 at 3:01 pm

  #514984

  I don't know why it has to be this complicated.  Change the database to Simple recovery mode.  Run DBCC Shrinkdb a couple times.  Switch to full recovery mode, do a full backup, and resume business.

  Derrick Leggett
  Mean Old DBA
  When life gives you a lemon, fire the DBA.