Problem with large transactionfile.

Hello
We have a problem with a large transactionfile.
How can I manage this?
I have tried this command:
dbcc shrinkfile (2, truncateonly)
but I get this errormessage:
Cannot shrink log file 2 (UX2LOG1) because all logical log
files are in use.
Can anyone help.
Thanks
Best regards
Carsten Holm.R5
Carsten
Have you tried to BACKUP LOG ? (I assume you don't have database set simple
recovery mode)
Also ,DBCC LOGINFO(dbid) command will show what transactions are running?
(status=2 at the end of the list)
"Carsten Holm" <cah@.r5.com> wrote in message
news:1958b01c44d53$dccf1640$a601280a@.phx.gbl...
> Hello
> We have a problem with a large transactionfile.
> How can I manage this?
> I have tried this command:
> dbcc shrinkfile (2, truncateonly)
> but I get this errormessage:
> Cannot shrink log file 2 (UX2LOG1) because all logical log
> files are in use.
> Can anyone help.
> Thanks
> Best regards
> Carsten Holm.R5
|||Carsten
Have a look at these links. They should help you.
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default...b;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default...b;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Regards
John
|||Hi
Thanks for your answer
I have runned the DBCC LOGINFO(dbid) and it returned many
rows, and all is with status 2, exept this:
22539529903669248000
2366000000052200021
It is in the middel of the rows.
We have tried a full backup, but the transactionlog was
not deleted.
Thanks.
Best regards
Carsten Holm
>--Original Message--
>Carsten
>Have you tried to BACKUP LOG ? (I assume you don't have
database set simple
>recovery mode)
>Also ,DBCC LOGINFO(dbid) command will show what
transactions are running?[vbcol=seagreen]
>(status=2 at the end of the list)
>
>"Carsten Holm" <cah@.r5.com> wrote in message
>news:1958b01c44d53$dccf1640$a601280a@.phx.gbl...
log
>
>.
>
|||Hi
Full backup of the database does not truncate log file. I suggest you to
backup log file and then to shrink it or to remove.
If you see transactions with status=2 at the end of the list you will need
to perfom dummy INSERTION to move them at the top.
<anonymous@.discussions.microsoft.com> wrote in message
news:19cca01c44d56$53146bf0$a001280a@.phx.gbl...[vbcol=seagreen]
> Hi
> Thanks for your answer
> I have runned the DBCC LOGINFO(dbid) and it returned many
> rows, and all is with status 2, exept this:
> 2 253952 9903669248 0 0 0
> 2366000000052200021
> It is in the middel of the rows.
> We have tried a full backup, but the transactionlog was
> not deleted.
> Thanks.
> Best regards
> Carsten Holm
> database set simple
> transactions are running?
> log
sql

Problem with large transactionfile.

Hello
We have a problem with a large transactionfile.
How can I manage this?
I have tried this command:
dbcc shrinkfile (2, truncateonly)
but I get this errormessage:
Cannot shrink log file 2 (UX2LOG1) because all logical log
files are in use.
Can anyone help.
Thanks
Best regards
Carsten Holm.R5Carsten
Have you tried to BACKUP LOG ? (I assume you don't have database set simple
recovery mode)
Also ,DBCC LOGINFO(dbid) command will show what transactions are running?
(status=2 at the end of the list)
"Carsten Holm" <cah@.r5.com> wrote in message
news:1958b01c44d53$dccf1640$a601280a@.phx
.gbl...
> Hello
> We have a problem with a large transactionfile.
> How can I manage this?
> I have tried this command:
> dbcc shrinkfile (2, truncateonly)
> but I get this errormessage:
> Cannot shrink log file 2 (UX2LOG1) because all logical log
> files are in use.
> Can anyone help.
> Thanks
> Best regards
> Carsten Holm.R5|||Carsten
Have a look at these links. They should help you.
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Regards
John|||Hi
Thanks for your answer
I have runned the DBCC LOGINFO(dbid) and it returned many
rows, and all is with status 2, exept this:
2 253952 9903669248 0 0 0
2366000000052200021
It is in the middel of the rows.
We have tried a full backup, but the transactionlog was
not deleted.
Thanks.
Best regards
Carsten Holm
>--Original Message--
>Carsten
>Have you tried to BACKUP LOG ? (I assume you don't have
database set simple
>recovery mode)
>Also ,DBCC LOGINFO(dbid) command will show what
transactions are running?
>(status=2 at the end of the list)
>
>"Carsten Holm" <cah@.r5.com> wrote in message
> news:1958b01c44d53$dccf1640$a601280a@.phx
.gbl...
log[vbcol=seagreen]
>
>.
>|||Hi
Full backup of the database does not truncate log file. I suggest you to
backup log file and then to shrink it or to remove.
If you see transactions with status=2 at the end of the list you will need
to perfom dummy INSERTION to move them at the top.
<anonymous@.discussions.microsoft.com> wrote in message
news:19cca01c44d56$53146bf0$a001280a@.phx
.gbl...[vbcol=seagreen]
> Hi
> Thanks for your answer
> I have runned the DBCC LOGINFO(dbid) and it returned many
> rows, and all is with status 2, exept this:
> 2 253952 9903669248 0 0 0
> 2366000000052200021
> It is in the middel of the rows.
> We have tried a full backup, but the transactionlog was
> not deleted.
> Thanks.
> Best regards
> Carsten Holm
> database set simple
> transactions are running?
> log

Problem with distribution agent

Can anyone help me on this?

I have a transactional replication going on from Server A to server B (Sql Server 2005)

The replication was working well till yesterday..

But now, the replicated transactions are not getting applied at the subscriber. When I see the Log reader agent, its working fine...delivering the transactions to the distributor. When I see the replication monitor status ..it says 'delivering replicated transactions'...however the number of undistriuted transactions is very high..its getting bigger and bigger..not decreasing...I dont see any errors..So my guess is the distribution agent is not working eventhough its says 'delivering replicated transactions'..Other thing is if i see the replication monitor it says status excellent...and latency as 00 ..but i dont see the latest data at the subscriber..the subscriber is way behind the publisher..

can anyone help me on this? how do i resolve this problem? I stopped the distribution agent and again started it..but it dint help...what should i do?

thanks

Are any changes being applied at all? Or did you just complete some very large batch process, like an update of 1 million records inside a single transaction?|||

Thanks for ur reply..

I dont think the changes are being applied...in last 20 -24 hrs..the data at the subscriber is same...i dont see new records at the subscriber in last 20-24 hrs..undistributed transactions count keep on increasing..(now its 22074840 ...time needed to apply these transactions keeps on increasing...now its (18 hrs )..

I am not sure whether we updated large records in a single transaction...but even if we had updated...subscriber should catch up with the publisher in 20-24 hrs ..Does it take so long to apply those changes at the subscriber?

what should i do to resolve this issue? how do i force those undistributed transactions to be applied at the subscriber?

log reader is working fine...replication status is excellent.latency is 00...only thing is the replicated transactions are not getting applied at the subscriber..the distribution database is at the subscriber (i.e. subscriber is also distibutor)...

Could you plzz help me to solve this problem

Thanks

|||

Hard to say what the problem is, have you done any investigation at hte subsriber, is anything blocking the distribution agent? You can also stop the distribution agent and run it from the command line with an increased OutputVerboseLevel, it might give you some more information.

Otherwise I'm not really sure what the issue is without more information. You can try profiling the subscriber to see if the distribution agent is even connected.

|||

Hi, vjai!

I have almost the same problem. Did you look at MSdistribution_history on distributor?

select * from MSdistribution_history
order by [time] desc

|||

I am having the same problem. The only thing that fixed it was re-starting the SQL Service and then re-starting the distribution agent. This is not a solution that we can do during business hours though. Does any body have any other suggestions on how to resolve this?

thanks,

Amir

|||I agree it's not a good workaround, however without identifying the problem it's hard for us to offer a proper solution.|||

From my experience, this kind of problem occurs whenever we do massive updates in a single batch and also if there are triggers on the subscriber. In my case, i had a update trigger at the subscriber and whenever i do large number of updates on that table (on publisher) in a single batch , I get this problem.

|||OK, so it's not a distribution agent problem then? Just like at the publisher, the transaction isn't committed until the end, so same goes at the subscriber. If the trigger is causing performance issues - it's probably firing once for every update - your transaction size is probably growing very large. You can work around this by committing smaller transactions at the publisher, or investigate the subscriber to see if there's something in the trigger that can be optimized, or maybe it's a hardware issue.

Problem with distribution agent

Can anyone help me on this?

I have a transactional replication going on from Server A to server B (Sql Server 2005)

The replication was working well till yesterday..

But now, the replicated transactions are not getting applied at the subscriber. When I see the Log reader agent, its working fine...delivering the transactions to the distributor. When I see the replication monitor status ..it says 'delivering replicated transactions'...however the number of undistriuted transactions is very high..its getting bigger and bigger..not decreasing...I dont see any errors..So my guess is the distribution agent is not working eventhough its says 'delivering replicated transactions'..Other thing is if i see the replication monitor it says status excellent...and latency as 00 ..but i dont see the latest data at the subscriber..the subscriber is way behind the publisher..

can anyone help me on this? how do i resolve this problem? I stopped the distribution agent and again started it..but it dint help...what should i do?

thanks

Are any changes being applied at all? Or did you just complete some very large batch process, like an update of 1 million records inside a single transaction?|||

Thanks for ur reply..

I dont think the changes are being applied...in last 20 -24 hrs..the data at the subscriber is same...i dont see new records at the subscriber in last 20-24 hrs..undistributed transactions count keep on increasing..(now its 22074840 ...time needed to apply these transactions keeps on increasing...now its (18 hrs )..

I am not sure whether we updated large records in a single transaction...but even if we had updated...subscriber should catch up with the publisher in 20-24 hrs ..Does it take so long to apply those changes at the subscriber?

what should i do to resolve this issue? how do i force those undistributed transactions to be applied at the subscriber?

log reader is working fine...replication status is excellent.latency is 00...only thing is the replicated transactions are not getting applied at the subscriber..the distribution database is at the subscriber (i.e. subscriber is also distibutor)...

Could you plzz help me to solve this problem

Thanks

|||

Hard to say what the problem is, have you done any investigation at hte subsriber, is anything blocking the distribution agent? You can also stop the distribution agent and run it from the command line with an increased OutputVerboseLevel, it might give you some more information.

Otherwise I'm not really sure what the issue is without more information. You can try profiling the subscriber to see if the distribution agent is even connected.

|||

Hi, vjai!

I have almost the same problem. Did you look at MSdistribution_history on distributor?

select * from MSdistribution_history
order by [time] desc

|||

I am having the same problem. The only thing that fixed it was re-starting the SQL Service and then re-starting the distribution agent. This is not a solution that we can do during business hours though. Does any body have any other suggestions on how to resolve this?

thanks,

Amir

|||I agree it's not a good workaround, however without identifying the problem it's hard for us to offer a proper solution.|||

From my experience, this kind of problem occurs whenever we do massive updates in a single batch and also if there are triggers on the subscriber. In my case, i had a update trigger at the subscriber and whenever i do large number of updates on that table (on publisher) in a single batch , I get this problem.

|||OK, so it's not a distribution agent problem then? Just like at the publisher, the transaction isn't committed until the end, so same goes at the subscriber. If the trigger is causing performance issues - it's probably firing once for every update - your transaction size is probably growing very large. You can work around this by committing smaller transactions at the publisher, or investigate the subscriber to see if there's something in the trigger that can be optimized, or maybe it's a hardware issue.