Reduce the size of Transaction Log FIle

  • Hi All,

     

    SQL Server Version : SQL Server 2000 8.0.194

     

    My one production database Transaction Log size is more than 10GB. But data file size is only 1 GB.

     

    I try to reduce the side of the logfile using shrink data file, again the size is not reducing.

    How can I reduce the size of a log file .

     

    Please advice

     

    Mathew

     

  • Are you using simple or full recovery?  If using full, backup the transaction log, or switch to simple recovery mode which will truncate the log at various intervals.

  • Hi Matthew,

    Here is the command you need to run to carry out Ian's suggestion.

    backup log <DB Name Here> with truncate_only

    After this, you need to consider the backup solution you require for this db, then set the database recovery mode as Ian suggests.

    Best regards,

    Martin

  • I did the following

    1. Change the recovery model to simple

    2. issue the commnad backup log <DB Name Here> with truncate_only. But this time file size not changed. FIle size is still 10 gb . But uses only 25 MB

    3. When i try to reduce the log size using enterprise manager, sql showing message "error 21335, New DB File size must be larger than current size'

    Pls explain me how can i reduce the physicial Transaction  file size

    Mathew

     

     

     

  • Matthew,

    What you have effectively by running the backup log command is emptied the water out of a bucket.  The contents of the bucket are gone, but the bucket still remains, if you catch my drift.

    To shrink the file, you must now run the following...

    dbcc shrinkfile (<virtual file name here> )

    and it will then shrink back to it's original size.  To find the virtual file name, run sp_helpfile from within the context of the target database.

    Hope that helps,

    Martin

  • I've found this script to work best for us.

    http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=26

     

    Good Luck!

  • As said in BOL:

    USE UserDBGODBCC SHRINKFILE (DataFil1, 7)GO

    "Datafill" is the logical file name and u need to difine a size the file needs to be shrinked to. Try with 9000 (GB) to start with. Once the file has been reduced to 9 GB go for another GB less. Seems to be in SQL 2000 if u have less phisical disk space - basically less then the log file DBCC SHRINKFILE takes forever to shrink the log. By reducing it by chunks its more faster and efficient. I had the same issue 2 days ago and this solution took care of it. post a note if this works.

    Good Luck.

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

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