Add persistent log buffer to a database

Applies to: SQL Server

This topic describes how to add and remove a persistent log buffer to a database in SQL Server 2016 (13.x) and above using Transact-SQL.

Permissions

Requires the ALTER permission on the database.

Configure persistent memory device (Linux)

To configure a persistent memory device in Linux.

Configure persistent memory device (Windows)

To configure a persistent memory device in Windows.

Add a persistent log buffer to a database

The volume or the mount point for the new log file must be formatted with DAX enabled (NTFS) or mounted with the DAX option (XFS/EXT4).

Use the following syntax to add a persistent log buffer to an existing database. The syntax differs depending on the version of SQL Server.

Add persistent log buffer in SQL Server 2017 (14.x) and later

SQL

ALTER DATABASE [DB] SET PERSISTENT_LOG_BUFFER = ON (DIRECTORY_NAME = 'path-to-directory-on-a-DAX-volume');

For example:

SQL

ALTER DATABASE WideWorldImporters SET PERSISTENT_LOG_BUFFER = ON (DIRECTORY_NAME = 'F:\SQLTLog');

The name of the persistent log file buffer is generated automatically. The size of the file is always 20 megabytes.

Add persistent log buffer in SQL Server 2016 (13.x)

SQL

ALTER DATABASE [DB] ADD LOG FILE
(
NAME = [DAXlogLogicalName],
FILENAME = 'path-to-log-file-on-a-DAX-volume',
SIZE = 20 MB
);

For example:

SQL

ALTER DATABASE WideWorldImporters ADD LOG FILE
(
NAME = wwi_log2, 
FILENAME = 'F:\SQLTLog\wwi_log2.pldf',
SIZE = 20 MB
);

The log buffer file on the DAX volume will be sized at 20 megabytes regardless of the size specified with the ALTER DATABASE ADD LOG FILE command.

Remove a persistent log buffer from a database

To safely remove a persistent log buffer, the database must be placed in single user mode in order to drain the persistent log buffer.

When you remove a persistent log buffer, the log buffer file on disk is deleted.

The syntax differs depending on the version of SQL Server.

Remove persistent log buffer in SQL Server 2017 (14.x) and later

SQL

ALTER DATABASE [DB] SET PERSISTENT_LOG_BUFFER = OFF;

For example:

SQL

ALTER DATABASE WideWorldImporters SET PERSISTENT_LOG_BUFFER = OFF;

Remove persistent log buffer in SQL Server 2016 (13.x)

SQL

ALTER DATABASE [DB] SET SINGLE_USER;
ALTER DATABASE [DB] REMOVE FILE [DAXlogLogicalName];
ALTER DATABASE [DB] SET MULTI_USER;

For example:

SQL

ALTER DATABASE WideWorldImporters SET SINGLE_USER;
ALTER DATABASE WideWorldImporters REMOVE FILE wwi_log2;
ALTER DATABASE WideWorldImporters SET MULTI_USER;

Limitations

Transparent Data Encryption (TDE) is not compatible with persistent log buffer.

Availability Groups can only use this feature on secondary replicas due to the requirement by the log reader agent for standard log writing semantics on the primary. However, a small log file must be created on all nodes (ideally on DAX volumes or mounts). In the event of a failover, the persistent log buffer path must exist, in order for the failover to be successful.

Dikkat

If the persistent log buffer path or file isn't present during an Availability Group failover event, or database startup, the database enters a RECOVERY PENDING state until the issue is resolved.

Interoperability with other PMEM features

When both persistent log buffer and Hybrid Buffer Pool are enabled, along with the start up trace flag 809, Hybrid Buffer Pool will operate in what is known as Direct Write mode.

Back up and restore operations

Normal restore conditions apply. If persistent log buffer is restored to a DAX volume or mount, it continues to function. If the log is restored to a non-DAX disk volume, it can be safely removed using the ALTER DATABASE REMOVE FILE command.

Next steps

• How It Works (It Just Runs Faster): Non-Volatile Memory SQL Server Tail Of Log Caching on NVDIMM
• Transaction Commit latency acceleration using Storage Class Memory in Windows Server 2016/SQL Server 2016 SP1
• Hybrid Buffer Pool