Transaction Log won't shrink, DB thinks it is replicating

Question

I've got a SQL Server 2008 R2 Express database running Kaspersky Security Center, and I have no idea under what circumstances the install happened, but the database appears to think that it's being replicated and will not free any space from the transaction log. e.g.:

USE master;

SELECT 
    name, log_reuse_wait, log_reuse_wait_desc, is_cdc_enabled 
FROM 
    sys.databases 
WHERE 
    name = 'KAV';

SELECT DATABASEPROPERTYEX('KAV', 'IsPublished');

returns:

name | log_reuse_wait | log_reuse_wait_desc | is_cdc_enabled
-----|----------------|---------------------|---------------
KAV  | 6              | REPLICATION         | 0

DATABASEPROPERTYEX('KAV', 'IsPublished')
----------------------------------------
0 [not published]

Also there's nothing listed in the Replication section in SSMS.

So far I've tried a couple statements gleaned from Google results:

USE KAV;
EXEC sp_repldone null, null, 0,0,1;
EXEC sp_removedbreplication KAV;

But I've had no luck in getting this DB to stop thinking it's being replicated.

Full sys.databases info:

+-----------------------------------+------------------------------------------------------------+
| name                              | KAV                                                        |
| database_id                       | 5                                                          |
| source_database_id                | NULL                                                       |
| owner_sid                         | 0x0105000000000005150000004EB006B0C3554AB049CEA01BE8030000 |
| create_date                       | 2013-07-04 10:31:28.947                                    |
| compatibility_level               | 90                                                         |
| collation_name                    | Latin1_General_CI_AS                                       |
| user_access                       | 0                                                          |
| user_access_desc                  | MULTI_USER                                                 |
| is_read_only                      | 0                                                          |
| is_auto_close_on                  | 0                                                          |
| is_auto_shrink_on                 | 0                                                          |
| state state_desc                  | ONLINE                                                     |
| is_in_standby                     | 0                                                          |
| is_cleanly_shutdown               | 0                                                          |
| is_supplemental_logging_enabled   | 0                                                          |
| snapshot_isolation_state          | 1                                                          |
| snapshot_isolation_state_desc     | ON                                                         |
| is_read_committed_snapshot_on     | 1                                                          |
| recovery_model                    | 1                                                          |
| recovery_model_desc               | FULL                                                       |
| page_verify_option                | 2                                                          |
| page_verify_option_desc           | CHECKSUM                                                   |
| is_auto_create_stats_on           | 1                                                          |
| is_auto_update_stats_on           | 1                                                          |
| is_auto_update_stats_async_on     | 0                                                          |
| is_ansi_null_default_on           | 1                                                          |
| is_ansi_nulls_on                  | 1                                                          |
| is_ansi_padding_on                | 1                                                          |
| is_ansi_warnings_on               | 1                                                          |
| is_arithabort_on                  | 1                                                          |
| is_concat_null_yields_null_on     | 1                                                          |
| is_numeric_roundabort_on          | 0                                                          |
| is_quoted_identifier_on           | 1                                                          |
| is_recursive_triggers_on          | 0                                                          |
| is_cursor_close_on_commit_on      | 0                                                          |
| is_local_cursor_default           | 1                                                          |
| is_fulltext_enabled               | 1                                                          |
| is_trustworthy_on                 | 0                                                          |
| is_db_chaining_on                 | 0                                                          |
| is_parameterization_forced        | 0                                                          |
| is_master_key_encrypted_by_server | 0                                                          |
| is_published                      | 0                                                          |
| is_subscribed                     | 0                                                          |
| is_merge_published                | 0                                                          |
| is_distributor                    | 0                                                          |
| is_sync_with_backup               | 0                                                          |
| service_broker_guid               | 19C05AF5-8686-4C27-BF7E-93E240DA953B                       |
| is_broker_enabled                 | 0                                                          |
| log_reuse_wait                    | 6                                                          |
| log_reuse_wait_desc               | REPLICATION                                                |
| is_date_correlation_on            | 0                                                          |
| is_cdc_enabled                    | 0                                                          |
| is_encrypted                      | 0                                                          |
| is_honor_broker_priority_on       | 0                                                          |
+-----------------------------------+------------------------------------------------------------+

Also:

DBCC OPENTRAN;
No active open transactions.

DBCC SQLPERF(LOGSPACE);
KAV 171066  99.55339    0

EXEC sp_replcounters;
KAV 0   0   0   0x00000000000000000000  0x00000000000000000000

I've also just performed full data and log backups.

I've run across a few posts with very similar situations, and the solution given was to set up replication Publishing and Distributing and then remove it again. However, this being Express Edition, these options do not even appear for me.

We're primarily a Linux shop and this the the only SQL Server instance we've got. If all else fails getting a real license might be our only recourse: to restore a backup to a non-Express instance and try to setup then remove a Publication, then finally restore back to Express.

score 9 · Answer 1 · edited Jul 29 '17 at 11:04

Solution for restoring a published database

We faced a similar problem: A published database is stored on Server1. Every day this database will be backed up and restored on Server2.

We frequently got error messages:

LOG full due to REPLICATION
log_reuse_wait_desc was set to REPLICATION.
Replication could not be removed, because this database was not published on Server2.

Solution

After restoring the database enable publication and remove it:

USE MyDatabase
GO
-- 1.) enable publication for MyDatabase
EXEC sp_replicationdboption 
  @dbname = 'MyDatabase', 
  @optname = N'publish', 
  @value = N'true';
GO
-- 2.) remove publication from database. Use the PUBLICATION-name (not database name)
sp_removedbreplication 'Publ_MyDatabase','both'

-- 3.) disable publication for MyDatabase
EXEC sp_replicationdboption 
  @dbname = 'MyDatabase', 
  @optname = N'publish', 
  @value = N'false';
GO

-- Verify: log_reuse_wait_desc should have changed from REPLICATION to NOTHING
SELECT name, log_reuse_wait_desc, * FROM sys.databases WHERE name = 'MyDatabase'

score 3 · Answer 2 · edited Nov 24 '15 at 14:01

I had the exact same issue. The SQL Express DB was never part of a replication. In the past it was repaired with some DBCC checkdb commands. And at some time we discovered that

SELECT name, log_reuse_wait_desc 
FROM sys.databases

has showed "REPLICATION" as reason and the logfile growing.

We removed replication using this tsql:

declare @db as varchar(100) = 'dbname'

exec sp_removedbreplication @db

That solved it and we could shrink the log.

score 1 · Answer 3 · answered Dec 01 '14 at 22:14

Is it acceptable to have downtime on this database? This was probably either restored from a replicated database or it was possibly a subscriber that was improperly removed, though that is unlikely. You could try doing a backup from express and restoring to a standard or higher edition then setting up replication again and removing it. Then you can backup from standard and restore to express. As long as you don't enable any features on the database while at the higher edition, there shouldn't be a problem downgrading. You can test this out in advance of an actual outage to ensure it will remove the status and script it all out to minimize downtime. If you don't have another server you can use, grab the evaluation copy and install on your local machine, a VM, the original machine if it is acceptable, or anywhere you can find. You have limited options with express as you have observed.

Pixelated · Answer 4 · 2014-12-03T10:12:05.397

Have you tried setting the database to not publish?

use master
exec sp_replicationdboption @dbname = N'<DATABASENAME>', @optname = N'publish', @value = N'false'
GO

and then backing up the log to see what happens?

Edit 1 : What does the following t-sql return?

-- Run on publisher database for Pub, subscriber information

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT  sa.name AS ArticleName,
        sp.name AS PublicationName,
        d.datasource AS Distributor,
        s.dest_db AS Destination_DB,
        srv.srvname AS SubscriptionServer
FROM    dbo.syspublications sp  
LEFT JOIN
        dbo.sysarticles sa 
        on sp.pubid = sa.pubid 
LEFT JOIN
        dbo.syssubscriptions s 
        on sa.artid = s.artid 
LEFT JOIN
        master.dbo.sysservers srv 
        on s.srvid = srv.srvid 
OUTER APPLY 
        (
        SELECT  datasource
        FROM    master.dbo.sysservers
        WHERE   srvstatus & 8 <> 0
        ) d

score 1 · Answer 5 · edited Jul 29 '17 at 11:01

I would try the following:

USE <database_name_here>
GO
EXEC sp_repldone 
    @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

After which you could try adding a replication and removing a replication for an individual table in the database as suggested in post further down.

We had a database at one time that switched to replication mode even though distribution and replication had not been setup on the SQL Server.

I couldn't find the original script I had used for my issue, so I ran a search and came across this entry at MSDN:

log_reuse_wait_desc = replication, transaction log won't stop growing

There is some unspecific root cause for this issue and it happens all over the world.

Good hunting!

score 0 · Answer 6 · edited Mar 17 '23 at 14:06

0

Was looking for a solutions on other sites and could not find something that worked. I had the log_reuse_wait_desc set to REPLICATION, although I never set up any replication.

What helped me was:

USE YOURDB
GO
EXEC sp_removedbreplication 'YOURDB'
GO
CHECKPOINT
GO

After that log_reuse_wait_desc was set back to LOG_BACKUP.

edited Mar 17 '23 at 14:06

John K. N.

18,854
14
56
117

answered Mar 17 '23 at 13:49

isamux

101
1

score -1 · Answer 7 · answered Dec 05 '14 at 16:12

If you've tried everything else then perhaps it would be possible (making sure you have a good backup first!) to detach the database, rename the log file (so SQL Server cannot find it) and then re-attach the database. I believe this will force SQL Server to create a new log file. Whether it will also stop thinking that the database is replicated I have no idea, but it seems at least possible.

Transaction Log won't shrink, DB thinks it is replicating

7 Answers7

Solution for restoring a published database

Solution

Linked

Related