Is there dbo for shrinking the Transaction Log?



  • I'm looking for a System Stored Procedure to shrink our log file. Our database is in Simple mode but the log grows out of control, so once a week, I would like to call upon the procedure to shrink the log file.

    I am already using dbo.DatabaseBackup and dbo.IndexOptimize which have worked great! Thank you so much for those.

    Thank you,



  • https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver15 does what you are looking for, but if you know the log will grow back again in a relatively short time then that probably isn't what you want. If it is going to grow back anyway you can't reuse the space (you need to leave it ready for that growth) and you are reducing performance in the circumstances where the log does need to grow rather than having the space already allocated.

    If you think the growth is unnecessary ("out of control") then you should look into what is happening during the periods of growth to see if those operations can be improved. Improving those activities will likely make them more efficient and improve your application's processing efficiency as well as the databases storage space efficiency. A common issue that is easy to improve its large updates that include a lot of noop updates, for instance UPDATE SomeTable SET AnInt = 123 will result in every row being updated even if 99% of them already have AnInt equalling 123, with all the log activity that involves. UPDATE SomeTable SET AnInt = 123 WHERE AnInt <> 123 would fix that simple example (be a little more careful of the columns can be NULL).


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2