Log file won't Shrink in Simple Recovery Mode

Ok, I know there are a TON of threads out there on this but I still can't find exactly what I am looking for.
I have a 4GB DB with a 1GB log file. Backups are done nightly and written to a NAS via UNC path. When I went home on Friday the log was 200MB -- this morning it was 1GB. As I stated the recovery model is set to simple so it should just reuse itself. I issued a DBCC loginfo to see that each part of the log was a status 2. I then tried a backup log... with truncate_only and backup log...with no_log. This did not reclaim any space.
I have exhausted what I THOUGHT I knew about simple recovery. If anyone else has an idea, please post!
It has always been my understanding thatIf there are a lot of VLF's with a status of 2 that means you have
uncommitted transactions in them. What does DBCC OPENTRAN() say?
--
Andrew J. Kelly SQL MVP
"Dave Harper" <Dave Harper@.discussions.microsoft.com> wrote in message
news:5F3AE072-8C13-444B-9C0B-E21BA3C7F717@.microsoft.com...
> Ok, I know there are a TON of threads out there on this but I still can't
find exactly what I am looking for.
> I have a 4GB DB with a 1GB log file. Backups are done nightly and written
to a NAS via UNC path. When I went home on Friday the log was 200MB -- this
morning it was 1GB. As I stated the recovery model is set to simple so it
should just reuse itself. I issued a DBCC loginfo to see that each part of
the log was a status 2. I then tried a backup log... with truncate_only and
backup log...with no_log. This did not reclaim any space.
> I have exhausted what I THOUGHT I knew about simple recovery. If anyone
else has an idea, please post!
> It has always been my understanding that|||Is there any open transaction? DBCC OPENTRAN.
Was there any replication properties set on for this database?
"Dave Harper" <Dave Harper@.discussions.microsoft.com> wrote in message
news:5F3AE072-8C13-444B-9C0B-E21BA3C7F717@.microsoft.com...
> Ok, I know there are a TON of threads out there on this but I still can't
find exactly what I am looking for.
> I have a 4GB DB with a 1GB log file. Backups are done nightly and written
to a NAS via UNC path. When I went home on Friday the log was 200MB -- this
morning it was 1GB. As I stated the recovery model is set to simple so it
should just reuse itself. I issued a DBCC loginfo to see that each part of
the log was a status 2. I then tried a backup log... with truncate_only and
backup log...with no_log. This did not reclaim any space.
> I have exhausted what I THOUGHT I knew about simple recovery. If anyone
else has an idea, please post!
> It has always been my understanding that|||Well that will sure do it<g>.
--
Andrew J. Kelly SQL MVP
"Dave Harper" <DaveHarper@.discussions.microsoft.com> wrote in message
news:9DA36206-3F23-4464-AD81-EABEF886FB82@.microsoft.com...
> There were 10 open trans in the DB
> "Andrew J. Kelly" wrote:
> > If there are a lot of VLF's with a status of 2 that means you have
> > uncommitted transactions in them. What does DBCC OPENTRAN() say?
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Dave Harper" <Dave Harper@.discussions.microsoft.com> wrote in message
> > news:5F3AE072-8C13-444B-9C0B-E21BA3C7F717@.microsoft.com...
> > > Ok, I know there are a TON of threads out there on this but I still
can't
> > find exactly what I am looking for.
> > >
> > > I have a 4GB DB with a 1GB log file. Backups are done nightly and
written
> > to a NAS via UNC path. When I went home on Friday the log was 200MB --
this
> > morning it was 1GB. As I stated the recovery model is set to simple so
it
> > should just reuse itself. I issued a DBCC loginfo to see that each part
of
> > the log was a status 2. I then tried a backup log... with truncate_only
and
> > backup log...with no_log. This did not reclaim any space.
> > >
> > > I have exhausted what I THOUGHT I knew about simple recovery. If
anyone
> > else has an idea, please post!
> > >
> > > It has always been my understanding that
> >
> >
> >|||Dave,
A log file works in a round robin format. It starts adding transactions
sequentially from the beginning to the end. Once it gets to the end it will
wrap around only if the first VLF is free and has no uncommitted
transactions. Otherwise it will grow the log and continue on. If you have
an open tran for 12 hours you have some serious issues with your app. A
transaction should ideally be open for seconds at the very most. You
absolutely need to find out who is keeping these trans open and why.
Whether you kill them or not is up to you but you have to either commit them
or roll them back.
--
Andrew J. Kelly SQL MVP
"Dave Harper" <DaveHarper@.discussions.microsoft.com> wrote in message
news:DD934F0C-13B9-4E67-876E-DB027E285BAF@.microsoft.com...
> Yes and no...(I think)
> Even if there are open transactions; shouldn't sql be able to reclaim the
space that was before the LSN of those transactions? I understand that that
portion of the log can't be reclaimed, but shouldn't the older?
> The only other thing I can think of is would have I a transaction that say
starts now and runs for 12 hours, just holding a resource open and not
allowing that portion of the log to be 'checkpointed'?
> Would I just cancel the SPIDS submitting those transactions so that there
are NO transactions occuring? Also why would sql care if it is in simple
recovery mode?
> Thanks for all you help.
> "Andrew J. Kelly" wrote:
> > Well that will sure do it<g>.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Dave Harper" <DaveHarper@.discussions.microsoft.com> wrote in message
> > news:9DA36206-3F23-4464-AD81-EABEF886FB82@.microsoft.com...
> > > There were 10 open trans in the DB
> > >
> > > "Andrew J. Kelly" wrote:
> > >
> > > > If there are a lot of VLF's with a status of 2 that means you have
> > > > uncommitted transactions in them. What does DBCC OPENTRAN() say?
> > > >
> > > > --
> > > > Andrew J. Kelly SQL MVP
> > > >
> > > >
> > > > "Dave Harper" <Dave Harper@.discussions.microsoft.com> wrote in
message
> > > > news:5F3AE072-8C13-444B-9C0B-E21BA3C7F717@.microsoft.com...
> > > > > Ok, I know there are a TON of threads out there on this but I
still
> > can't
> > > > find exactly what I am looking for.
> > > > >
> > > > > I have a 4GB DB with a 1GB log file. Backups are done nightly and
> > written
> > > > to a NAS via UNC path. When I went home on Friday the log was
200MB --
> > this
> > > > morning it was 1GB. As I stated the recovery model is set to simple
so
> > it
> > > > should just reuse itself. I issued a DBCC loginfo to see that each
part
> > of
> > > > the log was a status 2. I then tried a backup log... with
truncate_only
> > and
> > > > backup log...with no_log. This did not reclaim any space.
> > > > >
> > > > > I have exhausted what I THOUGHT I knew about simple recovery. If
> > anyone
> > > > else has an idea, please post!
> > > > >
> > > > > It has always been my understanding that
> > > >
> > > >
> > > >
> >
> >
> >