db mirroring: why tr. log continue growing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gene. - 30 Jul 2007 21:24 GMT

hi All
I test db. mirroring.
While it's working, tr. log continues growing. while I run tr. log dumps
regulary, the size of it only goes up.
I tried dbcc shrinkfile and shrinkdatabase - it won't help.

Does anybody have this experience?

Reply to this Message

Tom Moreau - 30 Jul 2007 23:16 GMT

Run DBCC OPENTRAN. You may have a long-running transaction.

Signature

Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

Reply to this Message

Andrew J. Kelly - 30 Jul 2007 23:19 GMT

Sounds like you have an open transaction somewhere. What does DBCC
OPENTRAN() say?

Signature

Andrew J. Kelly SQL MVP

> hi All
> I test db. mirroring.
[quoted text clipped - 3 lines]
>
> Does anybody have this experience?

Reply to this Message

Gene. - 31 Jul 2007 01:18 GMT

Guys, thank you first of all for responds.
This is the test db. Nothing is running there outside of my control.
The way I set it up is next:
backup test log... statements runs every second and I see successfull
execution of it.
Then, I run 1000 records inserts just to see how they show up on another end.
Problem is very obvious.

Interesting enough I tried to shrink file - noway. Once I stop mirrored
session and disconnect db's, shrink works immediately.

> Sounds like you have an open transaction somewhere. What does DBCC
> OPENTRAN() say?
[quoted text clipped - 6 lines]
> >
> > Does anybody have this experience?

Reply to this Message

Andrew J. Kelly - 31 Jul 2007 01:38 GMT

Just because it is under your control does not mean there isn't an open
transaction. What type of mirroring are you using? Is the tran getting
committed on the mirror?

Signature

Andrew J. Kelly SQL MVP

> Guys, thank you first of all for responds.
> This is the test db. Nothing is running there outside of my control.
[quoted text clipped - 19 lines]
>> >
>> > Does anybody have this experience?

Reply to this Message

Gene. - 31 Jul 2007 14:44 GMT

Hi Andrew

I ran dbcc opentran, does not show any open transactions.
Batch inserts which I run is not inside of begin tran, commit statements.
Do you have any database currently set up with database mirroring?
When I run sp_who, I see 3 db mirror processes:

21    0    suspended     sa

     0      NULL    DB MIRROR     0
21    1    suspended     sa

     0      NULL    DB MIRROR     0

25    0    suspended     sa

     0      test    DB MIRROR     0

spid25 is the one wich I recognize in charge for current mirror session.
Few days ago when I had similar setup and paused mirror session, I could not
get it back running. So I had to drop databases, endpoints. It seems like
this is left overs which I don't know how to get rid of it.
I am not sure how relevant spid 21 to the problem of growing tr. log.
When I select * from sys.endpoints, I see only 1 database mirroring session
running.
Any clue?

Log is growing

> Just because it is under your control does not mean there isn't an open
> transaction. What type of mirroring are you using? Is the tran getting
[quoted text clipped - 23 lines]
> >> >
> >> > Does anybody have this experience?

Reply to this Message

Gene. - 31 Jul 2007 14:48 GMT

Andrew, thank you so much for asking 'what type of mirroring' I use.
My intention was to use high performance mode.

When I set it up, i forget to run last statement:
ALTER DATABASE test SET PARTNER SAFETY OFF

Once I ran it, I was able to shrinkdb.
Thank you for help, Gene.

Reply to this Message