Skip to main content
added 406 characters in body
Source Link
J.D.
  • 39.5k
  • 12
  • 60
  • 134
  • If you are in fullFull or bulk_loggedBulk_Logged recovery model, then you need to do regular logLog backups. If you are in fullFull or bulk_loggedBulk_Logged recovery and don't do logLog backups, then there is nothing that will empty ("truncate") the logTransaction Log - so the logLog file with grow and grow and grow...
  • If you don't want to do logLog backups, then you will set the recovery modelRecovery Model to simpleSimple.

After you have sorted out above, you will perform a shrink of the logLog file.

Which Recovery Model you should use will depend on what level of granularity you need for backup restores. If you need closer to realtime backups than the Full backup, then continue to use the Full Recovery Model, and begin taking Log backups. Otherwise, if the last Full backup is as granular of a restore point as you need, switching to Simple Recovery Model is likely what you should do.

Here's an article I wrote on the topic: https://karaszi.com/large-transaction-log-file

  • If you are in full or bulk_logged recovery model, then you need to do regular log backups. If you are in full or bulk_logged recovery and don't do log backups, then there is nothing that will empty ("truncate") the log - so the log file with grow and grow and grow...
  • If you don't want to do log backups, then you will set the recovery model to simple.

After you have sorted out above, you will perform a shrink of the log file.

Here's an article I wrote on the topic: https://karaszi.com/large-transaction-log-file

  • If you are in Full or Bulk_Logged recovery model, then you need to do regular Log backups. If you are in Full or Bulk_Logged recovery and don't do Log backups, then there is nothing that will empty ("truncate") the Transaction Log - so the Log file with grow and grow and grow...
  • If you don't want to do Log backups, then you will set the Recovery Model to Simple.

After you have sorted out above, you will perform a shrink of the Log file.

Which Recovery Model you should use will depend on what level of granularity you need for backup restores. If you need closer to realtime backups than the Full backup, then continue to use the Full Recovery Model, and begin taking Log backups. Otherwise, if the last Full backup is as granular of a restore point as you need, switching to Simple Recovery Model is likely what you should do.

Here's an article I wrote on the topic: https://karaszi.com/large-transaction-log-file

Source Link
Tibor Karaszi
  • 18k
  • 2
  • 15
  • 29

  • If you are in full or bulk_logged recovery model, then you need to do regular log backups. If you are in full or bulk_logged recovery and don't do log backups, then there is nothing that will empty ("truncate") the log - so the log file with grow and grow and grow...
  • If you don't want to do log backups, then you will set the recovery model to simple.

After you have sorted out above, you will perform a shrink of the log file.

Here's an article I wrote on the topic: https://karaszi.com/large-transaction-log-file