How to shrink a SQL7 transaction log file?

Posted on 2007-10-01
Last Modified: 2013-12-28
I have a SQL7 running in NT4. The database size is about 2.5GB. The .mdf file is about 2.5GB, but the .ldf file is 7.5GB. When I back up the transaction log in SQL Enterprise Manager, that backup file is only 20MB!
Q#1. Why there is a huge size difference in the database, .ldf file and the log file backup?
Q#2. How to configure the database so the  .ldf file does not 'hog' too much disk space?
Q#3. How to 'shrink' the current .ldf file? I tried cmd: dbcc shrinkdatabase (myDB, 50) does not seem to work.
Q#4. What considerations to take when deciding whether to commit the log to database or keep it in transaction log?
Thanks a lot.
Question by:richtree
    LVL 142

    Accepted Solution


      transaction log has 2 possible configuration:
      * SIMPLE
         means that any transaction get's written to the transaction log, but once it is committed, it is overwritable in the log.
         you cannot perform transaction log backups
         you cannot perform restore to a point of time
         you should let the transaction log file size as it is, because if you shrink it (regulary) to a small size, it has to grow again.

      * FULL
         means that any transaction get's written to the transaction log, but only once the transaction log backup has been performed, it is overwritable in the log
         you have to (should) perform transaction log backups
         you can perform restore to a point of time
         you should let the transaction log file size as it is, because if you shrink it (regulary) to a small size, it has to grow again.

    note: if you had a database in full recovery mode, but did not do any transaction log backups, the log file will indeed grow endlessy until it fills the hard disk(s) completely.
    if that happened, you should change to SIMPLE, and try several times the DBCC SHRINKFILE ( log_name, <some size here) until the file shrinks (as the log file is internally a circular buffer, and in case the pointer is at the end of the file, the file won't shrink, hence the retries)

    LVL 68

    Assisted Solution

    Q1.  Db and log backups include only active portions of the file; the original files contain inactive data as well, so they can be (much) larger.  The log file is *never* shrunk automatically, so once it has grown to a very large size, you have to explicitly shrink it to get it back down to a manageable size.

    Q2.  You can restrict the log file to a maximum size (say 200M: you need an amount at least the size of the single largest transaction you will run).  This works great for dbs in Simple mode.
    For non-simple dbs, you must make sure that you backup the log frequently enough so that whatever space you assign is sufficient to meet all logging requirements.

    DBCC SHRINKFILE (logicalLogFileName, 200)
        --* you can get the logical log file name using "exec databaseName.dbo.sp_helpfile" in Query Analyzer
    --if it doesn't work, run the two commands again.
    --When done with that, do a full backup of your db as you will have broken your tlog backup chain.

    Q4.  I'm not sure specifically what you want to know.

    Author Comment

    some comments from DG:
    here is how to shrink the transaction log file in SQL7.
    - in the database options, set the auto-shrink on for transaction log file
    - run DBCC Shrinkfile (myDB_log, 500) , 500 means 500M, myDB_log should be the actually myDB's log file name.
    If it is still doesn't work, you can use the alter database command, or restore the database into a new log file name.

    Author Comment

    I do not see SIMPLPLE or FULL mode, but I see the following options:
    General - Database files - File properties: Automatically grow file -
    File growth:
     In megabytes
     By percent: 10 (//current setting)
    Maximum file size:
     Unrestricted filegrowth (//current setting)
     Restrict filegrowth (MB)
    Similar with  Transaction Log setting as above.
    Q#5. Based on the above choices, which option corresponds to SIMPLE mode, which to FULL mode?
    Q#5. What impact should I be aware of before I 'shrink' the log file? Right now the log file take 7GB space while it actually is only 250MB. So if there is no negative impact, I certainly like to 'shrink' it for now though it will grow again.
    Q#6. Any preparation/precaution should I take before I 'shrink' it?
    Thanks a lot.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    >I do not see SIMPLE or FULL mode
    check in the database general properties, not in the file properties of the database

    Author Comment

    Will I lose any capability (eg restore to a point) if I manually 'shrink' the database?
    What cautions should I take before 'shrink' it?
    Thanks a lot.
    LVL 68

    Assisted Solution

    >> Will I lose any capability (eg restore to a point) if I manually 'shrink' the database? <<

    If you shrink the log (either alone or as part of the whole db), yes, **from that point on** -- you could still do point-in-time recovery to before the shrink.  However, if you take a full backup of the db immediately after the shrink, then you will have a valid starting point for point-in-time recovery.

    Author Comment

    thank you for your confirmation.

    Author Comment

    Thank you all for your wonderful ideas.

    Featured Post

    Are your corporate email signatures appalling?

    Is it scary how unprofessional your email signatures look? Do users create their own terrible designs and give themselves stupid job titles? You can make this a lot easier for yourself by choosing an email signature management solution from Exclaimer today.

    Join & Write a Comment

    NTFS file system has been developed by Microsoft that is widely used by Windows NT operating system and its advanced versions. It is the mostly used over FAT file system as it provides superior features like reliability, security, storage, efficienc…
    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now