There are three methods available for database recovery model:
Recovery Model:
Every SQL Server database consists of at least two physical files on the hard drive, an MDF file and an LDF file. The MDF file contains all of the actual data being stored. The LDF file, or Log file, contains a record of each data change. This logging of each data change makes undo operations and “point in time” backups possible. A point in time backup gives us the ability to restore a database to literally any point in time we wish, yesterday, two hours ago, or two minutes ago. By default, both the MDF and LDF files are located in the SQL Server Server\Version\MSSQL\Data directory on the hard drive.
This recorded history doesn't remain in the transition log forever though. If it did, the size of the log file over time would become huge and unmanageable. Instead, the log file is periodically cleared, or “Truncated”. The amount of time the log file is allowed to grow before being truncated is determined by the database “Recovery Model”.
A “Recovery Model” determines how a database’s transaction logs are maintained. Each database has its own Recovery Model setting. Meaning a SQL Server can contain multiple databases, each with its own Recovery Model, separate of how other database are configured.
Simple Recovery:
When a database is set to Simple Recovery, it means log files are not kept permanently. So when a TSQL statement executes, changes are written to the data and log files, but they are not kept in the log file for long before being Truncated (cleared). This truncating is caused by SQL Server issuing a “Check Point”.
What this Truncating of the log file means to us is that the log file cannot be used for a database Restore. This is because we are not in control of when the log is cleared, SQL Servers checkpoints are. Therefore, when a database is set to Simple Recovery, the only backup type available is a Full Backup.
A Full backup restores all your data, you can’t tell it to restore all my data at a specific point in time. For point in time, we need a transaction log.
Full Recovery:
Not to be confused with “Full Backups”, Full Recovery refers to a database that keeps a transaction log file history. Because the log file will now be an integral part of the database, thought must be taken as to its creation. When creating a database, there are two file options to consider, “Initial Size”, and “Auto-growth”, both of which can be configured from the SQL Server Management Studios Database Properties screen. Under the Files page, there are settings for both Size and Growth.
If a database is set to Full Recovery, then the history of each data change operation is saved. If the log file files up or runs out of physical disk space, the database will stop working. This risk can be mitigated by SQL Server’s ability to Auto Grow the log file. To enable Auto Grow, give the log file an initial size in Megabytes. Next, select the “Autogrowth” check box and enter a growth size. The max size of the log file can also be restricted or left to grow indefinably. Without auto-growth, a fixed size for the log file has to be guessed. If the log file fills up, the database stops working. Auto-growth avoids this, but comes with some added overhead. When SQL Server increases the log file size dynamically, it can be resource (memory, CPU, and hard disk) intensive, and database performance may suffer while this happens. Once the log has been adjusted, performance will return to normal.
Bulk Logged:
In bulk logged mode, most transactions are stored in the transaction log, but some bulk operations such as bulk loads or index creation are not logged.
It is full mode with one difference; bulk load operations are minimally-logged. The log backup and truncation methods apply here just as they did in full mode so nothing really changes. This isn’t a mode you’ll actually run your database in though. If you have a need to backup your logs for full recovery, you’ll run in full mode, and only switch to this mode when you need to do large bulk load operations. Switching to bulk mode for these operations allows you to minimally log them to not only greatly reduce the size of the log, but also to speed the operation.
- Simple Recovery.
- Full Recovery.
- Bulk Logged.
Recovery Model:
Every SQL Server database consists of at least two physical files on the hard drive, an MDF file and an LDF file. The MDF file contains all of the actual data being stored. The LDF file, or Log file, contains a record of each data change. This logging of each data change makes undo operations and “point in time” backups possible. A point in time backup gives us the ability to restore a database to literally any point in time we wish, yesterday, two hours ago, or two minutes ago. By default, both the MDF and LDF files are located in the SQL Server Server\Version\MSSQL\Data directory on the hard drive.
This recorded history doesn't remain in the transition log forever though. If it did, the size of the log file over time would become huge and unmanageable. Instead, the log file is periodically cleared, or “Truncated”. The amount of time the log file is allowed to grow before being truncated is determined by the database “Recovery Model”.
A “Recovery Model” determines how a database’s transaction logs are maintained. Each database has its own Recovery Model setting. Meaning a SQL Server can contain multiple databases, each with its own Recovery Model, separate of how other database are configured.
Simple Recovery:
When a database is set to Simple Recovery, it means log files are not kept permanently. So when a TSQL statement executes, changes are written to the data and log files, but they are not kept in the log file for long before being Truncated (cleared). This truncating is caused by SQL Server issuing a “Check Point”.
What this Truncating of the log file means to us is that the log file cannot be used for a database Restore. This is because we are not in control of when the log is cleared, SQL Servers checkpoints are. Therefore, when a database is set to Simple Recovery, the only backup type available is a Full Backup.
A Full backup restores all your data, you can’t tell it to restore all my data at a specific point in time. For point in time, we need a transaction log.
Full Recovery:
Not to be confused with “Full Backups”, Full Recovery refers to a database that keeps a transaction log file history. Because the log file will now be an integral part of the database, thought must be taken as to its creation. When creating a database, there are two file options to consider, “Initial Size”, and “Auto-growth”, both of which can be configured from the SQL Server Management Studios Database Properties screen. Under the Files page, there are settings for both Size and Growth.
If a database is set to Full Recovery, then the history of each data change operation is saved. If the log file files up or runs out of physical disk space, the database will stop working. This risk can be mitigated by SQL Server’s ability to Auto Grow the log file. To enable Auto Grow, give the log file an initial size in Megabytes. Next, select the “Autogrowth” check box and enter a growth size. The max size of the log file can also be restricted or left to grow indefinably. Without auto-growth, a fixed size for the log file has to be guessed. If the log file fills up, the database stops working. Auto-growth avoids this, but comes with some added overhead. When SQL Server increases the log file size dynamically, it can be resource (memory, CPU, and hard disk) intensive, and database performance may suffer while this happens. Once the log has been adjusted, performance will return to normal.
Bulk Logged:
In bulk logged mode, most transactions are stored in the transaction log, but some bulk operations such as bulk loads or index creation are not logged.
It is full mode with one difference; bulk load operations are minimally-logged. The log backup and truncation methods apply here just as they did in full mode so nothing really changes. This isn’t a mode you’ll actually run your database in though. If you have a need to backup your logs for full recovery, you’ll run in full mode, and only switch to this mode when you need to do large bulk load operations. Switching to bulk mode for these operations allows you to minimally log them to not only greatly reduce the size of the log, but also to speed the operation.
No comments:
Post a Comment