SQL Server Recovery Model affect Performance ?

Leave a comment (0) Go to comments

What is SQL Server Recovery model ?

SQL Server Database recovery models allow you to specify the way SQL Server manages transactions and how much data can be recovered in case of disaster.

There are three type of recovery models are available in SQL Server and each of these models represents a different approach to balancing the tradeoff between conserving disk space and data recovery options in case of disaster.

How many recovery model are available in SQL Server ?

There are three type of recovery models are available in SQL Server and each of these models represents a different approach to balancing the tradeoff between conserving disk space and data recovery options in case of disaster.

  • Simple Recovery Model
  • Bulk Logged Recovery Model
  • Full Recovery Model

How to check current recovery model for a database ?

We can check Database Recovery model of database from it’s property, which can be check either by using TSQL function DATABASEPROPERTYEX or by using SSMS

TSQL

-- CHECK CURRENT Recovery Model for Database : AdventureWorksDW
SELECT DATABASEPROPERTYEX('AdventureWorksDW', 'Recovery') As [Recovery Model]
GO

OUTPUT

SQL Server Recovery Model affect Performance ? SQL Server recovery model affects SQL Server Performance recovery model

OR by SSMS

SQL Server Recovery Model affect Performance ? SQL Server recovery model affects SQL Server Performance recovery model

How to change SQL Server Recovery Model ? (sql server change recovery model)

How to set a recovery model for a SQL server database ?

We can change Database Recovery model of database by altering database option, which can be check either by using TSQL function DATABASEPROPERTYEX or by using SSMS

TSQL

-- Change Recovery Model for Database : AdventureWorksDW to FULL
ALTER DATABASE AdventureWorksDW SET RECOVERY FULL;

or by changing value from SSMS in Recovery Model option.

SQL Server Recovery Model affect Performance ? SQL Server recovery model affects SQL Server Performance recovery model

What is default recovery model for a new database ?

By default SQL Server sets FULL recovery model for all newly created database as it copies values from model database.

Incase you want to change default recovery model for new database, change recovery model for MODEL SQL Server system database.

Can we change recovery model for System Database ?

YES, we can change recovery model

Database Name Default Recovery Model Can be changed or not
master Simple Not Applicable,technically, YES you can set to FULL or BULK logged but the database continues to operate as simple recovery model only.  you can not perform BACKUP LOG even you change that to full / bulk logged
model FULL can be changed and set to FULL or BULK Logged
msdb SIMPLE can be changed and set to FULL or BULK Logged
tempdb SIMPLE Can not be changed
distribution SIMPLE YES, but distribution db just stores metadata and history data for replication, but a FULL recovery model is recommended.
resource N/A recovery model is not relevant to resource database

What is the best recovery model, which should be used?

Where Data is critical and data loss can not be afforded a FULL recovery model is suggested

But for development servers where database can be refreshed by production, and space is concern, then SIMPLE recovery mode can be turned on.

Does Recovery Model affects database transaction log ?

YES,The full recovery model might consume all available disk space, if appropriate database maintenance is not performed as log keep growing.

Does SQL Server recovery model affects SQL Server Performance ?

Technically, small transaction didn’t shown any performance affect but yes, recovery model affect database performance for large transactions.

Full logged provide slow response time for large / bulk operations where bulk logged / simple gears up large transaction.

Does the recovery model effect the database size / disk ?

YES,The full recovery model might consume all available disk space, if appropriate database maintenance is not performed as log keep growing.

Is SQL Server recovery mode and Recovery model is same thing ?

YES, is one of the same thing.

Does SQL Server recovery model affects your database recovery?

YES, your data safety/recovery  depends on recovery model in case of disaster.

  • Simple Recovery Model – suits for databases in which data changes infrequently.
  • Full Recovery Model – best suits for production OLTP systems where full recovery from damaged media is the highest priority.
  • Bulk-logged Recovery Model - Similar to the full recovery model, the bulk logged recovery model uses both database and log backups to re-create a database.

What is the best recovery model for recovery point of view ?

Where Data is critical and data loss can not be afforded a FULL recovery model is the only option as this supports point in time recovery.

Which recovery model gives a best sql server database performance ?

Simple Recovery Model  – It allows for the fastest bulk operations and the simplest backup-and-restore strategy.

Bulk_Logged Recovery Model  – allows recovery in case of media failure and gives best performance using least log space for certain bulk operations like BULK INSERT, bcp, CREATE INDEX etc.

What is differences between full and simple recovery model ?

Check my previous blog post, which lists 10 differences between FULL vs SIMPLE Recovery model, http://goo.gl/0h37O

EOF - SQL Server Recovery Model affect Performance ?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.