Tuesday 13 March 2012

Logging and Transaction

SQL Server ensures that any change to the abstracts is ACID-compliant, i.e. it uses affairs to ensure that the database will consistently backslide to a accepted constant accompaniment on failure. Each transaction may abide of assorted SQL statements all of which will alone accomplish a abiding change to the database if the aftermost account in the transaction (a COMMIT statement) completes successfully. If the COMMIT auspiciously completes the transaction is cautiously on disk.

SQL Server accouterments affairs application a write-ahead log.

Any changes fabricated to any folio will amend the in-memory accumulation of the page, accompanying all the operations performed will be accounting to a log, forth with the transaction ID which the operation was a allotment of. Each log access is articular by an accretion Log Sequence Number (LSN) which is acclimated to ensure that all changes are accounting to the abstracts files. Additionally during a log restore it is acclimated to analysis that no logs are bifold or skipped. SQL Server requires that the log is accounting assimilate the disc afore the abstracts folio is accounting back. It charge additionally ensure that all operations in a transaction are accounting to the log afore any COMMIT operation is appear as completed.

At a afterwards point the server will checkpoint the database and ensure that all pages in the abstracts files accept the accompaniment of their capacity synchronised to a point at or afterwards the LSN that the checkpoint started. Aback completed the checkpoint marks that allocation of the log book as complete and may chargeless it (see Simple transaction logging vs Full transaction logging). This enables SQL Server to ensure candor of the data, alike if the arrangement fails.

On abortion the database log has to be replayed to ensure the abstracts files are in a constant state. All pages stored in the cycle advanced allotment of the log (not apparent as completed) are rewritten to the database, aback the end of the log is accomplished all accessible affairs are formed aback application the cycle aback allocation of the log file.

The database agent usually checkpoints absolutely frequently. However, in a heavily loaded database this can accept a cogent achievement impact. It is accessible to abate the abundance of checkpoints or attenuate them absolutely but the rollforward during a accretion will booty abundant longer

No comments:

Post a Comment