DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Truncate Log ?

  1. #1
    Ruaidhri Guest

    Truncate Log ?


    I have been looking at the size of one particular database and log file over
    the
    past 3 weeks. The database file has been growing continually (over 1000MB
    within the last 10 days alone) but the log file has not changed size from
    380MB.

    I have backed up the log file and i have also executed the following statements,

    BACKUP LOG db1 WITH TRUNCATE_ONLY
    BACKUP LOG db1 WITH NO_LOG,

    but when i execute sp_helpfile the log file does not appear to have changed
    in
    size.

    Do I have to execute dbcc shrinkfile before the Log file will decrease in
    size??
    I was under the impression that i would see a reduction in size when i truncated
    the log !!


    Thanks a million,
    Ruaidhri

  2. #2
    Michael Levy Guest

    Re: Truncate Log ?

    Truncating the log does not reduce the physical file size. To do that you
    will have to use DBCC SHRINKFILE. Truncating the log removes the inactive
    portion of the transaction log, those transactions that have already been
    terminated through commit or rollback. Normally this information is retained
    in the event that the database must be restored from backup.

    You might want to check out the following topics in the SQL Server BOL:
    Virtual Log Files and Shrinking the Transaction Log,

    -Mike
    --
    Michael Levy MCDBA, MCSD, MCT
    michaell@gasullivan.com



  3. #3
    Colin McGuigan Guest

    Re: Truncate Log ?

    Michael Levy <michaell@gasullivan.com> wrote in message
    news:3a40b875@news.devx.com...
    > Truncating the log does not reduce the physical file size. To do that you
    > will have to use DBCC SHRINKFILE. Truncating the log removes the inactive
    > portion of the transaction log, those transactions that have already been
    > terminated through commit or rollback. Normally this information is

    retained
    > in the event that the database must be restored from backup.
    >
    > You might want to check out the following topics in the SQL Server BOL:
    > Virtual Log Files and Shrinking the Transaction Log,


    Note that truncating the log will also only remove whatever's after the
    active portion of the log. If the active portion of the log is at the end
    of the log file, truncating it won't change it's size; even after a DBCC
    SHRINKFILE.

    The best suggestion I've seen for this is to create a temp table, and insert
    records into it. After every hundred records or so, check where the active
    portion of the log is. If it's near the top (say in the first ten rows
    returned by DBCC LOGINFO(<db name>) (eg, DBCC LOGINFO(MyDatabase) ) ), then
    you truncate it (and drop the temp table).

    --
    Colin McGuigan




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center