Automatic Recovery
Automatic recovery is a process the server’s executes during every startup to ensure that each database is restored to a consistent state. It happen every time the server is started
Transaction
A transaction is a logical unit of one or more Transact-SQL statements
Consistent State
All committed transactions are reflected on the database device
Any transaction that was not committed is not reflected on the database device(s)
Syslogs ( transaction log )
Typically the whole row is recorded in the transaction log.
‘selects’ are not recorded in the log b’ cos the don’t modify the database.
Transaction log pages and their corresponding data pages are processed in the data cache
Data cache
Data cache is a portion of Adaptive Server memory where data, index and log pages currently in use by the server are held. When pages are not in use by the server, they are stored in the disk.
When any modification is made the corresponding page (data, log or index ) is brought into the cache, if it is not already in the cache. Then the modifications are made to the pages in the cache. Later the modified pages are written to the disk.
Dirty page
A dirty page is a page in the data cache that has been modified but the changes are not made to the disk.
Situations when data, log and index pages are written to the disk
a> “Age out” - no room for the new page in the cache (the least recently used are written to the disk regardless whether the transaction is committed )
b> whenever a checkpoint occurs ( this is the major function of chekcpoint )
c> housekeeper task ( housekeeper also writes pages to disk )
d> on commit of a transaction ( here only the log page is written to the disk )
Write Ahead logs
When changed cache pages are written to the disk, always the log pages are written first and then the data / index pages are written.
Chained and Unchained mode
Chained mode :- implicit begin transaction
Unchained mode:- every statement is treated as a transaction.
Data cache and server failure
If a server failure occurs,
Changes written to the disk are preserved.
Changes written to the data cache but not written to the disk are lost.
When the server restarts,
* If the changes are written to the disk, but the transaction was uncommitted (incomplete) then the transaction is rolled back (undo). Same thing happens when the transaction is aborted. Here, comparison is made between the data and log pages, which are on the disk.
* Likewise roll forward (redo) happens when the log pages are written to disk but data pages are not.
During Automatic Recovery, each database is compared to its transaction log :
a> Changes from incomplete or rolled back transactions are undone
b> Changes from committed transactions are verified
c> System databases are recovered.
d> User databases are recovered.
e> Checkpoint is fired by the server
Checkpoint
A checkpoint is a process that brings cache pages in sync with the disk (writes the dirty pages to the disk) to shorten the amount of time needed for recovery. The checkpoint allows the current transaction to continue but stop any new transaction for a while. Writes a checkpoint record in the transaction log.
Server Execution of Checkpoint
a> Housekeeper
b> Recovery interval in minutes configuration parameter
c> Shutdown with wait
d> Automatic recovery
e> Dump database
f> Dump transaction
* Checkpoint & Housekeeper tasks
Housekeeper tasks :
One work is to write dirty pages to the disk.
There is something as ‘checkpoint server task’, which determines whether it needs to perform a checkpoint or not. If the number of rows in the transaction log (syslogs) is more than 100, it issues a checkpoint.
Checkpoints that occur as a result of the housekeeper task are known as ‘free checkpoints’.
See “CHECKPOINT SLEEP” in sp_who command.
Recovery Interval Checkpoints
Recovery interval in minutes is the parameter, which determines the maximum amount of time the server will take while recovery because depending on this parameter the checkpoints are fired and the dirty pages (data) are writen to the disk. So it takes lees time for recovery as there are less dirty pages to be written to the disk while recovery (or server start up).
If you set recovery interval in minutes to 3, the checkpoint process writes changed pages to disk only when syslogs contains more than 18,000 rows since the last checkpoint.
This may create a brief period of high I/O, called a checkpoint spike.
The Recovery Order
a> master
b> sybsystemdb
c> model
d> tempdb (created by copying model)
e> sybsystemprocs
f> sybsecurity (if installed)
After this, the user databases are recovered.
The users can log on to the server as soon as the system databases are restored but can only access the database after their databases are restored.
sp_dbrecovery_order db_name, n ( to change the recovery order of user databases )
sp_dbrecovery_order db_name, n ( to remove a database from recovery order )
Automatic recovery is a process the server’s executes during every startup to ensure that each database is restored to a consistent state. It happen every time the server is started
Transaction
A transaction is a logical unit of one or more Transact-SQL statements
Consistent State
All committed transactions are reflected on the database device
Any transaction that was not committed is not reflected on the database device(s)
Syslogs ( transaction log )
Typically the whole row is recorded in the transaction log.
‘selects’ are not recorded in the log b’ cos the don’t modify the database.
Transaction log pages and their corresponding data pages are processed in the data cache
Data cache
Data cache is a portion of Adaptive Server memory where data, index and log pages currently in use by the server are held. When pages are not in use by the server, they are stored in the disk.
When any modification is made the corresponding page (data, log or index ) is brought into the cache, if it is not already in the cache. Then the modifications are made to the pages in the cache. Later the modified pages are written to the disk.
Dirty page
A dirty page is a page in the data cache that has been modified but the changes are not made to the disk.
Situations when data, log and index pages are written to the disk
a> “Age out” - no room for the new page in the cache (the least recently used are written to the disk regardless whether the transaction is committed )
b> whenever a checkpoint occurs ( this is the major function of chekcpoint )
c> housekeeper task ( housekeeper also writes pages to disk )
d> on commit of a transaction ( here only the log page is written to the disk )
Write Ahead logs
When changed cache pages are written to the disk, always the log pages are written first and then the data / index pages are written.
Chained and Unchained mode
Chained mode :- implicit begin transaction
Unchained mode:- every statement is treated as a transaction.
Data cache and server failure
If a server failure occurs,
Changes written to the disk are preserved.
Changes written to the data cache but not written to the disk are lost.
When the server restarts,
* If the changes are written to the disk, but the transaction was uncommitted (incomplete) then the transaction is rolled back (undo). Same thing happens when the transaction is aborted. Here, comparison is made between the data and log pages, which are on the disk.
* Likewise roll forward (redo) happens when the log pages are written to disk but data pages are not.
During Automatic Recovery, each database is compared to its transaction log :
a> Changes from incomplete or rolled back transactions are undone
b> Changes from committed transactions are verified
c> System databases are recovered.
d> User databases are recovered.
e> Checkpoint is fired by the server
Checkpoint
A checkpoint is a process that brings cache pages in sync with the disk (writes the dirty pages to the disk) to shorten the amount of time needed for recovery. The checkpoint allows the current transaction to continue but stop any new transaction for a while. Writes a checkpoint record in the transaction log.
Server Execution of Checkpoint
a> Housekeeper
b> Recovery interval in minutes configuration parameter
c> Shutdown with wait
d> Automatic recovery
e> Dump database
f> Dump transaction
* Checkpoint & Housekeeper tasks
Housekeeper tasks :
One work is to write dirty pages to the disk.
There is something as ‘checkpoint server task’, which determines whether it needs to perform a checkpoint or not. If the number of rows in the transaction log (syslogs) is more than 100, it issues a checkpoint.
Checkpoints that occur as a result of the housekeeper task are known as ‘free checkpoints’.
See “CHECKPOINT SLEEP” in sp_who command.
Recovery Interval Checkpoints
Recovery interval in minutes is the parameter, which determines the maximum amount of time the server will take while recovery because depending on this parameter the checkpoints are fired and the dirty pages (data) are writen to the disk. So it takes lees time for recovery as there are less dirty pages to be written to the disk while recovery (or server start up).
If you set recovery interval in minutes to 3, the checkpoint process writes changed pages to disk only when syslogs contains more than 18,000 rows since the last checkpoint.
This may create a brief period of high I/O, called a checkpoint spike.
The Recovery Order
a> master
b> sybsystemdb
c> model
d> tempdb (created by copying model)
e> sybsystemprocs
f> sybsecurity (if installed)
After this, the user databases are recovered.
The users can log on to the server as soon as the system databases are restored but can only access the database after their databases are restored.
sp_dbrecovery_order db_name, n ( to change the recovery order of user databases )
sp_dbrecovery_order db_name, n ( to remove a database from recovery order )
Comments
Post a Comment