Hi Carlos,
First, you need to analyze why your transaction log is filling up so much. Presumably it has been auto-growing to reach 100 GB in size. Are you regularly running transaction log backups? If not, you should schedule them, at least once a day (for a development server; much more frequently for a production server), using either the SQL Server Management Studio or the DBACOCKPIT tools in your SAP system. The transaction log backup will automatically truncate the log of committed transactions after successful completion, thus preventing it from continuously filling up.
It will not, however, shrink the file after truncation. You should determine the appropriate size for your transaction log file so that it is large enough to manage typical transaction load on the system between backups, but not so large that it locks up an unreasonable amount of disk space. I think 10 MB is going to be far too small. You might start with about 10% of the size of your database and then see how it goes from there, fine-tuning it.
Shrinking the log to the appropriate size may take a few iterations. The shrink file operation releases the space in the log at the tail end, after the most recent transaction. However, there may be 'empty' log segments in the file ahead of that transaction. Thus, it may take a couple of iterations of truncating the log (via backup), then shrinking it, then truncating it again, then shrinking it again. Sometimes you can do these operations one right after the other, in a row, to get the result you want. Sometimes you may need to let some activity occur in the log, then truncate and shrink it again.
You can read up on more technical detail about why shrinking the log works this way here: Shrinking the Transaction Log.
Regards,
Matt
.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.