0

Is it possible to reduce the initial log-file size of a DB. See the screen-shoot below, the log-file shouldn't be more than 500 MB with an autogrowth of 100 MB. I dont want to shrink the file, i want to reduce the allocated space to 500 MB. How do i do it?

enter image description here

itsAftab
  • 159
  • 1
  • 2
  • 6
  • 1
    Also please read http://dba.stackexchange.com/questions/29829/why-does-the-transaction-log-keep-growing-or-run-out-of-space – Aaron Bertrand Feb 05 '16 at 15:18

1 Answers1

0

Depend on your database activity and database recovery mode you can manage TLOG size in different ways.

First of all to reduce TLOG usage you have to make transactions smaller.

If the database is in FULL recovery mode, do TLOG backups often.

As lots of guru's say - don't touch TLOG and TempDB they grew as they need to.

Oleg Strutinskii
  • 223
  • 2
  • 12
  • The DB in FULL. As you can see in the screen-shoot the TLOG doesn't use more than 1 % of allocated space, therefore it should only have 500 MB. We take a full back-up with TLOG in the morning at 05:00 and then two DIFF. backups during the day. Also, there is a shrink job that runs 06:00. – itsAftab Feb 05 '16 at 08:43
  • Try to take backup TLOG every 15 minutes, watch for TLOG growth - http://dbadiaries.com/monitoring-sql-server-transaction-log-growth-using-dbcc-sqlperflogspace. – Oleg Strutinskii Feb 05 '16 at 08:46
  • @itsAftab, the current size of the transaction log isn't neccessarily the maximum size it can reach. – Daniel Hutmacher Feb 05 '16 at 08:56