SQL Server Performance

how to physically shrink transation log

Discussion in 'Performance Tuning for DBAs' started by ranjitjain, Jan 21, 2005.

  1. ranjitjain New Member

    I have a databases in SQL that is growing very quickly. The TX logs for these databases are growing quickly as well. I don't have the autoshrink turned on as I have heard that it is bad for file fragmentation. What are some things that I can do to control the database size and TX log size. One database is almost 20GB and that will eat up a lot of disk space when I do backups of the logs and database. Thanks for any help
  2. mmarovic Active Member

    To controll tx log size you have 3 choices:

    1. Autshrink or regurarly shrink db or log. Not good because of file fragmentation and wasting resources (why to constantly shrink and expand?)
    2. Have database model set to simple. Drowback is that in case of disaster you can recover data just to point of last full backup.
    3. Use full recovery model with regular tran log backups. The safest one. If you don't have enough disk space better ask for additional disks that is usually cheeper then possible lost of transactions after last full backup.

    On top of that you cuold also use bulk_logged model and combine differential backups with full and tran log backups. I don't have enough experience with it to discuss.
  3. satya Moderator

    Can you define what kind of growth on that database, and are there any database maintenence plans or reindex jobs that might contributing this log expansion.

    If you deploy the SIMPLE recovery model then ensure to schedule the full backup in frequent intervals in order to maintain the availability. In this model the database can be recovered only to the last full database backup or last differential backup.



    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. ranjitjain New Member

    quote:Originally posted by satya

    Can you define what kind of growth on that database, and are there any database maintenence plans or reindex jobs that might contributing this log expansion.

    If you deploy the SIMPLE recovery model then ensure to schedule the full backup in frequent intervals in order to maintain the availability. In this model the database can be recovered only to the last full database backup or last differential backup.



    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

    Thats right satya that lot of reindexing is done causing TX to grow.
    Now i need to decrease the size of TX physically as im running out of Harddik space
  5. davidfarr Member

  6. satya Moderator

    It may not be the final solution where you can add more disk space and as an interim solution you can shrink the Transaction log in order to cater the database needs.

    You may need to reindex every index on the database regularly, where you can choose or select few set of indexes those should be indexed in order to get optimum performance, this way you can reduce the stress on the Transaction log size and disk space issue.

    YOu can also use DBCC INDEXDEFRAG to keepup the performance, for the logical fragmentation you must use DBREINDEX for performance.

    Books online is the best bet in this regard to findout about INDEXDEFRAG and DBREINDEX.

    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. ranjitjain New Member

    Originally posted by satya

    It may not be the final solution where you can add more disk space and as an interim solution you can shrink the Transaction log in order to cater the database needs.

    You may need to reindex every index on the database regularly, where you can choose or select few set of indexes those should be indexed in order to get optimum performance, this way you can reduce the stress on the Transaction log size and disk space issue.

    YOu can also use DBCC INDEXDEFRAG to keepup the performance, for the logical fragmentation you must use DBREINDEX for performance.

    Books online is the best bet in this regard to findout about INDEXDEFRAG and DBREINDEX.

    Hi Satya,
    Ive finally deattached my DB and then reattached renaming TX filename.
    What issues gonna b created after this.
    But doing this has solved my Disk space problem as new TX file with 600 kb size is created
  8. satya Moderator

    Ensure the database is accessible after you've attached and if you're getting performance dent then again you must run UPDATE STATS on the tables or DBREINDEX.

    Ok that may be the right one for this situation and again same issue will be repeated from the schedule of DB reindex jobs and again you need to shrink the size.

    SO the final solution is to resize the database or add more disk space to server.
    Or archive the database wherever required.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. ranjitjain New Member

    Originally posted by satya

    Ensure the database is accessible after you've attached and if you're getting performance dent then again you must run UPDATE STATS on the tables or DBREINDEX.

    Ok that may be the right one for this situation and again same issue will be repeated from the schedule of DB reindex jobs and again you need to shrink the size.

    SO the final solution is to resize the database or add more disk space to server.
    Or archive the database wherever required.


    Thanx alot satya,
    Well My DB is running perfectly and i took backup of the DB too without any dent.
    The first thing i did was UPDATE statistics.
    Now ive scheduled a job to shrink TX file everyday.
  10. hoo-t New Member

    If you have to, you have to. But shrinking the transaction log every week, much less every day, is a really bad idea. If you shrink it every day, its obviously growing every day. Why does it grow? Because it doesn't have sufficient space to perform the operation that it has been asked to perform. When does it grow? When users update or insert records, and when you are running certain maintenance processes. Well, if the transaction log has to expand in order for a user's update to process, the user sits and waits while the log file expands. This is NOT a good thing! If the transaction log has to expand to complete your maintenance routine, your maintenance routine runs much longer as well. You may not care about that one...unless of course, users are in the system...in which case they are being impacted by both the log expansion AND your maintenance routine. Finally, constant expansion and shrinking of the log file leads to increased fragmentation, which results in...yep, decreased performance. In short, shrinking the transaction log every day will impact the performance of your database dramatically!

    If you are having difficulty controlling log growth, the best thing that you can do is increase the frequency of your log backups. Many shops (including mine) backup logs as frequently as every 15 minutes. That won't cost you much more disk space for the backups, and will allow your transaction log to stay much, much smaller. You're just splitting up those transactions among more backup files. You'll find that when you arrive at that magic log backup frequency, your log file will stabilize at a size that you are satisfied with and will quit expanding.

    Steve
  11. ranjitjain New Member

    hi steve,
    U have forced me to think over this again seriously.
    Thanx alot for that valuable suggestion.
    Now i definately have to rethink of this.
  12. mmarovic Active Member

    Hey Steve, I'm giong to put this thread in my favourites because of your answer. That is something I try to explain all the time but I was not able to put together such detailed and clear explanantion. Great post! How about adding new hint?
  13. hoo-t New Member

    ranjitjain,

    Good! You would NOT have been happy with the results of shrinking every day! The problem is, that lurking about in this forum and a couple others, I see this every day! I even see posts recommending it occasionally!!! There are situations where it may be required to shrink on a schedule, but it should be avoided like the plague!

    mmarovic,
    Thank you for your kind words! I doubt I will have time today, but I'll take a look, see if I can reword it some, and submit it as a hint. I can't imagine that its not already there, though!

    On one of the other forums I frequent, I have intended to write an article about this issue, but haven't gotten to it yet.

    Steve
  14. SandeepThorat New Member

    Hello All,I am facing the same problem. Tx Log file increase upto GBs. Though I am taking Tx Log Backup every one hour, the file size doesn't decrease. A shrink opration after the hourly Tx Log Backup also doesn't help. How should I control the Tx Log file size. Please help.One more thing is, for the same set of data, Tx Log file doesn't grow that fst in SQL Server 2000 as compared to 2005. What can be the reason behind this? Regards,Sandeep

Share This Page