How to change Integration Services Catalog (SSISDB) database Master Key encryption password?

To change the Integration Services Catalog (SSISDB) database Master Key encryption password, run the following Transact-SQL statement:

USE [SSISDB];
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = N'[old_password]'; -- Password used when creating SSISDB
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = N'[new_password]';
GO

In the above Transact-SQL fragment, we first open the Integration Services Catalog (SSISDB) database Master Key with the existing password, and then regenerated it with a new one.

Advertisements

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.