December 13, 2015 at 2:26 am
The Transaction Log Backup runs for 2 seconds and it does not Backup and I Can't Shrink the Log File.
The file has grown to 97
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 13, 2015 at 2:33 am
How do I get out of this mess?
BACKUP LOG [PrismData] TO DISK = N'E:\Backup\PrismData_backup_2015_12_13_042945_6506526.trn' WITH NOFORMAT, NOINIT, NAME = N'PrismData_backup_2015_12_13_042945_6506526', SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10
Processed 23 pages for database 'PrismData', file 'PrismData_Log' on file 1.
The log was not truncated because records at the beginning of the log are pending replication or Change Data Capture. Ensure the Log Reader Agent or capture job is running or use sp_repldone to mark transactions as distributed or captured.
BACKUP LOG successfully processed 23 pages in 0.147 seconds (1.179 MB/sec).
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 13, 2015 at 2:39 am
Welsh Corgi (12/13/2015)
How do I get out of this mess?BACKUP LOG [PrismData] TO DISK = N'E:\Backup\PrismData_backup_2015_12_13_042945_6506526.trn' WITH NOFORMAT, NOINIT, NAME = N'PrismData_backup_2015_12_13_042945_6506526', SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10
Processed 23 pages for database 'PrismData', file 'PrismData_Log' on file 1.
The log was not truncated because records at the beginning of the log are pending replication or Change Data Capture. Ensure the Log Reader Agent or capture job is running or use sp_repldone to mark transactions as distributed or captured.
BACKUP LOG successfully processed 23 pages in 0.147 seconds (1.179 MB/sec).
You have the cause clearly stated in the message, check you're replication.
😎
December 13, 2015 at 2:47 am
Welsh Corgi (12/13/2015)
The log was not truncated because records at the beginning of the log are pending replication or Change Data Capture. Ensure the Log Reader Agent or capture job is running or use sp_repldone to mark transactions as distributed or captured.
The error message tells why the log won't shrink and what you should do to fix it.
You've either got transactional replication with a log reader job that's not running or change data capture with a capture job that's not running.
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
December 13, 2015 at 2:54 am
GilaMonster (12/13/2015)
Welsh Corgi (12/13/2015)
The log was not truncated because records at the beginning of the log are pending replication or Change Data Capture. Ensure the Log Reader Agent or capture job is running or use sp_repldone to mark transactions as distributed or captured.The error message tells why the log won't shrink and what you should do to fix it.
You've either got transactional replication with a log reader job that's not running or change data capture with a capture job that's not running.
Log reader Agent error:
The last step did not log any message!
I do not know how to fix or come up with a work around.
The error message is not intuitive.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 13, 2015 at 3:01 am
I just told you! And the error message that you posted told you as well!
You've either got transactional replication with a log reader job that's not running or change data capture with a capture job that's not running.
Ensure the Log Reader Agent or capture job is running or use sp_repldone to mark transactions as distributed or captured.
You don't need to think about how to come up with a work around, the error message that you posted told you exactly what to do.
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
December 13, 2015 at 3:13 am
GilaMonster (12/13/2015)
I just told you! And the error message that you posted told you as well!You've either got transactional replication with a log reader job that's not running or change data capture with a capture job that's not running.
Ensure the Log Reader Agent or capture job is running or use sp_repldone to mark transactions as distributed or captured.
You don't need to think about how to come up with a work around, the error message that you posted told you exactly what to do.
So what do I do?
When I start the Log Reader Agent it fails.
How do I mark transactions as distributed or captured?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 13, 2015 at 6:19 am
Welsh Corgi (12/13/2015)
When I start the Log Reader Agent it fails.
And you've, of course, looked at the errors it logs, googled them if you don't understand immediately and hence know why it's failing?
If not, that might be the place to start. I know it's a novel suggestion, but it beats waiting for something to magically work.
How do I mark transactions as distributed or captured?
You shouldn't, unless you want to reinitialise the replication.
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
December 13, 2015 at 3:46 pm
You have the error message that you posted. Did you actually read it? When done, think about it and determine which scenario applies to you.
December 13, 2015 at 4:29 pm
Ed Wagner (12/13/2015)
You have the error message that you posted. Did you actually read it? When done, think about it and determine which scenario applies to you.
I determined that the Log Reader Agent is failing. So I cannot back up the log.
I do not know how to fix the problem for the error message is not clear.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 13, 2015 at 10:02 pm
try this
SELECT name, log_reuse_wait_desc FROM sys.databases
it told you the cause for no emptying the log was REPLICATION
then execute
sp_removedbreplication youdbname
to fix
December 14, 2015 at 3:36 am
Ok, the log reader is failing. Why? What steps have you taken to debug the log reader?
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
December 14, 2015 at 4:54 am
Does anyone have a guide as to what steps should be taken to debug a Log Reader failure?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 14, 2015 at 7:06 am
Welsh Corgi (12/14/2015)
Does anyone have a guide as to what steps should be taken to debug a Log Reader failure?
Start with the logs. Why is the Log Reader failing? Without that information, we can't help you any more than you can help yourself.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply