SQL Server Performance

Trx Log stop auto shrink ?

Discussion in 'SQL Server 2005 General DBA Questions' started by yhchan2005, Jul 11, 2011.

  1. yhchan2005 Member

    Hi,

    i have create a maintenance plan to do the daily full backup for my DB. i have set the Auto Shrink to true and Recovery Model for the DB is Full. All the while it is working find and the Trx LOg is auto shrink back to 1024k after the full backup.

    on last week, i have done a batch delete and also the batch insert record, which cause the Trx Log file growth until 11GB and start from that day, the auto shrink is stop working.

    i have manual do the shrink log file ( as attach in the picture ) but the file size still remain 11GB, please help !!!!
  2. mmarovic Active Member

  3. yhchan2005 Member

    thanks for your comment and will turn the auto shrink off.

    but my question is, why previously the Log file can be shrink, but now cannot ? is that something wrong in the log file ? how to check it and fixed it ?
  4. mmarovic Active Member

    Have you backed-up the transaction log? Only transaction log backup releases log entries, marking them ready for reuse which means they can be shrinked as well. However, the point is that not only autoshrink is bad, any shrink is actually bad, with exception for cleaning up one time mess that happened, but it will never happen again.
  5. yhchan2005 Member

    finally manage to shrink it. I need to run 2 time ( must ) the Trx backup and do the shrinking. Notice that the shrink process will have impact to the Table fragmentation, if i am doing Log shipping and the Log file will be reset back to 1024K after trx log backup, will it have any performance and fragmentation issue ?
  6. satya Moderator

    By default the virtual log space has been filled up and waiting to truncate or backup to a log file, where the CHECKPOINT can occur. In your case the large batch delete has occupied the log file as 1 transaction and in such case you can view the OPEN TRANSACTIONS on the database.

    The log file SHRINK operation may not have any performance affect, but if in case the process that requires larger transaction log space will slow down due to the time taking to increase the .LDF file size. So I would suggest to set the Transaction log size to an optimal value such as 10GB if you do not have any space issues on that disk.

Share This Page