Submit a ticketCall us

Get a crash course on Network Monitoring delivered right to your inbox
This free 7-day email course provides a primer to the philosophy, theory, and fundamental concepts involved in IT monitoring. Lessons will explain not only how to perform various monitoring tasks, but why and when you should use them. Sign up now.

Home > Success Center > Network Performance Monitor (NPM) > SQL Mgt Studio - LDF - Database Recovery mode when setup for DR - FULL vs SIMPLE

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

Updated March 15th, 2016

Overview

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

Environment

SQL Studio Manager

Steps

Open SQL Studio Manager. If SQL Studio Manager is not installed, download the installer from Microsoft® SQL Server® 2008 Management Studio Express (© 2017 Microsoft, available at https://www.microsoft.com/, obtained on February 13, 2016.).

  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 have mirroring on their Orion DB and they do not wish to turn off full Recovery.

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

 

As SQL Server is 3rd 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 to change recovery to SIMPLE Mode where possible and also see our Disaster Recovery documents.

Official Solarwinds Documentation

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 LDF File to bottleneck and grow out of Control. SIMPLE recovery is not as intensive and can handle to intensive writes to it.

See:     

Here is a Good explanation of Simple VS Full Recovery:

Recovery Models (SQL Server) (© 2017 Microsoft, available at https://msdn.microsoft.com/, obtained on February 13, 2017)

555.jpg

Transaction Log Size

The transaction log should be sized based on the amount 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 Simple Recovery Model the transaction log should not grow as the interval between checkpoints (which truncate the log) is based on the amount 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 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 Transaction LOG from growing excessively, backup frequently when in Full Recovery. Working with Transaction Log Backups / (© 2017 Microsoft, available at https://msdn.microsoft.com/, obtained on February 13th, 2017). “More frequent log backups have the added advantage of increasing the frequency of log truncation, resulting in smaller log files.”

 

Useful Reference:

 

Disclaimer: Please note, any content posted herein is provided as a suggestion or recommendation to you for your internal use. This is not part of the SolarWinds software or documentation that you purchased from SolarWinds, and the information set forth herein may come from third parties. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment.  You elect to use third party content at your own risk, and you will be solely responsible for the incorporation of the same, if any.

Last modified
14:43, 13 Feb 2017

Tags

Classifications

Public