Need advice about setting file limit, file grow

Seeking advice here.
We have a database that consists of about 440 mb (mdf) and the logs of the
database amount to almost 14 GB (ldf).
We schedule to run the transaction log every night (I used to GUI when we
set up).
During the last year, we have seen the logs spike up to 42 GB twice (one in
Feb and recently Nov). Running the transaction backup doesn't seem to reduce
the size. The only time we could get it to reduce size is running the
command below, but the problem is we have to shutdown both IIS servers that
connect to this database (it won't do any good if the IIS is still pointing
to it).
backup log bbnc to disk='E:\bbnc_log3.log'
dbcc shrinkfile (Portal_log, 2)
We are considering setting a limit for the file size. Right now it is set
to "Automatic grow file", by 10 percent. Maximum file growth to
"Unrestricted file growth". What are the cons of setting a limit for the
size?
What is the best setting for us?
Thanks in advance.
TntYou need to contain your run-away transactions. If you run a very large
transaction that uses up a lot of transaction log space, your tran log will
grow large no matter how often you back up the tran log.
Linchi
"tnt" wrote:
> Seeking advice here.
> We have a database that consists of about 440 mb (mdf) and the logs of the
> database amount to almost 14 GB (ldf).
>
> We schedule to run the transaction log every night (I used to GUI when we
> set up).
>
> During the last year, we have seen the logs spike up to 42 GB twice (one in
> Feb and recently Nov). Running the transaction backup doesn't seem to reduce
> the size. The only time we could get it to reduce size is running the
> command below, but the problem is we have to shutdown both IIS servers that
> connect to this database (it won't do any good if the IIS is still pointing
> to it).
>
> backup log bbnc to disk='E:\bbnc_log3.log'
> dbcc shrinkfile (Portal_log, 2)
> We are considering setting a limit for the file size. Right now it is set
> to "Automatic grow file", by 10 percent. Maximum file growth to
> "Unrestricted file growth". What are the cons of setting a limit for the
> size?
> What is the best setting for us?
>
> Thanks in advance.
> Tnt|||Can you give me links to how to deal with "run-away" transactions?
Thanks,
Tnt
"Linchi Shea" wrote:
> You need to contain your run-away transactions. If you run a very large
> transaction that uses up a lot of transaction log space, your tran log will
> grow large no matter how often you back up the tran log.
> Linchi
> "tnt" wrote:
> > Seeking advice here.
> >
> > We have a database that consists of about 440 mb (mdf) and the logs of the
> > database amount to almost 14 GB (ldf).
> >
> >
> > We schedule to run the transaction log every night (I used to GUI when we
> > set up).
> >
> >
> > During the last year, we have seen the logs spike up to 42 GB twice (one in
> > Feb and recently Nov). Running the transaction backup doesn't seem to reduce
> > the size. The only time we could get it to reduce size is running the
> > command below, but the problem is we have to shutdown both IIS servers that
> > connect to this database (it won't do any good if the IIS is still pointing
> > to it).
> >
> >
> > backup log bbnc to disk='E:\bbnc_log3.log'
> >
> > dbcc shrinkfile (Portal_log, 2)
> >
> > We are considering setting a limit for the file size. Right now it is set
> > to "Automatic grow file", by 10 percent. Maximum file growth to
> > "Unrestricted file growth". What are the cons of setting a limit for the
> > size?
> >
> > What is the best setting for us?
> >
> >
> > Thanks in advance.
> >
> > Tnt|||I have caught a number of 'infinite-looping' bugs in my day. The most
common situation was actually VB6's ON ERROR RESUME NEXT.
You can use profiler to watch the executions on the server (sql batch
completed and rpc completed events) and probably visually find repeated
calls. You can also set up profiler to save to disk and let it run
unattended. Be careful with this tho because if there is looping you could
write a HUGE profiler file also!
On a separate tack, are you doing an explicit transaction log backup' If
not, then it could just be that reason that the tlog is 14GB. Doing full
backups doesn't get rid of tlog entries and it will keep growing. If you
can't afford the space (or don't need the data in the log) you can do backup
log mydbname with truncate_only.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"tnt" <tnt@.discussions.microsoft.com> wrote in message
news:D05F5380-BE0C-4EDA-B81E-6671F8189DCE@.microsoft.com...
> Can you give me links to how to deal with "run-away" transactions?
> Thanks,
> Tnt
> "Linchi Shea" wrote:
>> You need to contain your run-away transactions. If you run a very large
>> transaction that uses up a lot of transaction log space, your tran log
>> will
>> grow large no matter how often you back up the tran log.
>> Linchi
>> "tnt" wrote:
>> > Seeking advice here.
>> >
>> > We have a database that consists of about 440 mb (mdf) and the logs of
>> > the
>> > database amount to almost 14 GB (ldf).
>> >
>> >
>> > We schedule to run the transaction log every night (I used to GUI when
>> > we
>> > set up).
>> >
>> >
>> > During the last year, we have seen the logs spike up to 42 GB twice
>> > (one in
>> > Feb and recently Nov). Running the transaction backup doesn't seem to
>> > reduce
>> > the size. The only time we could get it to reduce size is running the
>> > command below, but the problem is we have to shutdown both IIS servers
>> > that
>> > connect to this database (it won't do any good if the IIS is still
>> > pointing
>> > to it).
>> >
>> >
>> > backup log bbnc to disk='E:\bbnc_log3.log'
>> >
>> > dbcc shrinkfile (Portal_log, 2)
>> >
>> > We are considering setting a limit for the file size. Right now it is
>> > set
>> > to "Automatic grow file", by 10 percent. Maximum file growth to
>> > "Unrestricted file growth". What are the cons of setting a limit for
>> > the
>> > size?
>> >
>> > What is the best setting for us?
>> >
>> >
>> > Thanks in advance.
>> >
>> > Tnt