Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Location
    Singapore
    Posts
    17

    Question Unanswered: How to Free SQL Server 7 Transaction Log Space

    Hi Guys,

    My company SQL Server has allocated 6G+ of space for the transaction log space. However, only about 300MB of them are used.

    Is there any chance (ways) that I can free up these free space which are allocated for the transaction log?

    Thanks in advance.

    Tang

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Post Re: How to Free SQL Server 7 Transaction Log Space

    Q1 Is there any chance (ways) that I can free up these free space which are allocated for the transaction log?
    A1 Yes. For example:

    USE YourDB
    GO
    DBCC SHRINKFILE (YourDBLog, 600)

  3. #3
    Join Date
    Nov 2002
    Location
    Singapore
    Posts
    17

    Re: How to Free SQL Server 7 Transaction Log Space

    This doesn't work.
    The free spaced allocated to Transaction Log still remain.

    I have attached an image of the space allocation. Is the Transaction Log free space shrinkable?


    Originally posted by DBA
    A1 Yes. For example:

    USE YourDB
    GO
    DBCC SHRINKFILE (YourDBLog, 600)
    Attached Thumbnails Attached Thumbnails space.gif  

  4. #4
    Join Date
    Oct 2002
    Posts
    369

    Re: How to Free SQL Server 7 Transaction Log Space

    RE:
    This doesn't work.
    The free spaced allocated to Transaction Log still remain.

    I have attached an image of the space allocation. Is the Transaction Log free space shrinkable?
    It is rarely instantaneous on a heavily used production DB. On such systems it can take a while (especially on large systems with large logs and lots of transactions that stay open for some time) eventually the VLF moves and it does shrink; though I have heard claims that several shrink statements are necessary. If it fails because the minnimum size is > what you are trying to shrink it to, it will fail.

  5. #5
    Join Date
    Nov 2002
    Location
    Singapore
    Posts
    17

    Smile Re: How to Free SQL Server 7 Transaction Log Space

    Thank you for your reply, I guess it is almost time to bring down the server for maintenance...

    Originally posted by DBA
    RE:

    It is rarely instantaneous on a heavily used production DB. On such systems it can take a while (especially on large systems with large logs and lots of transactions that stay open for some time) eventually the VLF moves and it does shrink; though I have heard claims that several shrink statements are necessary. If it fails because the minnimum size is > what you are trying to shrink it to, it will fail.

  6. #6
    Join Date
    Oct 2002
    Posts
    369

    Post Re: How to Free SQL Server 7 Transaction Log Space

    RE:
    Thank you for your reply, I guess it is almost time to bring down the server for maintenance...
    You are welcome.

    You may be interested to know that MS supposedly changed the shrink algorithm somewhat in Sql Server 2k to make it act noticeably more quickly (apparently many users do not like to wait).

    Actually I think actions taken by impatient 7.0 users to find a "quicker way" caused frequent headaches at Microsoft, and that led to the speedier shrink performance in Sql Server 2k.
    Last edited by DBA; 12-10-02 at 05:03.

  7. #7
    Join Date
    Oct 2002
    Posts
    369

    Post Re: How to Free SQL Server 7 Transaction Log Space

    The following is from BOL:

    Shrinking a log is dependent on first truncating the log. Log truncation does not reduce the size of a physical log file, it reduces the size of the logical log and marks as inactive the virtual logs that do not hold any part of the logical log. A log shrink operation removes enough inactive virtual logs to reduce the log file to the requested size.

    The unit of size reduction is a virtual log. For example, if you have a 600 MB log file that has been divided into six 100 MB virtual logs, the size of the log file can only be reduced in 100 MB increments. The file size can be reduced to sizes such as 500 MB or 400 MB, but it cannot be reduced to sizes such as 433 MB or 525 MB.

    Virtual logs that hold part of the logical log cannot be freed. If all the virtual logs in a log file hold parts of the logical log, the file cannot be shrink until a truncation marks one or more of the virtual logs at the end of the physical log as inactive.

    When any file is shrunk, the space freed must come from the end of the file. When a transaction log file is shrunk, enough virtual logs from the end of the file are freed to reduce the log to the size requested by the user. The target_size specified by the user is rounded to the next highest virtual log boundary.

Posting Permissions

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