Managing the space used by your SQL Server transaction logs is pretty easy.
If the database is in full recovery mode then do transaction log backups on a regular basis. Typically every 15-60 minutes.
The second option is to put the database into Simple recovery mode. If you need the ability to do point in time recovery then you can’t use simple recovery mode at all.
Now there is something that you shouldn’t ever do. Truncating the log and shrinking the file is the first thing that shouldn’t ever be done (contrary to popular opinion).
If you are doing log backups, and your database log file is growing to a large size, then you need to get more space for your disk. Shrinking the log won’t do anything for you as the SQL Server will simply grow the file back out to a larger size. All you are doing is giving yourself a false sense of security when it comes to how much free drive space that you have.
There are also some internal technical reasons to not do this as well which you can read a little about on Paul Randal’s blog. (If Paul tells me he’s got a more specific post I’ll post that link as well.)
Denny
