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.
在本文中，我们将讨论一些重要SQL Server事务日志采访问题，在SQL Server管理员或开发人员技术工作采访中可能会询问您。
Q1. What is the main concept of the SQL Server Transaction?
Q1。 SQL Server事务的主要概念是什么？
- 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
Q2。 可以使用四个ACID属性描述SQL Server事务。 ACID的缩写是什么？ 分别描述每个属性
ACID is the acronym for Atomicity, Consistency, Isolation, and Durability
ACID是Atomicity ， Consistency ， Isolation和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?
Q3。 以下事务状态之间的区别是什么：中止与失败以及活动与提交？
- 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 中止而未提交的事务
- 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?
Q4。 本地和分布式SQL Server事务之间有什么区别？
- Local transaction is a SQL Server transaction that is processing data from the local database server 本地事务是一个SQL Server事务，正在处理本地数据库服务器中的数据
- Distributed transaction is a SQL Server transaction that is processing data from more than one database server 分布式事务是一种SQL Server事务，正在处理来自多个数据库服务器的数据
Q5. Mention some of the best practices that you should follow when working with SQL Server transactions
Q5。 提及使用SQL Server事务时应遵循的一些最佳做法
- You need to narrow the scope of the SQL Server transaction 您需要缩小SQL Server事务的范围
- 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
Q6。 用一句话描述预写日志记录的概念
- 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 这是在将数据页写入数据库数据文件之前将日志记录写入SQL Server事务日志文件的过程。
Q7. What is the difference between the database log file and the database data file when talking about the reading and writing mechanism?
Q7。 在谈论读写机制时，数据库日志文件和数据库数据文件有什么区别？
- sequentially where they are performed 按顺序执行，对于数据库数据文件则randomly in the case of the database data file 随机进行
Q8. What is the SQL Server Transaction Log LSN?
Q8。 什么是SQL Server事务日志LSN？
- Log Sequence Number (LSN) 日志序列号 （LSN）标识写入SQL Server事务日志文件的每个事务日志记录。
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?
Q9。 您的一位客户收到交易记录错误号9002 。 弄清为什么会发生此错误，以及应如何解决？
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:
此错误表明，由于以下原因，没有可用空间可用于在SQL Server事务日志文件中写入新的事务日志记录：
- 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 要解决该问题，您需要确定原因并执行适当的操作，例如将其截断，进行TRN备份或扩展当前磁盘驱动器的大小。
- SQL Server Transaction Log ArchitectureSQL Server事务日志体系结构SQL Server Transaction Log Growth Monitoring and ManagementSQL Server事务日志增长监视和管理。
Q10. Describe how the SQL Server Transaction Log truncation process performed
Q10。 描述如何执行SQL Server事务日志截断过程
- 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 在截断SQL Server事务日志之前，SQL Server首先检查该事务是否已提交，没有等待备份操作或任何高可用性或更改跟踪功能，并且已经在该事务日志上触发了一个检查点以将其标记为非活动状态。
- 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 之后，SQL Server会识别非活动虚拟日志文件（事务日志中最小的截断单元），并考虑到当该VLF中没有可用的活动日志记录时，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?
Q11。 什么是虚拟日志文件？
- 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
Q12。 SQL Server引擎在内部尝试使SQL Server VLF的数量保持尽可能小。 弄清原因
- 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 当SQL Server事务日志文件包含大量SQL虚拟日志文件时，数据库将在恢复过程中遭受性能问题的困扰
- 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 假定由于某种原因重新启动了SQL Server实例，当该实例再次联机时，所有数据库都将处于恢复状态，以将数据库还原到最新的一致状态。 在恢复过程中，将读取数据库事务日志文件SQL虚拟日志文件，以将数据库保持在该一致状态。 使用大量的VLF，恢复状态将需要较长时间才能使数据库联机
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?
Q13。 SQL Server重新启动后，如果您的数据库之一需要很长时间才能恢复。 您如何解决此问题并加以解决？
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:
我们首先需要使用DBCC Loginfo命令检查VLF的数量是否很大。 可以通过以下步骤解决具有大量VLF的问题：
- Take a SQL Server transaction log backup for that database to make sure that all inactive parts of the transaction log are truncated 为该数据库执行SQL Server事务日志备份，以确保事务日志的所有不活动部分均被截断
- Run the CHECKPOINT command to perform a manual checkpoint, so that all pages in the buffer will be written to the database files 运行CHECKPOINT命令以执行手动检查点，以便将缓冲区中的所有页面都写入数据库文件
- 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 再次检查VLF的数量。 如果该数字现在较小，请尝试将事务日志文件的初始大小设置为较大的大小，并以增量数量而不是使用百分比来启用文件自动增长选项，以防止频繁的小增量导致价格过高。 SQL Server VLF编号再次出现
Q14. What is the Recovery Model database property?
Q14。 恢复模型数据库属性是什么？
- 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 恢复模型数据库属性描述了SQL Server Engine如何处理数据库事务日志。 这包括指定可以对此数据库执行哪些类型的备份和还原操作，数据库可以参与的灾难恢复的高可用性以及是否将事务日志保存到数据库事务日志文件中
Q15. In SQL Server, there are three recovery model types. List these three recovery models and the main differences between them
Q15。 在SQL Server中，有三种恢复模型类型。 列出这三种恢复模型以及它们之间的主要区别
- 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 简单的恢复模型 ：在执行事务期间，SQL Server事务日志将在事务日志文件中保留一小段时间。 一旦执行了Checkpoint操作以提交此事务，事务日志将被截断。 使用简单恢复模型支持配置数据库时，支持两种数据库备份操作： 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 完全恢复模型 ：事务日志将在SQL Server事务日志文件中保留更长的时间，在执行检查点提交事务时，事务日志将在事务日志中保持为非活动状态，而在SQL事务中将被完全截断日志备份已执行。 具有完全恢复模型的数据库支持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
Q16。 简要阐明SQL Server事务日志在维护以下高可用性和灾难恢复解决方案中的作用
- 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 日志传送 ：日志传送高度依赖于事务日志，在使用累积SQL事务日志备份将第一个完整备份还原到辅助日志之后，它会执行同步过程
- 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故障转移群集 ：此HA解决方案中SQL Server事务日志没有作用，因为它是在SQL Server实例级别配置的
- 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
- Always On可用性组 ：通过将可用性组中所有参与数据库的事务日志记录发送到所有辅助副本，以将其写入数据库事务日志文件并在辅助副本上重做，来执行主副本和辅助副本之间的同步。资料库
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?
Q17。 如果数据库配置了完全恢复模型，而简单恢复模型则不需要完全恢复模型，为什么我们需要进行事务日志备份？
- 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 这是由于SQL Server引擎如何处理每个恢复模型中SQL Server事务日志，当使用简单恢复模型配置数据库时，在执行检查点操作以提交活动事务后，事务日志将被标记为非活动并被截断。 但是，如果数据库配置了完全恢复模型，则检查点将提交事务并将其标记为不活动，而不会截断事务日志，它将在其中等待事务日志备份操作的截断
Q18. What is the difference between SQL Server Transaction Log Truncation and Shrink operations?
Q18。 SQL Server事务日志截断和收缩操作之间有什么区别？
- log truncation process, all inactive VLFs will be deleted from the transaction log file and become available for reuse, without deallocating that space 日志截断过程中，所有不活动的VLF将从事务日志文件中删除，并且可以重新使用，而无需分配该空间
- In the transaction log shrink process, all free VLFs will be deallocated and returned to the operating system 在事务日志缩减过程中，所有可用的VLF将被释放并返回到操作系统
Q19. List some of the actions that consume the SQL Server Transaction Log space
Q19。 列出一些消耗SQL Server事务日志空间的操作
- SQL Server index rebuilds and reorganizes operations SQL Server索引重建和重组操作
- 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 SQL Server复制或更改数据捕获日志代理已暂停或失败
- 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 长时间运行的Diff备份完整操作延迟了事务日志截断过程
Q20. Is it recommended to create multiple Transaction Log files on a database to distribute the load?
Q20。 是否建议在数据库上创建多个事务日志文件以分配负载？
- 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 在数据库中创建多个SQL事务日志文件不会带来任何性能增强，因为对事务日志文件的读取和写入过程是按顺序执行的，如果有多个事务日志文件，则无法选择执行并行I / O操作事务日志文件，它将继续写入第一个文件，直到没有可用空间为止，然后它将开始写入第二个文件。 当前磁盘驱动器的可用空间不足时，具有多个事务日志文件的唯一好处是扩展了事务日志文件的大小
Q21. Why do we need to locate the database data file and the transaction log file on separate disk drives?
Q21。 为什么我们需要在单独的磁盘驱动器上找到数据库数据文件和事务日志文件？
- 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
Q22。 在SQL Server中，默认情况下禁用数据库文件“自动收缩”选项。 弄清原因
- 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?
Q23。 破坏数据库SQL Server事务日志文件的背后有哪些不同的原因？
- System abnormal termination without proper shutdown 系统异常终止而未正确关闭
- Hardware or software corruption in the I/O subsystem I / O子系统中的硬件或软件损坏
- A virus, malicious software or malware attack that damaged the database files or prevent the SQL Server engine from accessing it 破坏数据库文件或阻止SQL Server引擎访问它的病毒，恶意软件或恶意软件攻击
- The transaction log file contains no room for any new transaction log 事务日志文件不包含任何新事务日志的空间
- How to Rebuild a Database with a Corrupted or Deleted SQL Server Transaction Log File如何使用损坏或删除SQL Server事务日志文件重建数据库。
Q24. What is the role of the Microsoft Distributed Transaction Coordinator?
Q24。 Microsoft分布式事务处理协调器的作用是什么？
- A transaction coordination manager coordinates the COMMIT and ROLLBACK for the distributed transaction within all participating SQL Server instances 事务协调管理器为所有参与SQL Server实例内的分布式事务协调COMMIT和ROLLBACK
翻译自: https://www.sqlshack.com/sql-server-transaction-log-interview-questions/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
