Optimization failure.

Sorry if this is a repost I don't think my first one went through.
I have optimization setup in my database maintenance plan. It fails
everytime. It creates a LDF file which stops when it hits 2.5gb. This
causes the error "Log File is Full". At that point noone can connect to the
DB until I run a copy and delte query to move the info elsewhere.
Two questions:
1) Can I change the size the LDF file is allowed to grow too and where, or
can I tell it not to create the LDFfile?
2) Cani choose where the LDF file is located, I need it written to another
drive where I have more room for it to grow.
Thanks in aedvance.UPDATE: Ok so when I look at the properties for one of the databases and
look at the Transaction Log tab, I see where the LDF record is coming from.
I also see where it is restricted to 2500 mb.
I would like to change the maximum file size to Unrestricted file growth.
But I need to move where the log file is saved to a different drive. When I
click to change the location I get the message "A transaction log file's
physical filename cannot be changed once the transaction log file has been
created."
How do I change where the physical location of the log file is?
Thanks,
"Kevin Porter" wrote:
> Sorry if this is a repost I don't think my first one went through.
> I have optimization setup in my database maintenance plan. It fails
> everytime. It creates a LDF file which stops when it hits 2.5gb. This
> causes the error "Log File is Full". At that point noone can connect to the
> DB until I run a copy and delte query to move the info elsewhere.
> Two questions:
> 1) Can I change the size the LDF file is allowed to grow too and where, or
> can I tell it not to create the LDFfile?
> 2) Cani choose where the LDF file is located, I need it written to another
> drive where I have more room for it to grow.
> Thanks in aedvance.
>|||Kevin,
This KB has instructions for various SQL versons and databases, so it should
have your answer.
http://support.microsoft.com/kb/224071/en-us
RLF
"Kevin Porter" <KevinPorter@.discussions.microsoft.com> wrote in message
news:54446D4E-5B4F-46EF-8BCE-A1CDF2EC3B77@.microsoft.com...
> UPDATE: Ok so when I look at the properties for one of the databases and
> look at the Transaction Log tab, I see where the LDF record is coming
> from.
> I also see where it is restricted to 2500 mb.
> I would like to change the maximum file size to Unrestricted file growth.
> But I need to move where the log file is saved to a different drive. When
> I
> click to change the location I get the message "A transaction log file's
> physical filename cannot be changed once the transaction log file has been
> created."
> How do I change where the physical location of the log file is?
> Thanks,
> "Kevin Porter" wrote:
>> Sorry if this is a repost I don't think my first one went through.
>> I have optimization setup in my database maintenance plan. It fails
>> everytime. It creates a LDF file which stops when it hits 2.5gb. This
>> causes the error "Log File is Full". At that point noone can connect to
>> the
>> DB until I run a copy and delte query to move the info elsewhere.
>> Two questions:
>> 1) Can I change the size the LDF file is allowed to grow too and where,
>> or
>> can I tell it not to create the LDFfile?
>> 2) Cani choose where the LDF file is located, I need it written to
>> another
>> drive where I have more room for it to grow.
>> Thanks in aedvance.
>>|||Thank you Russell. That looks to be exactly what I need. I figured it would
involve detaching the database, but walkthroughs are nice when you're just
learning like me.
Thanks again for the quick response.
"Russell Fields" wrote:
> Kevin,
> This KB has instructions for various SQL versons and databases, so it should
> have your answer.
> http://support.microsoft.com/kb/224071/en-us
> RLF
> "Kevin Porter" <KevinPorter@.discussions.microsoft.com> wrote in message
> news:54446D4E-5B4F-46EF-8BCE-A1CDF2EC3B77@.microsoft.com...
> > UPDATE: Ok so when I look at the properties for one of the databases and
> > look at the Transaction Log tab, I see where the LDF record is coming
> > from.
> > I also see where it is restricted to 2500 mb.
> >
> > I would like to change the maximum file size to Unrestricted file growth.
> > But I need to move where the log file is saved to a different drive. When
> > I
> > click to change the location I get the message "A transaction log file's
> > physical filename cannot be changed once the transaction log file has been
> > created."
> >
> > How do I change where the physical location of the log file is?
> >
> > Thanks,
> >
> > "Kevin Porter" wrote:
> >
> >> Sorry if this is a repost I don't think my first one went through.
> >>
> >> I have optimization setup in my database maintenance plan. It fails
> >> everytime. It creates a LDF file which stops when it hits 2.5gb. This
> >> causes the error "Log File is Full". At that point noone can connect to
> >> the
> >> DB until I run a copy and delte query to move the info elsewhere.
> >>
> >> Two questions:
> >>
> >> 1) Can I change the size the LDF file is allowed to grow too and where,
> >> or
> >> can I tell it not to create the LDFfile?
> >>
> >> 2) Cani choose where the LDF file is located, I need it written to
> >> another
> >> drive where I have more room for it to grow.
> >>
> >> Thanks in aedvance.
> >>
> >>
>
>|||On Wed, 18 Jul 2007 09:40:02 -0700, Kevin Porter wrote:
>Sorry if this is a repost I don't think my first one went through.
>I have optimization setup in my database maintenance plan. It fails
>everytime. It creates a LDF file which stops when it hits 2.5gb. This
>causes the error "Log File is Full". At that point noone can connect to the
>DB until I run a copy and delte query to move the info elsewhere.
>Two questions:
>1) Can I change the size the LDF file is allowed to grow too and where, or
>can I tell it not to create the LDFfile?
>2) Cani choose where the LDF file is located, I need it written to another
>drive where I have more room for it to grow.
>Thanks in aedvance.
>
Hi Kevin,
Your questions are already answered, but you might still need to adress
the reason of your ever-growing log file.
Do you know that a database with recovery model full (the default) or
bulk-logged will continue to grow the log file until you take a bacup of
the log file? If you need full (or bulk-logged) recovery, then you
should schedule regular log backups. If you don't need the ability to
restore beyond the last full database backup, you should change the
recovery model to simple.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis