As we know that without any transaction we cannot perform any task in the database. To maintain every field in the database, we need to perform transactions for a particular change. These transactions are very important for every database and make it interactive for users.SQL server also dealing with these transaction logs and stores them into a separate file. Below is a brief description of these transaction logs.
At first we will discuss about transaction log and then we will further know about its storage.
Nature of a transaction log and the purpose of using it in SQL server
The nature of a transaction log is totally depends on its properties. Transaction log uses following four properties to complete its task in SQL server database.
- Atomicity
- Consistency
- Isolation
- Durability
Below is a small introduction of the above four properties:
- 1. Atomicity:
- The Atomicity defines that if all the operations of a work unit are not executed properly, then the transaction will be considered a failed transaction and the previous Transaction will come in its initial position.
- 2. Consistency:
- This property tells that, after a committed transaction the database is completely changing or not its previous state.
- 3. Isolation:
- This property makes transactions to able to remain independent of each other.
- 4. Durability:
- It ensures the persistency of a committed transaction in case of system failure.
This above four properties tell the nature of a transaction. The main purpose of using transaction log in SQL server is that we can bring back our database from a critical condition if any failure occurs in the system. Now the point is, where are these transaction logs stored in SQL server?
What Is A Log File And What It Stores?
After completing a successful transaction, it is very important to store the transactions in a separate file. The log file stores all the log events (insert, delete, update …etc) executed in SQL server and the size of a log file depends on users’ choice. It is necessary to have one log file for every database and you can have more than one log file for a database. The file extension for log files is “.ldf”.
Why Do We Need To Store The Transactions In The Log File?
It is very important to store the transaction logs into a log file, although they follow the A.C.I.D property. But after all, the transaction logs are very critical components of the database. If any system failure occurs, then you can recover your database from this file. Only the committed transactions are stored in a log file in a sequential manner. We can recover our update, delete or insert and creation tables and all other logged operations through this file and also can roll back the deleted and truncated transactions.
Mainly the log file contains all the transactions logs in a sequential order and maintains it for future use. If any failure occurs in the system, then you can manage and recover your database from this file.
Is a log file can be corrupt?
Yes, the log file can also be corrupt. Because it is also a system file so it can also be corrupted by some input/output subsystem. A log file can corrupt if the database is in the process and suddenly a power failure occurs.
Causes that can corrupt a log file
As if we consider the corruption reason of a log file, we see that the hardware part is more responsible for this corruption issue, they can be an operating system, device drivers, cables, networks and file system drivers... Etc.
In such condition just detect the defected hardware part and immediately change it. Otherwise again, it could be the cause of the problem.
Some other reasons that can corrupt a log file
- Compression in data files size and unsupported folders can be the cause of corruption.
- PSS is also found as a bug in the Microsoft product so it could be the cause of corruption.
SQL server provides some recovery options that can recover your corrupted database. But it depends on you that which option you should have to choose.
Which Option Is Better For Database Recovery?
Recovery depends on whether you are recovering a log file or the transaction logs. If you are recovering the transaction logs so it depends on your recovery models to which recovery model you have chosen to recover your database. But to recover the transactions is not a good option because the database recovery depends on your recovery model. So to rebuild the log file is a perfect option for you.
How To Rebuild A Corrupted Log File?
Implementing the following steps in SQL server can rebuild a corrupted log file.
- The sp_detach_db command helps you to detach your database. To detach your database at first you need to alter your database and switch it to emergency mode.
Syntax:
ALTER DATABASE database_name { < set_database_options > } [;] < set_database_options >::= SET { { < optionspec > [ ,...n ] [ WITH < termination > ] } } < optionspec >::= { < db_state_option > } < db_state_option >::= { ONLINE | OFFLINE | EMERGENCY } - Now you can modify the corrupt Transaction Log File(s) by performing delete or rename operations and then, create database, and use FOR ATTACH_REBUILD_LOG option to attach your database while rebuilding the Log File(s).
Syntax:
CREATE DATABASE database_name
ON < filespec > [,..n ]
FOR { ATTACH_REBUILD_LOG }
[;]
FOR ATTACH_REBUILD_LOG
It tells that how to create a database by attaching an existing operating system file. This option will only read/write the databases. If there is any damage in a transaction log file, then the log file is built again.
The ATTACH_REBUILD_LOG expect the following:
- The database should fully closed
- All the MDF and NDF file should be available
Important:
This following operation destroys the log backup chain. After the operation to make a full database backup is a good option to avoid the problem of recovery in the future. It is very important for every database to keep secure the log files. If the above method failed to recover your database, use a SQL log file recovery software.
0 comments:
Post a Comment