Post reply

Shrinking Transaction Log File

  • October 17, 2002 at 9:55 am

    #58093

    We have been unsuccessful in shrinking a transaction log file that had grown to 30GB. Only 93MB are used while 30GB are free. I am attempting to give back the 30GB to the operating system. We have SQL 7.0 (sp3) on Win2000 Advanced server (Windows cluster).

    I have truncated the transaction log leaving 2.9GB of log free and 93MB used. I then tried shrinking the log file to 500MB (using both the EM and T-SQL). Books on Line says it doesn't happen right away but will when there is another checkpoint. I have run checkpoints and transaction log backups in hope of then freeing the space -but no luck.

    Any help would be greatly appreciated.

    Thanks

    Ron

  • October 17, 2002 at 10:06 am

    #438796

    Read Microsoft KB at http://support.microsoft.com/default.aspx?scid=kb;en-us;Q256650.

  • October 17, 2002 at 6:44 pm

    #438843

    Its all about virtual log files and that you need to get back to the beginning before you can truncate and shrink the physical file.

    The basic process is

    Set the recovery model to simple

    then perform a transaction (i.e create table fred (name char(8000)) the select name from sysobject into fred drop table fred)

    checkpoint

    truncate log

    shrink log file

    This keeps adding transactions filling virtual log files until the process starts using the first virtual log files and the file shrinks

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • October 18, 2002 at 12:24 am

    #438847

    Ron

    Never had much luck with shrinking the log file under SQL7. In SQL2K it's a bit better, but not that much. You can play around with the virtual logs files, truncating and shrinking but the quickest method I found was to detach the Database.

    When the database is detached rename or delete the log file. Then attach the database without specifying a location for the log file. During the attach process it will create a log file that is 2mb in size.

    Obviously you can't detach the database when there is activity taking place, so just pick a quite time when nothing is happening.

    At one point I had a database that was living on a server with no space at all and a continually growing log file (due to a badly designed front-end app) was always causing crashes. The process described above had the database back in action, with no loss of data, in less than a 30 sec.

    Phill

    Check out my script at

    http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=190&CategoryNm=Maintenance%20and%20Management%20&CategoryID=1

    Thanks

    Phill Carter

    --------------------
    Colt 45 - the original point and click interface

  • October 18, 2002 at 5:18 am

    #438869

    This is considered the best script around to use. I have used it since I first started using SQL 7. The author Andrew Zanevsky had a great article on it as well but I forget where I found it, then I found the code posted here one day. It is the best.

    http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=26&CategoryNm=Maintenance%20and%20Management%20&CategoryID=1

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Viewing 5 posts - 1 through 4 (of 4 total)

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