Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2008
    Posts
    6

    Unanswered: Backing up transaction log file

    Hi All,
    Before posting this, I did search for backing up the transaction log file(.LDF).Currently transaction log file is 4 GB size and I want to reduce it to few MBs.Will the following procedure work?
    1)Take the backup of transaction log file by executing the statement-
    BACKUP LOG <name of DB> TO <name of db>backup

    2)Run DBCC shrink file statement to reduce the log file size-
    DBCC SHRINKFILE(<name of log file>,25)
    This is as per procedure explained in http://support.microsoft.com/kb/272318

    Will this free up 4GB physical space on drive? or anything else I need to do ?

    Thanks
    Prasanna

  2. #2
    Join Date
    May 2004
    Location
    Columbus, OH
    Posts
    59
    Prasanna,

    When I need to do what you're thinking of doing I do the following steps:

    1) Backup the LOG file using this command
    BACKUP LOG [DatabaseName] TO DISK = N'P:\backup data\Database_translog.bak' WITH INIT , NOUNLOAD , NAME = N'Translog Backup', SKIP , STATS = 10, NOFORMAT

    2) Truncate the entries in the LOG file
    BACKUP LOG DatabaseName WITH TRUNCATE_ONLY

    3) Shrink the LOG file
    DBCC SHRINKFILE(DatabaseName_LOG)

    This is the important part though, you need to make sure that you are backing up the LOG file more regularly so that it does not get to this size in the future. You can use the command in step 1) to do the backup.

    By doing a regular backup of the LOG file you will keep the size manageable.

    It may not free up all 4GB of space, but it will take a large bite out of it.
    Life....Just another opportunity to live another day like a pirate....

  3. #3
    Join Date
    Jan 2008
    Posts
    6

    backing up log file

    Thanks.
    Prasanna.

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    And of course all of this assumes that you have completed a full backup of the database.

    If you have not done a full backup, there is no point of reference for the log file to be truncated - see transaction logs, checkpoints in BOL.

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    May 2004
    Location
    Columbus, OH
    Posts
    59
    Tomh53,

    Absolutely. Thank you for filling the gap that I unintentionally left.
    Life....Just another opportunity to live another day like a pirate....

  6. #6
    Join Date
    Jan 2008
    Posts
    6

    Unhappy sharepoint

    Yes, Its a full backup.I forgot to mention that this is Microsoft Sharepoint database.and database is MSDE 2000.Possibly I need to use osql or sqlcmd command tools to achive this.

    Thanks
    Prasanna

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •