Unanswered: Minimizing the entries to Transaction Log
I have a DB of transaction log grown to 19GB. I have some questions.
1. Is there any way that we can minimize the entries to transaction log.
2. If i use Transactions in my stored procedure will it be usefull to automatically remove the transaction entries in transaction log once after the commit transaction is given.
3. Is there a way to reduce my current transaction log and not to grown in such a manner futher.
1. No. Every DML and DDL operation is logged.
2. Again, everthing is logged, whether you use explicit transactions (BEGIN TRAN... COMMIT TRAN) or implicit transactions.
3. Yes. You can back up the database on scheduled intervals. This will not shrink the physical file, but it will mark "old" backed up virtual log files as reusable, which allows SQL Server to reuse them insted of expanding the physical log file.
You could also set the recovery model to "Simple". This will allow SQL Server to reuse virtual log files once all transactions are committed/rolled back and the data in the data pages referred to by all transactions are written to disk.
Take a look at "transaction logs [SQL Server], architecture" to better understand what transaction logs are and how SQL Server uses them.
I have just given you a very simplified summary on how it works.