Ahmad Yaseen

SQL Server Transaction Log Interview Questions

October 3, 2019 by

In this article, we will discuss some important SQL Server Transaction Log interview questions that you may be asked during a SQL Server administrator or developer technical job interview.

Q1. What is the main concept of the SQL Server Transaction?

  • A single unit of work that consists of more than one related tasks and these tasks should succeed or fail as one atomic unit

Q2. The SQL Server transaction can be described using the four ACID properties. What does the ACID acronym for? Describe each property individually

  • ACID is the acronym for Atomicity, Consistency, Isolation, and Durability

    • Atomicity indicates that the transaction will succeed as one unit when all the individual tasks are succeeded. Also, if any single task within the transaction failed, the overall transaction will fail
    • Consistency means that the transaction will keep the database in a valid state without affecting its consistency. This can be guaranteed by complying with all foreign keys and constraints, defined on the columns
    • Isolation indicates that each transaction will have its boundary, that keeps it separated from all other transactions that are executed concurrently, without affecting each other
    • Durability guarantees that, if any abnormal system failure or termination issue occurred, the committed transaction result that is already written to the database will not be lost where it will be recovered when the server is back to normal

Q3. What is the difference between the following transaction states: Aborted vs Failed and Active vs Committed?

  • The Aborted transaction is the transaction that does not complete it is executed successfully. The Failed transaction is a transaction that is Aborted without being committed
  • The Active transaction is a running transaction. The Committed transaction is the transaction that completes its execution successfully without any error

Q4. What is the difference between the Local and the Distributed SQL Server transactions?

  • The Local transaction is a SQL Server transaction that is processing data from the local database server
  • The Distributed transaction is a SQL Server transaction that is processing data from more than one database server

Q5. Mention some of the best practices that you should follow when working with SQL Server transactions

  • You need to narrow the scope of the SQL Server transaction
  • Try to retrieve all needed data from your database tables before opening the transaction if possible
  • Make sure to access the least possible amount of data inside the created transaction body
  • It is a disaster to wait for user input inside the transaction body
  • Study the situation and make sure to use the most suitable mode and isolation level for the transactions

Q6. Describe the Write-ahead Logging concept in one sentence

  • It is the process of writing a log record to the SQL Server transaction log file before writing the data pages to the database data file

Q7. What is the difference between the database log file and the database data file when talking about the reading and writing mechanism?

  • The read and write on the log file performed sequentially where they are performed randomly in the case of the database data file

Q8. What is the SQL Server Transaction Log LSN?

  • Each Transaction log record that is written to the SQL Server transaction log file can be identified by its Log Sequence Number (LSN)

Q9. One of your clients receives a transaction log error number 9002. Clarify why this error happened and what should you perform to fix it?

  • This error is an indicator that there is no free space available for writing the new transaction log record in the SQL Server transaction log file due to:

    • No truncate operation performed on the transaction log
    • The database log file Auto-growth property is disabled
    • The database log file Auto-growth is enabled but the disk drive where the transaction log file is stored is running out of free space
  • To fix that issue, you need to identify the cause and perform the proper action such as truncating it, taking TRN backup or extending the current disk drive size

Q10. Describe how the SQL Server Transaction Log truncation process performed

  • Before truncating the SQL Server transaction log, the SQL Server checks first that this transaction is committed, not pending a backup operation or any high availability or change tracking features, and that a Checkpoint is already triggered on that transaction log to mark it as inactive
  • After that, SQL Server identifies the inactive Virtual Log Files, the smallest truncation unit in the transaction log, taking into consideration that the VLF will be considered inactive when there is no active log record available inside this VLF
  • Now, the log truncation process will delete all inactive Virtual Log Files from the transaction log file

Q11. What is the Virtual Log File?

  • In SQL Server, the transaction log is divided logically into smaller units, in which the log records will be written. These small units are called Virtual Log Files

Q12. The SQL Server Engine tries internally to keep the number of SQL Server VLFs as small as possible. Clarify the reason

  • When the SQL Server transaction log file contains a large number of SQL Virtual Log Files, the database will suffer from performance issues during the recovery process
  • Assume that the SQL Server instance is restarted for any reason, when it becomes online again, all databases will be in a recovery state, to restore the database to the latest consistent state. During the recovery process, the SQL Virtual Log Files of the database transaction log file will be read to maintain the database in that consistent state. With a large number of VLFs, the recovery state will take a longer time to bring the database online

Q13. If one of your databases is taking a long time to be recovered after the SQL Server reboot. How could you troubleshoot this issue and fix it?

  • We need to first check if the number of VLFs is large using the DBCC Loginfo command. Having a large number of VLFs can be fixed by following the steps below:

    • Take a SQL Server transaction log backup for that database to make sure that all inactive parts of the transaction log are truncated
    • Run the CHECKPOINT command to perform a manual checkpoint, so that all pages in the buffer will be written to the database files
    • Try to shrink the database log file to the smallest possible amount
    • Check the number of VLFs again. If the number is smaller now, try to set the initial size of the transaction log file to a larger size and enable the file auto-growth option with an increment amount, rather than using percentage, to prevent the frequent small increments that lead to high SQL Server VLFs number issue again

Q14. What is the Recovery Model database property?

  • The recovery model database property describes how the SQL Server Engine will deal with the database transaction logs. This includes specifying what types of backup and restore operations can be performed on this database, the high availability a disaster recovery that the database can participate in, and if the transaction log will be saved to the database transaction log file

Q15. In SQL Server, there are three recovery model types. List these three recovery models and the main differences between them

  • Simple recovery model: The SQL Server transaction logs will be kept in the transaction log file for a short time during the transaction execution. Once a Checkpoint operation is performed to commit this transaction, the transaction log will be truncated. There are two database backup operations supported when the database is configured with Simple recovery model supports, the Full backup, and Differential backup
  • Full recovery model: The transaction logs will be kept in the SQL Server transaction log file for a longer time, where it will stay in the transaction log as inactive when a checkpoint is performed to commit the transaction and will be truncated completely when a SQL transaction log backup is performed. A database with Full recovery model supports the Full, Transaction Log and Differential backup types
  • Bulk-logged recovery model: Similar to the Full recovery model, except that it will use the minimal logging technique to log the bulk operations

Q16. Clarify briefly the role of the SQL Server Transaction Log in maintaining the following High Availability and Disaster Recovery solutions

  • Log Shipping: The Log Shipping depends highly on the transaction logs, where it performs the synchronization process, after restoring the first full backup to the secondary, using the cumulative SQL transaction log backups
  • Transactional Replication: With the help of the Log Reader Agent, any newly written transaction log in the publisher database will be copied to the distribution database
  • Database Mirroring: The synchronization process between the principal and the mirror databases is performed sending the Transaction Log records from the principal database to the mirror database
  • Always On Failover Cluster: No role for the SQL Server Transaction Log in this HA solution, as it is configured at the SQL Server instance level
  • Always On Availability Group: The synchronization between the primary and the secondary replicas is performed by sending the transaction log records from all participating databases in the availability group to all secondary replicas to be written to the database transaction log file and redo it at the secondary replica databases

Q17. Why do we need to take Transaction Log backup when the database is configured with Full recovery model that is not required in case of the Simple recovery model?

  • This is due to how the SQL Server Engine treats the SQL Server transaction logs in each recovery model, where the transaction log will be marked as inactive and truncated after performing a checkpoint operation to commit the active transaction when the database is configured with simple recovery model. But if the database is configured with the Full recovery model, the checkpoint will commit the transaction and mark it as inactive without truncating the transaction log, where it will be waiting for truncation by the transaction log backup operation

Q18. What is the difference between SQL Server Transaction Log Truncation and Shrink operations?

  • In the transaction log truncation process, all inactive VLFs will be deleted from the transaction log file and become available for reuse, without deallocating that space
  • In the transaction log shrink process, all free VLFs will be deallocated and returned to the operating system

Q19. List some of the actions that consume the SQL Server Transaction Log space

  • SQL Server index rebuilds and reorganizes operations
  • No transaction log backup configured on a database configured with the Full recovery model
  • A long-running or uncommitted transaction
  • SQL Server replication or Change Data Capture log agent is paused or failed
  • Database Mirroring or Always On Availability Group is paused or connection failure
  • Long-running Full of Diff backup operation that delayed the transaction log truncation processes

.

Q20. Is it recommended to create multiple Transaction Log files on a database to distribute the load?

  • There is no performance enhancement that you can gain from creating multiple SQL Transaction Log files in your database, as the reading and writing processes on the transaction log file are performed sequentially, with no option to perform parallel I/O operations in the case of multiple transaction log files, where it will keep writing to the first file until it has no free space, then it will start writing to the second one. The only gain with multiple transaction log files is extending the transaction log file size when the current disk drive runs out of free space

.

Q21. Why do we need to locate the database data file and the transaction log file on separate disk drives?

  • Due to the different mechanisms used to perform the read and write operations in the log and data files of the database, where the sequential mechanism used for the transaction log file and the random mechanism used for the database data files. So, it is highly recommended to place the transaction log files on a separated physical drive, to isolate the effect of the simultaneous sequential workload on the random workload

.

Q22. In SQL Server, the database files Auto Shrink option is disabled by default. Clarify the reason

  • Because if you keep removing the available free space, the transaction log file will grow again. And because the space allocation process causes performance degradation as it will take time while zeroing the space by the operating system before it is being allocated

Q23. What are the different reasons behind corrupting the SQL Server Transaction Log file of the database?

  • System abnormal termination without proper shutdown
  • Hardware or software corruption in the I/O subsystem
  • A virus, malicious software or malware attack that damaged the database files or prevent the SQL Server engine from accessing it
  • The transaction log file contains no room for any new transaction log

Q24. What is the role of the Microsoft Distributed Transaction Coordinator?

  • A transaction coordination manager coordinates the COMMIT and ROLLBACK for the distributed transaction within all participating SQL Server instances

Ahmad Yaseen
Latest posts by Ahmad Yaseen (see all)

Related posts:

  1. Top 50 collection of articles on SQL interview questions
  2. Understanding SQL Server database recovery models
  3. SQL interview questions on database backups, restores and recovery – Part I
  4. SQL Server Transaction Log Administration Best Practices
  5. SQL Server Junior Database Administrator Scenario-Related Interview Questions and Answers
SQL interview questions, Transaction log

About Ahmad Yaseen

Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields. He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer. Also, he is contributing with his SQL tips in many blogs. View all posts by Ahmad Yaseen

168 Views