Skip to main content

Before switching from FULL to SIMPLE recovery model, ask yourself how much data you can afford to lose. For the databases where in case of a disaster you're fine with restoring the last database backup, SIMPLE should be OK. If this is not the case, stay with FULL.

To shrink the LDFLDF file to as small a size as possible, follow the steps given by Kimberly Tripp here: 8 Steps to better Transaction Log throughput8 Steps to better Transaction Log throughput

  1. Wait for the time when there's low activity on the database

  2. Run in SSMS:

    DBCC SHRINKFILE(transaction_log_logical_filename, TRUNCATEONLY)

    DBCC SHRINKFILE(transaction_log_logical_filename, TRUNCATEONLY)
    
  3. Modify the transaction log file size:

    ALTER DATABASE db_name MODIFY FILE ( NAME = transaction_log_logical_filename, SIZE = new_size)

    ALTER DATABASE db_name
    MODIFY FILE ( NAME = transaction_log_logical_filename, SIZE = new_size)
    

Before switching from FULL to SIMPLE recovery model, ask yourself how much data you can afford to lose. For the databases where in case of a disaster you're fine with restoring the last database backup, SIMPLE should be OK. If this is not the case, stay with FULL

To shrink the LDF file to as small a size as possible, follow the steps given by Kimberly Tripp here: 8 Steps to better Transaction Log throughput

  1. Wait for the time when there's low activity on the database

  2. Run in SSMS

    DBCC SHRINKFILE(transaction_log_logical_filename, TRUNCATEONLY)

  3. Modify the transaction log file size

    ALTER DATABASE db_name MODIFY FILE ( NAME = transaction_log_logical_filename, SIZE = new_size)

Before switching from FULL to SIMPLE recovery model, ask yourself how much data you can afford to lose. For the databases where in case of a disaster you're fine with restoring the last database backup, SIMPLE should be OK. If this is not the case, stay with FULL.

To shrink the LDF file to as small a size as possible, follow the steps given by Kimberly Tripp here: 8 Steps to better Transaction Log throughput

  1. Wait for the time when there's low activity on the database

  2. Run in SSMS:

    DBCC SHRINKFILE(transaction_log_logical_filename, TRUNCATEONLY)
    
  3. Modify the transaction log file size:

    ALTER DATABASE db_name
    MODIFY FILE ( NAME = transaction_log_logical_filename, SIZE = new_size)
    
Source Link

Before switching from FULL to SIMPLE recovery model, ask yourself how much data you can afford to lose. For the databases where in case of a disaster you're fine with restoring the last database backup, SIMPLE should be OK. If this is not the case, stay with FULL

To shrink the LDF file to as small a size as possible, follow the steps given by Kimberly Tripp here: 8 Steps to better Transaction Log throughput

  1. Wait for the time when there's low activity on the database

  2. Run in SSMS

    DBCC SHRINKFILE(transaction_log_logical_filename, TRUNCATEONLY)

  3. Modify the transaction log file size

    ALTER DATABASE db_name MODIFY FILE ( NAME = transaction_log_logical_filename, SIZE = new_size)