lundi 1 décembre 2014

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


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.


eg:



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:



KAV 6 REPLICATION 0
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 its being replicated.


Full sys.databases info:



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


Update: 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.





Aucun commentaire:

Enregistrer un commentaire