Post reply

Backup Log With Truncate Only?

  • March 7, 2006 at 12:55 pm

    #112217

    Hello, first post, very general question but I've recently been moved from a Citrix server admin position into a SQL DB admin position, and it's all brand new to me!  I'm excited to learn it, but man what a lot to learn.  Here's the question - doing some reading on Microsofts official SQL 2000 Admin course, and it talks about running the command in the subject line to remove entries from the transaction log. If I understand it correctly, it will truncate the log, without backing it up, and not even note in the transaction log that it was truncated.  What type of situation would this command be used in?

    Thanks for any information, hopefully my questions will get "less dumb" as I learn more about the SQL world. 

  • March 8, 2006 at 3:34 am

    #625348

    If the transaction log contains committed transactions and u need more space for lot

     of active transactions then u can truncate the

    log without backing up.

    ..hema

  • March 8, 2006 at 3:36 am

    #625349

    Hi David, this is an old trick used by developers when they ran out of transaction log space.I would never use this command on a production server, it breaks the transaction log history so I sense from your question you are gong to be fine as a DBA.

  • March 8, 2006 at 5:49 am

    #625369

    Thanks for the replies Hema and Allen.  I guess I wasn't thinking about a non-prod environment.  Seems so far that I have seen a lot more people involved in Dev work on the SQL side than I was used to on the Citrix side.

    Allen - thanks especially for the dba comment ... I can't explain how weird it is to instantaneously go from being a SME in my field (Citrix) to not being able to significantly contribute to the group (SQL).  But I always did say I loved what I do b/c of the constant challenge!

    Thanks all!

  • March 8, 2006 at 4:53 pm

    #625562

    If you set the database recovery mode to the Simple Recovery Model, it automatically does a log truncation after every database checkpoint. As mentioned above, normally only development use. Also, a database that is normally read-only, but gets loaded and then backed up immediately--if there was a problem, you'ld just restore from a backup and retry the load.

    David Lathrop
    DBA
    WA Dept of Health

  • January 29, 2009 at 2:57 am

    #934475

    I read this post with interest... I have baulked at using this command too.

    I guess the correct command to use is a simple DBCC SHRINKFILE ('filename', ) command?

  • January 29, 2009 at 4:26 am

    #934507

    andy.dicken (1/29/2009)

    I guess the correct command to use is a simple DBCC SHRINKFILE ('filename', ) command?

    No. Truncate removes inactive log records but doesn't change the size of the file. Shrink changes the size of the file, but doesn't affect the log records.

    Read through this - Managing Transaction Logs[/url]

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 29, 2009 at 4:30 am

    #934509

    Yes, sorry I should have given more detail. What I mean is, after a successful log backup, which truncates the log inside the file, you can use the shrink file to reduce the physical file size to pull back disc space. That way, with the backup and the shrink file, you have maintained integrity and saved space as well?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply