none
How to shrink LDF file? RRS feed

  • Question

  • I need to shrink the ldf on my Sql Server 2000 DB from 16,000 to 800.  I am trying this:  

    dbcc shrinkfile ( logical file name,target size,truncateonly)

    I get the logical file name from sysfiles

    dbcc shrinkfile ('C:\mssql\data\myDB_log.ldf', 800, truncateonly) 

    I get a message  -- The Identifier that starts with 'C:\mssql\data\myDB_log.ldf'  DBCC execution completed. 

    But the ldf file has not shrunk at all.  What do I need to do ? 

    Thanks


    Rich P

    Monday, December 3, 2012 7:22 PM

Answers

All replies

  • Oh, the DB is also under Replication, so I was thinking of detaching it and just deleting the ldf file and regenerate a new one -- but I don't want to stop replication.   Will the trick above still work?

    Rich P

    Monday, December 3, 2012 7:34 PM
  • This article will explain wat is happening:

    http://rusanu.com/2012/07/27/how-to-shrink-the-sql-server-log/


    "Premature optimization is the root of all evil." - Knuth

    If I provoked thought, please click the green arrow

    If I provoked Aha! please click Propose as Answer

    Monday, December 3, 2012 7:36 PM
  • TRUNCATEONLY is applicable only to data files.

    http://technet.microsoft.com/en-us/library/ms189493.aspx

    Please run transactional backup first then shrink log file without TRUNCATEONLY option.

    Monday, December 3, 2012 7:49 PM
  • TRUNCATEONLY is applicable only to data files.

    http://technet.microsoft.com/en-us/library/ms189493.aspx

    Please run transactional backup first then shrink log file without TRUNCATEONLY option.


    Thank you for your reply. I am getting a little desparate here. The DB is under replication. I need to shrink the log file. Could you give me an example what I need to do?

    Thank


    Rich P

    Monday, December 3, 2012 8:02 PM
  • BACKUP LOG AdventureWorks2012 TO DISK = 'C:\Temp\AdventureWorks2012_Log.bak' GO

    USE AdventureWorks2012

    GO DBCC SHRINKFILE (AdventureWorks2012_Log, 10);



    Monday, December 3, 2012 10:13 PM
  • USE [databasename]
    GO
    DBCC SHRINKDATABASE(N'databasename') --to shrink the database
    GO
    DBCC SHRINKFILE (N'logicalname of the .mdf file' , 0, TRUNCATEONLY) --to shrink data file
    GO
    DBCC SHRINKFILE (N'logical name of the .ldf file' , 0, TRUNCATEONLY)--to shrink ldf file
    GO
    Monday, December 3, 2012 10:44 PM
  • to use these techniques -- does the DB have to be in singlue_user mode first?

    BACKUP LOG AdventureWorks2012 TO DISK = 'C:\Temp\AdventureWorks2012_Log.bak'
    GO
    USE AdventureWorks2012GO
    DBCC SHRINKFILE (AdventureWorks2012_Log, 10);


    -------------------------------------------

    USE [databasename]
    GO
    DBCC SHRINKDATABASE(N'databasename') --to shrink the database
    GO
    DBCC SHRINKFILE (N'logicalname of the .mdf file' , 0, TRUNCATEONLY) --to shrink data file
    GO
    DBCC SHRINKFILE (N'logical name of the .ldf file' , 0, TRUNCATEONLY)--to shrink ldf file
    GO


    Rich P

    Tuesday, December 4, 2012 11:59 PM
  • >Oh, the DB is also under Replication

    Likelyhood is high that you have to stop replication.

    The following blog on the same topic:

    http://www.sqlusa.com/bestpractices2005/shrinklog/

    Probably you don't need the database to be in the FULL recovery mode. If so, put it into the SIMPLE mode.


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Wednesday, December 5, 2012 12:16 AM
    Moderator