August 9, 2017 at 11:56 am
The database is in simple mode and when I try to shrink the log
USE db1
GO
DBCC SHRINKFILE('db1_log', 20
)
GO
The transaction log for database db1 is full due to 'REPLICATION'
We have never used replication.
August 9, 2017 at 12:13 pm
Check this, are all zeros?
select
name,
is_published,
is_subscribed,
is_merge_published,
is_distributor
from sys.databases
August 9, 2017 at 12:22 pm
SQL Guy 1 - Wednesday, August 9, 2017 12:13 PMCheck this, are all zeros?
select
name,
is_published,
is_subscribed,
is_merge_published,is_distributor
from sys.databases
Check the flag for CDC as well so add: is_cdc_enabled
Sue
August 9, 2017 at 12:58 pm
Sue_H - Wednesday, August 9, 2017 12:22 PMSQL Guy 1 - Wednesday, August 9, 2017 12:13 PMCheck this, are all zeros?
select
name,
is_published,
is_subscribed,
is_merge_published,is_distributor
from sys.databases
Check the flag for CDC as well so add: is_cdc_enabled
Sue
Yes it does and I trued to disable the CDC I get the following error
Msg 22831, Level 16, State 1, Procedure sp_cdc_disable_db_internal, Line 262 [Batch Start Line 8]
Could not update the metadata that indicates database db1 is not enabled for Change Data Capture. The failure occurred when executing the command '(null)'. The error returned was 9002: 'The transaction log for database 'client_705' is full due to 'REPLICATION'.'. Use the action and error to determine the cause of the failure and resubmit the request.
August 9, 2017 at 1:34 pm
Guras - Wednesday, August 9, 2017 12:58 PMYes it does and I trued to disable the CDC I get the following error
Msg 22831, Level 16, State 1, Procedure sp_cdc_disable_db_internal, Line 262 [Batch Start Line 8]
Could not update the metadata that indicates database db1 is not enabled for Change Data Capture. The failure occurred when executing the command '(null)'. The error returned was 9002: 'The transaction log for database 'client_705' is full due to 'REPLICATION'.'. Use the action and error to determine the cause of the failure and resubmit the request.
Error 9002 is due to the log being full. It's CDC but reporting as REPLICATION that is filling the log.
There are ways to manually remove CDC but that's not going to do you any good since it will log those changes - same reason you got the error.
Grow the log a bit and disable CDC. If there is absolutely no space on the disk then try moving files off that disk. And if that still is not an option, you can try adding another log file on a different drive and then going back and removing it after everything is fixed. It needs space in the log to disable CDC.
Sue
August 10, 2017 at 2:33 am
Before you disable CDC, find out why it's enabled. It may have a legit and important business use.
Check that the jobs related to CDC are running correctly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2017 at 4:58 am
There isn't a way you can move a log file without SQL service restart . As mentioned by Gail , there must be some business reason for CDC to be working, as its not a minor decision. Service Delivery team has to be involved to know better.
Arshad
August 14, 2017 at 1:22 pm
Arsh - Sunday, August 13, 2017 4:58 AMThere isn't a way you can move a log file without SQL service restart...
Just to clarify in case someone runs across this thread, that's not true. You would need to take the database in question offline (or detach/attach), but you wouldn't have to stop/start the entire instance.
Cheers!
August 15, 2017 at 12:50 am
What's I said is in the context of a service interruption.
August 15, 2017 at 1:55 am
Arsh - Tuesday, August 15, 2017 12:50 AMWhat's I said is in the context of a service interruption.
It's still untrue. You need to stop/start the instance to move files for the system databases, but it's not needed for the user databases as they can be taken offline.
It's also irrelevant, as no one asked or suggested moving the DB's log file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply