Shring the LOG file.

• Julian Milano

  SSC Veteran

  Points: 250

  More actions

  February 26, 2006 at 7:25 pm

  #112963

  I have the following files on my SQL Server 2000 server:

  Location: D:\Program Files\Microsoft SQL Server\MSSQL\Data

  Database: Test system- mirror of production system database

  Filenames: JLRXTestApp.mdf (data) 6.6GB, JLRXTestApp_log.LDF (Log file) 66GB

  The log file has never been backed up since this is only our test system, thus it has grown to 66GB and is now in danger of filling our SQL server hard disk which also houses our production database.

  I thus need to shrink this log file. I thought I could do it using the following commands:

  -- Select the correct database

  USE JLRXTestAPP;

  -- Set JLRXApp DB size & autogrow values.

  ALTER DATABASE JLRXTestApp MODIFY FILE

   (NAME='JLRXTestApp', SIZE=9216MB, MAXSIZE=UNLIMITED,

   FILEGROWTH=1024MB);

  -- Shrink the log file before setting it to the new size.

  BACKUP LOG JLRXTestApp WITH NO_LOG

  CHECKPOINT

  DBCC SHRINKFILE('JLRXTestApp_log.ldf');

   BACKUP DATABASE JLRXAPP TO

   DISK='F:\BACKUPS\JLRXTest\JLRXTestAPP_LOG_TRUNCLOG.BAK' WITH INIT;

  -- Set JLRXApp Log size & autogrow values.

  ALTER DATABASE JLRXTestApp MODIFY FILE

      (NAME='JLRXTestAPP_Log.ldf', SIZE=1024MB, MAXSIZE=UNLIMITED, FILEGROWTH=512MB);

   

  But when I run the commands above, nothing happens to the LOG file- it's still 66GB! What am I doing wrong? Oh, when I run the commands in Query Analyser, I get the following results:

   

  The command(s) completed successfully.

• Julian Milano

  SSC Veteran

  Points: 250

  More actions

  February 26, 2006 at 7:28 pm

  #623384

  Oh, and I tried to change the Subject line to fix the missplelling of Shrink (from Shring) and the forum doesn't let me- is this a known problem?

• philcart

  SSC-Forever

  Points: 47794

  More actions

  February 26, 2006 at 10:32 pm

  #623393

  If this is just your test database and you don't need to worry about the transaction log for recovery, then I'd do the following.

  1. Set the recovery model of the database to simple.
  2. Detach the database.
  3. Delete the ldf file.
  4. Re-attach the database

  You'll get a warning message at the last step saying the ldf file couldn't be found and a default file is being created.

  Once that is all done, create a weekly SQL Agent job that exectes the following

  use [<<MyDB>>] DBCC SHRINKFILE (N'<<MyDB_Log_Name>>')

  This will keep the size in check.

   

  --------------------
  Colt 45 - the original point and click interface