18

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.

1
  • How get full sys.databases info and format it ?
    – Kiquenet
    Feb 25, 2021 at 13:15

7 Answers 7

9

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'
1
  • 1
    How-get PUBLICATION-name ? any SQL using sys.* ?
    – Kiquenet
    Feb 25, 2021 at 13:19
2

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.

1

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.

2
  • Downtime isn't a significant issue with the database since it's simply running a centralized update/licensing server for our AV. [Also it was down for a few days before I noticed] However, as I mentioned in the comments, we're primarily a Linux shop and this is our only MSSQL instance. Also the backup is 180GB+, so shipping it out to an external provider is not an option either.
    – Sammitch
    Dec 1, 2014 at 23:52
  • You can install another instance on the same box and restore the backup of that database, space permitting. Alternatively, you can take a backup and then detach the database from express and attach to the evaluation copy and try to setup/remove a publication. Worst case, you screw up the original and have to drop it and restore the backup. Best case, it works, you detach from the evaluation and reattach to express and then uninstall the evaluation. Dec 2, 2014 at 14:28
1

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
1
  • For me, Microsoft SQL Server 2016 (SP2-GDR) (KB4583460) - 13.0.5103.6 (X64) Nov 1 2020 00:13:28 not exists dbo.syspublications
    – Kiquenet
    Feb 25, 2021 at 14:58
1

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!

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.

-1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.