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 fi
nd exactly what I am looking for.
I have a 4GB DB with a 1GB log file. Backups are done nightly and written t
o 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 s
hould just reuse itself. I
issued a DBCC loginfo to see that each part of the log was a status 2. I th
en tried a backup log... with truncate_only and backup log...with no_log. T
his did not reclaim any space.
I have exhausted what I THOUGHT I knew about simple recovery. If anyone els
e 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|||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...
> find exactly what I am looking for.
> to a NAS via UNC path. When I went home on Friday the log was 200MB -- th
is
> 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 o
f
> the log was a status 2. I then tried a backup log... with truncate_only a
nd
> backup log...with no_log. This did not reclaim any space.
> else has an idea, please post!
>
>|||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...[vbcol=seagreen]
> There were 10 open trans in the DB
> "Andrew J. Kelly" wrote:
>
can't[vbcol=seagreen]
written[vbcol=seagreen]
this[vbcol=seagreen]
it[vbcol=seagreen]
of[vbcol=seagreen]
and[vbcol=seagreen]
anyone[vbcol=seagreen]|||There were 10 open.
"Richard Ding" wrote:

> 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...
> find exactly what I am looking for.
> to a NAS via UNC path. When I went home on Friday the log was 200MB -- th
is
> 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 o
f
> the log was a status 2. I then tried a backup log... with truncate_only a
nd
> backup log...with no_log. This did not reclaim any space.
> else has an idea, please post!
>
>|||Yes and no...(I think)
Even if there are open transactions; shouldn't sql be able to reclaim the sp
ace that was before the LSN of those transactions? I understand that that p
ortion 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 s
tarts now and runs for 12 hours, just holding a resource open and not allowi
ng that portion of the log to be 'checkpointed'?
Would I just cancel the SPIDS submitting those transactions so that there ar
e NO transactions occuring? Also why would sql care if it is in simple reco
very 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...
> can't
> written
> this
> it
> of
> and
> anyone
>
>|||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?[vbcol=seagreen]
> Thanks for all you help.
> "Andrew J. Kelly" wrote:
>
message[vbcol=seagreen]
still[vbcol=seagreen]
200MB --[vbcol=seagreen]
so[vbcol=seagreen]
part[vbcol=seagreen]
truncate_only[vbcol=seagreen]