Transaction Log Backup runs for 2 seconds does not Backup - Can't Shrink Log File

Question

Transaction Log Backup runs for 2 seconds does not Backup - Can't Shrink Log File

Welsh Corgi

SSC Guru

Points: 116520
More actions
December 13, 2015 at 2:26 am

#310115

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/

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

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/

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

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.

😎

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

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/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

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/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ed Wagner SSC Guru Points: 287026 More actions · Answer 8

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

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/

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 10

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

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/

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 13

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning