shrinking the log file

• sql_2005_fan

  SSCommitted

  Points: 1871

  More actions

  August 17, 2006 at 8:34 am

  #114610

  I have database abcd and its data file is 100 MB and its log file is 4 GB. I took the transaction log backup of the database and ran the following command

  DBCC SQLPERF(logspace)

   

  The result shows that only 2% of the log space used. So I want to shrink the log file to 200 mb.

  So I am using the following command

   

  DBCC SHRINKFILE (abcd_log, 200)

  But its not shrinking the log file. So what should I do to shrink the log file.

  Any help would be appreciated.

   

  Thanks.

• Ken McKelvey

  SSCoach

  Points: 18842

  More actions

  August 17, 2006 at 8:44 am

  #655464

  Try:

  alter database abcd

  set recovery simple

  use abcd

  go

  dbcc shrinkfile(abcd_log, 200)

  -- if originally in full recovery, reset

  alter database abcd

  set recovery full

  You will need to do a full backup afterwards.

   

• sql_2005_fan

  SSCommitted

  Points: 1871

  More actions

  August 17, 2006 at 9:09 am

  #655469

  Thanks, It worked like a dream.