SQL Management Studio - LDF - Database Recovery mode when setup for DR - FULL vs SIMPLE

1/30/2019 1:53 AM

10/17/2025 4:20 PM

Database Recovery Model - Changing Recovery Model to Simple
To view or change the recovery model of a database
 

Network Performance Monitor

Open SQL Studio Manager. If SQL Studio Manager is not installed, download the installer from Install SQL Server Management Studio 

1. In Object Explorer, after you connect to the appropriate instance of the Microsoft SQL Server Database Engine, click the server name to expand the server tree.
2. Expand Databases. Depending on the database, select either a user database or expand System Databases and select a system database.
3. Right-click the database, then click Properties. The Database Properties dialog box opens.
4. In the Select, a Page pane, click Options. In the Recovery model list box, the current recovery model is displayed.
5. To change the recovery model select a different model list. The choices are Full, Bulk-logged, or Simple. 
   • NOTE: SolarWinds recommends that you use the Simple recovery model unless you have a SQL cluster. If you have a SQL cluster set up for high availability (HA), you must use the Full recovery model.

"WHY do we need to change from FULL to SIMPLE?" 

Some customers prefer to have FULL Recovery, usually as their main concerns are because they most likely DB cluster setup on their Orion DB and they do not wish to turn off full Recovery.

Mirroring can be done with Log shipping, DB Snapshots, or 3^rd Party software. But this is up to the Customer to implement. 

• Database Mirroring Best Practices and Performance Considerations 

As SQL Server is 3^rd Party, SQL Server support is limited on that, so please consult with your DBA. NPM Customers do not usually need High Availability\Protection Recovery for a product like NPM, whereas High Performance is critical. So where possible we recommend changing recovery to SIMPLE Mode where possible and also see our Disaster Recovery documents.

Having a FULL recovery Model causing a lot of overhead to give such redundancy and with WRITE Intensive DB like Orion NPM and especially with Netflow that could cause the LDF File to bottleneck and grow out of control. SIMPLE recovery is not as intensive and can handle intensive writes to it.

See:     

• A transaction log grows unexpectedly or becomes full in SQL Server 
• Backup Under the Simple Recovery Model 

Here is a Good explanation of Simple VS Full Recovery: 

Recovery Models (SQL Server) 

Transaction Log Size 

The transaction log should be sized based on the number of data modifications made to a database and the frequency of the log backups. Large data modifications, such as data loads or index rebuilds should be taken into account when calculating a log file size.

• In the Simple Recovery Model, the transaction log should not grow as the interval between checkpoints (which truncate the log) is based on the number of data modifications made. If the log does grow, it may be that there are long-running transactions or transactions that have been left open. Either may indicate a problem with the application.
• In the Full or Bulk-Logged Recovery Model if the transaction log grows it may indicate that the frequency of data modifications has increased and as such, the interval between log backups should be decreased. It may also indicate long-running transactions or that the log backup jobs are not running properly.

BACKUP Frequently if using FULL Recovery 

Also remember that in order to stop the Transaction LOG from growing excessively, backup frequently when in Full Recovery. Working with Transaction Log Backups  “More frequent log backups have the added advantage of increasing the frequency of log truncation, resulting in smaller log files.”

 

Useful Reference:

• Database recovery model - Change recovery model to Simple