I think I have to set the database to Auto Shrink 1

[bessebo]

I just was reading about Auto Shrink and it appears that I will have to set this database option if I want the transaction log to shrink since I am using transaction log backups. This may explain why my transaction log grew so tremendously when I was rebuilding indexes.

 

[dwight1]

Thank you SQLBILL.

I did the backup log with truncate only and it worked. I have changed the db backup to simple from full because the data is only for reporting purposes and doesnot require timely update. Previous nights backup is more than good.

So as per your advice, the best practice is to keep the tlog and Dfiles to grow automatically and keep a watch on them THEN restricting the tlog/file growth.

 

[SQLBill]

The only purpose (IMO) for setting Maximum File Growth is to limit how much the transaction log can grow in total. Then you can set alerts to tell you when it's reaching that point. However, IMO, that's too late...you should already be aware of how the tlog is growing and do what is necessary (including getting more drives) before all the space is 'consumed'.

I allow my tlog and data files to grow automatically by 2 GB and just keep an eye on their sizes. As needed, rarely, I will shrink the files. I normally only need to do this after a DBCC INDEXDEFRAG. Otherwise, I just leave them alone. Of course, with 900+ GB of space, I do have some room for growth.

-SQLBill

Posting advice: FAQ481-4875