SQL Server Performance

Reduce the Tran's Log size

Discussion in 'Performance Tuning for DBAs' started by vpathisseril, Oct 22, 2003.

  1. vpathisseril New Member

    I have SQL Server database.
    Total database size is close to 15Gb.
    The data file size is approximately 3.5 Gb, and set to Grow Automatically by 10% and Max file size is unlimited.
    The transaction Log file is set to close to 11.5 Gb, and set to Grow Automatically by 10% and Max file size is unlimited. Only about 500 mB of 11.5 used for transactions.
    Is there any possible way that I can change the size of he transaction log somewhat three GB Max. Because I am doing an hourly transaction log and full dB backup. I have tried Shrink, DBCC, and sp_detach the db, delete the ldf file, and tried to attach. And I tried my last resort, took a backup of the dB, delete the dB, recreate the dB, and restored, then also the translog file got created as same as 11.5. You can get the log details at
    http://goEdeveloper.com/work.
    Thanks[?]
  2. satya Moderator

    Yes, initially use DBCC SHRINKFILE to shrink the Tlog to desired size and then set the Tlog set by righ-clicking on the database, remove auto-grow option for Tlog file alone.

    Before executing DBCC SHRINKFILE make sure there are no transactions pending on the Tlog and the process will take bit time to reduce the size which is not immediate.

    Refer to books online for more information.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. vpathisseril New Member

    Hi Satya,
    Thanks, I am kind of new with this, so please check the below script before i execute.
    http://goEdeveloper.com/work

    F:SQL Serve LogDev3_log.ldf


    So script as follows
    DBCC ShrinkFile ('F:SQL Serve LogDev3_log.ldf',1500)
    when i executed this i got the follwoing error.

    <B>
    Server: Msg 8985, Level 16, State 1, Line 1
    Could not locate file 'F:SQL Server LogDev3_log.ldf' in sysfiles.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    </B>
    Any suggestins will highly appreciated.
    Thanks
  4. satya Moderator

    In the DBCC SHRINKFILE the filename is to be used 'Is the logical name of the file shrunk' not the physical name. So it must be DBCC SHRINKFILE('Dev3_log,1500). (check for the correct logical name for this .LDF)

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  5. vpathisseril New Member

    DBCC SHRINKFILE (SERM_Log,500)
    DBID FileID CurrentSize MinSize
    102781680 128781680128
  6. vpathisseril New Member

    Thanks Satya,
    I have tried your suggestion as follows
    I have used the logical name. Still it didn't shrink to the that I want. I want to bring it down to 500 Mb, this is my test dB. Also when I do a restore my production to Dev2 the Trans log off of My prod is 12 gB, and I want to bring that down to 1gb, that would be final goal
    Any suggestion would be greatly appreciated.


    DBCC SHRINKFILE (SERM_Log,500)

    DBID FileID CurrentSize MinSize UsedPageSize Estimated PageSize

    102781680 128 781680 128
  7. vpathisseril New Member

    Hi..
    Thanks Satya..
    I played around DBCC ShrinkFile and it worked.
    I have backed up the TransLog, and ran the DBCC SHRINKFILE script.
    Repeated the step for few times and got where I wanted.
    Once again, thak you for your help.
    You know wht your talking..
    Varghese
  8. satya Moderator

    Glad it worked, and usually DBCC SHRINKFILE proces is slow and not immediate as compared to SHRINKDB process.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  9. hetOrakel New Member

    Hello,

    i'm having the same problem as you're describing.
    But when i try to use the shrinkfile optin in the Control Center i can't set to a smaller size then approx 2G5 (The dB file itself is approx 16M!) It says al the space is used. Is there some way to free this space? Or at least find out what's occuping this amount of Gig's.

    Kind regards
    Henrie hetOrakel

    [edit]
    some spelling corrected
    [/edit]
  10. satya Moderator

    In general to gain more space, you can free disk space on any disk drive containing the transaction log file for the related database. Freeing disk space allows the recovery system to enlarge the log file automatically. Or you can gain space by adding or enlarging a log file for the specified database.

    Because a log file can only be shrunk to a virtual log file boundary, it may not be possible to shrink a log file to a size smaller than the size of a virtual log file, even if it is not being used.

    What statement do you use to SHRINK the Tlog?


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  11. hetOrakel New Member

    I use the "SQL Server Enterprise manager". And click my way into the shrnik (file) function.

    But why has this file grown so aburdly large on a db of 16M?
    (I know off-course the auto-grow is enabled en was set to unlimited grow in 10% steps.....)
    But isn't 2G5 not a bit much for such a little DB?

    I turned the auto-grow off. (now)
  12. satya Moderator

    It depends on activity of the application accessing database information, database maintenance plans etc.

    Also how often you backup the log.

    Follow the books online to reduce the shrink the Tlog using DBCC SHRINKFILE which clarifies how to proceed.

    You may use EMPTYFILE option and before that take full backup and keep it safe.
    After this make sure you maintain regular backups and if require assess Tlog size and keep it fixed.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  13. hetOrakel New Member

    Ok thanx!

    I'm right now backing up the db/TLog. Then i'm gonna try to shrink the TLog again.


    Ok, shrinking didn't decrease the file-size. Read some docs on MSDN-online. Then used the DBCC SHRINKFILE command in the query-analyzer. This did do the job. There-after limited the TLog-size at 1G5.

    Thnx 4 helping / giving the right hints.

    PS: Total DB Size now below 100M

    Kind regards
    Henrie hetOrakel
  14. satya Moderator

    Ensure to schedule the Tlog backup and assess the size after all jobs and activity on the database, as state above.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  15. vpathisseril New Member

    During an Off peak time, backup your DB,
    Run DBCC SHRINKDATABASE ('DBName', DesiredTRansLog Size in MB)
    Example: SHRINKDATABASE ('PUBS', 10)
    Backup your TransLog.
    Perform the step till you reached your desired Translog size.
    You might need perform the steps more than few time escially your Tlog has grown over 1 Gb.
    To avoid your TransLog to growth in future as Satya suggested remove the AutoGrow option and create a job to backup Trans Log every 15 mins to 2 hrs depeding on daily tranasction.
    Good Luck
  16. TerryCrosby New Member

    Another thing to try, if the log doesn't matter to you, issue dump tran DBNAME with truncate_only from query analyzer, then issue the DBCC ShrinkFile. I do this on all me test databases when i need space and want to shrink down the transaction log files.

    Terry Crosby
  17. satya Moderator

    ... and do not forget to take full backup after you issue TRUNCATE_ONLY clause,if any issues on databases then no chance for recovery.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  18. satya Moderator

    And also DBCC SHRINKFILE does have options...EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ] }

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page