Today I was called by the operation team, said that a drive in a database server running SQL Server going to be out of free space. I found a database that its transaction log was much bigger than how it should be. The scheduled transaction log backup was fine (I'm using the Ola's maintenance solution), and no any active transactions that preventing log truncation was found by the DBCC OPENTRAN() command running in the context of that database. However, DBCC OPENTRAN told me that the database has a publication of transactional replication configured. Then I queried the log_reuse_wait_desc column of the sys.databases system catalog view, which told me that the reuse of transaction log space was waiting on REPLICATION. Then I figured out it was the replication log reader agent got some problem. Below screenshot shows the error message of the log reader agent, by checking from the Replication Monitor > right-click that Log Reader Agent > View Details:
The error message was "
Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission". This message is quite misleading, but the root cause is the
database owner of the publication database is invalid, which is mostly due to the database was created by a login and then the login was being removed. In order to solve it, the easiest solution is to set
sa as the database owner, no matter you are just enabled Windows authentication or the sa login is disabled, you can still use the sa login as a valid database owner. It can be set in the database property file tab:
Now the replication resumed running normally. But if there are too many non-distributed replication records, drop and create the publication and subscription again may be a faster alternative, and that's why you must always remember to generate creation script for all new and changed publications.
