Professional Web Applications Themes

TRuncating Transaction Log - Microsoft SQL / MS SQL Server

Can somebdoy please help me regarding transactions log. I was doing a lot of testing on my database and now its final. Now I want to take a backup of this database so that I can restore it on a client server. Problem is the size. The .MDF file is just 7 MB but the .LDF file is 1.16 GB. How can I truncate the log file? I have tried Shrink database but the size does not decrease much. And now whenver i try it is actually increasing the log file size. Any input? I am using MS SQL Server ...

  1. #1

    Default TRuncating Transaction Log

    Can somebdoy please help me regarding transactions log. I was doing a lot of
    testing on my database and now its final. Now I want to take a backup of
    this database so that I can restore it on a client server. Problem is the
    size. The .MDF file is just 7 MB but the .LDF file is 1.16 GB. How can I
    truncate the log file? I have tried Shrink database but the size does not
    decrease much. And now whenver i try it is actually increasing the log file
    size. Any input?

    I am using MS SQL Server 2000 under Windows 2000.

    Thanks


    Rizwan Guest

  2. #2

    Default Re: TRuncating Transaction Log

    Change the recovery mode to Simple and then use DBCC SHRINKFILE. If it
    still doesn't shrink then have a look here:

    http://www.support.microsoft.com/?id=317375 Log File Grows too big
    http://www.support.microsoft.com/?id=110139 Log file filling up
    http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
    http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
    and AutoShrink
    http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
    Server 7.0 Tran Log
    http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
    Server 2000 with DBCC SHRINKFILE


    --

    Andrew J. Kelly
    SQL Server MVP


    "Rizwan" <com> wrote in message
    news:bdbZa.5887$bellglobal.com... 
    of 
    file 


    Andrew Guest

  3. #3

    Default Re: TRuncating Transaction Log

    Hi Rizwan,

    If your DB is in FULL Recovery model, the transaction log will not be
    truncated untill you back it up.
    You should plan a proper backup strategy for your DB but for now it sounds
    like you want the quick and dirty way so here goes:

    BACKUP LOG <your_db_name> WITH TRUNCATE_ONLY -- to truncate the log without
    actually backing it up
    DBCC SHRINKFILE (<your_log_logical_name>, <desired_size_in_MB>)-- to
    truncate the physical log file

    NOTE (from BOL):
    "...However, if part of the logical log resides in the virtual logs beyond
    the target size, SQL Server frees as much space as possible and then issues
    an informational message. The message tells you what actions you need to
    perform to move the logical log out of the virtual logs at the end of the
    file. After you perform the actions, you can then reissue the DBCC
    SHRINKFILE command to free the remaining space. For more information about
    shrinking transaction logs, see Shrinking the Transaction Log."

    Backup your DB again because now you will not be able to use the log for
    restore purposes.

    HTH

    "Rizwan" <com> wrote in message
    news:bdbZa.5887$bellglobal.com... 
    of 
    file 


    Amy Guest

Similar Threads

  1. Linking URL truncating
    By whatcom_county in forum Macromedia Contribute General Discussion
    Replies: 2
    Last Post: April 3rd, 03:48 PM
  2. ColdFusion truncating text
    By Reg Phipps in forum Coldfusion Flash Integration
    Replies: 1
    Last Post: November 9th, 09:50 PM
  3. truncating text
    By Matt in forum PHP Development
    Replies: 5
    Last Post: December 3rd, 01:46 PM
  4. FW POP-UPs Truncating URLs
    By BoydR webforumsuser@macromedia.com in forum Macromedia Fireworks
    Replies: 2
    Last Post: October 7th, 08:20 PM
  5. Replies: 0
    Last Post: August 14th, 06:58 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139