Tag Archives: SQL Server 2012

Database Issue: The transaction log for database ‘db_name’ is full due to ‘ACTIVE_BACKUP_OR_RESTORE’.

Today, I was called in the early hours because the transaction log of one of the SQL Server 2016 database was full. I logged onto the SQL server instance via SQL Server Management Studio, and saw the following errors in the SQL Server error log:

The transaction log for database 'OLTPDB' is full
due to 'ACTIVE_BACKUP_OR_RESTORE'.

Reason for failure

This occurs because the full backup for the ‘OLTPDB’ database was still running, and as a result, the transaction log of this database was not freed up for reuse. This caused the transactions that were running against this database to fail.

Issue resolution

As we know, the portion of the transaction log file that is used during the full backup operation must be maintained while the full backup operation is running, which will also be backed up at the end of the full backup operation. That’s because the full database backup contains all the database data at the start of the full backup operation plus the transition log used until the end of the full backup operation. Therefore, to fix the issue, I had to manually increase the size of the transaction log file, to accommodate the high load against the database.

Transparent Data Encryption (TDE) with Database Mirroring

Database security is essential for the organisations working in healthcare and financial sector because the databases in such organisations contain sensitive and confidential information about their customers. The organisation working in such sectors also falls under the boundaries of data regulatory and compliance requirements (such as HIPAA, PCI-DSS, SSAE16 etc.) which require the encryption of sensitive data inside databases and at operating system level. SQL Server offers a variety of options for encryption which includes Transparent Data Encryption (TDE). This feature is available in since the release of SQL Server 2008 and provides us the real-time encryption of data and log files. The Transparent Dat Encryption (TDE) also encrypts backups, snapshots and transaction logs of the database.

Checkout my article here in which I discussed the architecture of Transparent Data Encryption (TDE), and the process of implementing Transparent Data Encryption (TDE) on standalone and mirrored database.

This article is published on SSWUG.org.