how to reduce transaction log size automatically? Possible Duplicate:
  Why Does the SQL Server Transaction Log Keep Growing or Run Out of Space?  




how can i reduce the size of Transaction log programatically and automatically, because no one knows when the when it gets full since how can we manage this can any one suggest for this
You need to first understand what type of recovery (from a point in time standpoint) you want to get out of your SQL Server environment. 

Do you want to be able to restore to a specific point in time? Then you want to remain in full recovery mode and you need to start taking transaction log backups (the log file will continue to grow in full mode until you take a log backup, as described in the linked blog post below)

If you are fine going to just the last full backup and not a specific point in time, then you can switch to simple as @andomar's answer suggests. (in this model, if you backup at 8PM each night and have a failure at 7:30PM, you have to go back to 8PM last night. This is fine in many applications but you need to be sure your business is okay with this. This blog post talks about customer expectations around recovery.

If you want to reduce the size of your transaction log file after it has grown due to mismanagement you can shrink. But I would NOT shrink the entire database as another answer suggests. That will cause other issues. It is generally speaking not a best practice to ever shrink your database. I would do a SHRINKFILE instead.

DBCC SHRINKFILE (&lt;FileName&gt;, &lt;TargetSize&gt;) WITH NO_INFOMSGS


This command is described here.

If you want to understand the mechanics deeper, I wrote a blog post a few years ago that discusses transaction log growth. Proper management is best because then you won't have to worry about finding an automatic way to shrink it.