db mirroring: why tr. log continue growing

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?Run DBCC OPENTRAN. You may have a long-running transaction.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:4B6FD7B3-D4E6-47A1-8A9D-1634AAF7076D@.microsoft.com...
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?|||Sounds like you have an open transaction somewhere. What does DBCC
OPENTRAN() say?
--
Andrew J. Kelly SQL MVP
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:4B6FD7B3-D4E6-47A1-8A9D-1634AAF7076D@.microsoft.com...
> 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?|||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.
"Andrew J. Kelly" wrote:
> Sounds like you have an open transaction somewhere. What does DBCC
> OPENTRAN() say?
> --
> Andrew J. Kelly SQL MVP
> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> news:4B6FD7B3-D4E6-47A1-8A9D-1634AAF7076D@.microsoft.com...
> > 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?
>
>|||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?
--
Andrew J. Kelly SQL MVP
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:E2CB1FF0-4EE7-4FD6-854D-C7A4E9A8D60D@.microsoft.com...
> 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.
> "Andrew J. Kelly" wrote:
>> Sounds like you have an open transaction somewhere. What does DBCC
>> OPENTRAN() say?
>> --
>> Andrew J. Kelly SQL MVP
>> "Gene." <Gene@.discussions.microsoft.com> wrote in message
>> news:4B6FD7B3-D4E6-47A1-8A9D-1634AAF7076D@.microsoft.com...
>> > 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?
>>|||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
"Andrew J. Kelly" wrote:
> 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?
> --
> Andrew J. Kelly SQL MVP
> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> news:E2CB1FF0-4EE7-4FD6-854D-C7A4E9A8D60D@.microsoft.com...
> > 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.
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Sounds like you have an open transaction somewhere. What does DBCC
> >> OPENTRAN() say?
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> >> news:4B6FD7B3-D4E6-47A1-8A9D-1634AAF7076D@.microsoft.com...
> >> > 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?
> >>
> >>
> >>
>
>|||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.
"Andrew J. Kelly" wrote:
> 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?
> --
> Andrew J. Kelly SQL MVP
> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> news:E2CB1FF0-4EE7-4FD6-854D-C7A4E9A8D60D@.microsoft.com...
> > 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.
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Sounds like you have an open transaction somewhere. What does DBCC
> >> OPENTRAN() say?
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> >> news:4B6FD7B3-D4E6-47A1-8A9D-1634AAF7076D@.microsoft.com...
> >> > 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?
> >>
> >>
> >>
>
>