What is a Transactional Log? Why it is required for Database?
Transactional Log : It is an Integral Part of the Database and is used to recover the database in the event of failure.Each and Every action that occurs in the Database are stored in the Transactional Log.A Database must have atleast one transactional log file.You cannot create a Database without transactional log file.You can create
more than one transactional log file in a single Database.Transactional log is required for database to ensure integrity, to allow transactional rollbacks and database recovery.
Minimum and Maximum size of Transactional log :
Minimum is 512 KBMaximum is 2TB
How SQL uses the Transactional log :
When changes are made to a database,those changes are first written (hardened) to the log file and the data pages are changed in memory. Once the record of the changes is in the log, the transaction is considered complete. The data pages will be written to the disk at a later time either by the lazy writer or by the checkpoint process.Transaction log entries are considered active until the data pages that were modified by that transaction have been written to disk. Once that occurs, the log entries are considered inactive and are no longer necessary for database recovery.
When a server is restarted, SQL uses the transaction log to see if, at the point the server shut down there were any transactions that had completed but whose changes may not been written to disk, or any transactions that had not completed. If there are then the modifications that may not have been written to disk are rolled forward and any that had not completed are rolled back. This is done to ensure that the database is in a consistent state after a restart.
Transactional Log Architecture :
The transaction log for any database is managed as a set of virtual log files (VLFs) whose size is determined internally by SQL Server based on the total size of all the log files and the growth increment used when enlarging the log. When a log file is first created, it always has between 2 and 16 VLFs. If the file size is 1 MB or less, SQL Server divides the size of the log file by the minimum VLF size [31 * 8 KB] to determine the number of VLFs. If the log file size is between 1 and 64 MB, SQL Server splits the log into 4 VLFs. If the log file is greater than 64 MB but less than or equal to 1 GB, 8 VLFs are created. If the size is more than 1 GB, there will be 16 VLFs. When the log grows,the same formula is used to determine how many new VLFs
to add. A log always grows in units of entire VLFs and can be shrunk only to a VLF boundary.
A VLF can be in one of four states: ACTIVE,RECOVERABLE,REUSABLE,UNUSED
ACTIVE : The active portion of the log begins at the minimum LSN representing an active (uncommitted) transaction. The active portion of the log ends at the last LSN written.Any VLFs that contain any part of the active log are considered active VLFs.
RECOVERABLE : The portion of the log Previous the oldest active transaction is needed only
to maintain a sequence of log backups for restoring the database to a former state.
REUSABLE : If transaction log backups are not being maintained or if you have already backed up the log, VLFs before the oldest active transaction are not needed and can be reused.
Truncating or backing up the transaction log changes recoverable VLFs into reusable VLFs.
UNUSED : One or more VLFs at the physical end of the log files might not have been used yet if not enough logged activity has taken place or if earlier VLFs have been marked as
reusable and then reused.
You can observe the properties of virtual log files by executing the undocumented command DBCC LOG INFO in all Databases including System databases.Here Iam providing some screenshots taken by me.
After seeing this pictures you can observe that In all databases the startoffset is 8192.Even when you create
a database by using T-SQL or SSMS the startoffset value wont change.
When you create a database using T-SQL or SSMS the transactional log size will be vary.The default value
of transactional log is 504KB through T-SQL and through SSMS it is 1024KB.
After seeing this pictures you can observe that In all databases the startoffset is 8192.Even when you create
a database by using T-SQL or SSMS the startoffset value wont change.
When you create a database using T-SQL or SSMS the transactional log size will be vary.The default value
of transactional log is 504KB through T-SQL and through SSMS it is 1024KB.
No comments:
Post a Comment