KB2963658 - FIX: The TDE enabled database stops responding indefinitely on WRITELOG or LOGBUFFER wait in SQL Server

Applies To
SQL Server 2008 Service Pack 3 SQL Server 2008 Standard SQL Server 2008 Developer SQL Server 2008 Enterprise SQL Server 2008 R2 Service Pack 2 SQL Server 2008 R2 Developer SQL Server 2008 R2 Enterprise SQL Server 2008 R2 Standard

Symptoms

Assume that you have a database that has transparent data encryption (TDE) enabled in Microsoft SQL Server 2008 or SQL Server 2008 R2, any write operation against the database waits indefinitely on WRITELOG or LOGBUFFER.

When the database is in full recovery mode and scheduled log backups are being done regularly, the first and last log sequence number (LSN) numbers in SQL Server error logs are same for consequent log backups after the issue is hit. It is still advised to confirm if any user activity is expected during this timeframe. The LSN numbers may not change because there is no write operation caused by the lack of user activity. Related SQL Server error log entries are following:

Note

Log was backed up. Database: <DataBaseName>, creation date(time): <Date><Time>, first LSN: 76383:11154:1, last LSN: 76383:11154:1….. This is an informational message only. No user action is required.
Log was backed up. Database: <DataBaseName>, creation date(time): <Date><Time>, first LSN: 76383:11154:1, last LSN: 76383:11154:1….. This is an informational message only. No user action is required.

This issue is not specific to database in full recovery mode, databases with other recovery modes are also prone to encounter the same issue. Additionally, you may also see a SPID in rollback state in system table sys.sysprocesses.

Cause

This issue occurs because of some underlying exception around TDE.

Resolution

The issue was first fixed in the following cumulative update of SQL Server. After you apply this hotfix, SQL Server takes the database offline when you hit the issue (instead of waiting indefinitely on WRITELOG or LOGBUFFER), the database can be brought back online without restarting SQL Server services. In some scenarios the database will be brought back online automatically by SQL Server without any human intervention. Relevant information will be logged in SQL Server error logs.

The hotfix will not resolve the issue completely. However, additional diagnostic information (based on type and error severity) may be logged in the SQL Server error logs when the issue occurs again. You have to identify the actual exception and fix it with the help of additional diagnostic information that you might retrieve after you apply the hotfix.

Cumulative Update 13 for SQL Server 2008 R2 SP2 /en-us/help/2967540

Cumulative Update 17 for SQL Server 2008 SP3 /en-us/help/2958696

About cumulative updates for SQL Server

Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:

      

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.