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...[vbcol=seagreen]
> 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:
|||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...
>
>
|||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