how to physically shrink transation log

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

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.

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.

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.

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.

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

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.

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?

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.

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