Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2007

    Unanswered: Minimizing the entries to Transaction Log

    Hi All,

    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.

    Thanks in Advance


  2. #2
    Join Date
    Nov 2005
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts