Today we found a PI Asset Framework database (PIFD) with a runaway transaction log taking up more than 95 GB on a 100 GB LUN in development. The database was in SIMPLE recovery model, so it should not have been bloating up. Attempts to shrink the log ...
DBCC SHRINKFILE (N'PIFD_log' , 0, TRUNCATEONLY)
... returned no error but didn't succeed in shrinkage. Any DDL commands resulted in this error:
The transaction log for database 'PIFD' is full due to 'REPLICATION'.
Replication is not configured on this system: there is no distribution database, no publication, nor any subscription. However, sys.databases showed it was configured to wait until commands had been replicated before releasing log space:
SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'PIFD'
This returned "REPLICATION" for log_reuse_wait_desc. I tried to reset this using sp_removedbreplication:
USE [PIFD];
EXEC sp_removedbreplication [PIFD];
Because this did not work (log_reuse_wait_desc was still set to REPLICATION), I looked at the setting for change data capture (CDC):
SELECT name, is_cdc_enabled FROM sys.databases
I verified that somehow CDC was enabled, so I disabled it (this is development, so I can shoot first and ask questions later):
USE PIFD;
EXEC sys.sp_cdc_disable_db;
Then I was able to shrink the log with DBCC SHRINKFILE, but I had to set/reset the recovery model to FULL and SIMPLE to get sys.databases to show that the database setting for log_reuse_wait was no longer set to REPLICATION.
Researching this a bit more, I realized that the table changes had piled up because we had turned off the SQL Agent to troubleshoot some PI Server connectivity errors (some SSIS jobs connecting to a local PI server were failing due to some archive/buffer errors on the corresponding PI system), and the OSISoft maintenance job that uses CDC on this PIFD database had failed to run, causing the PIFD log file to bloat. In production or test, I would not fiddle with a PI database directly as OSISoft wants users to use OSISoft utilities to administer their databases. So the next step is to chat with development about how to restore this database. My guess is we'll just restore from a recent backup.
Comments