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.
Best Answer
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.
log_reuse_wait_descwas set toREPLICATION.Solution
After restoring the database enable publication and remove it: