Optimizations job failure - SQLServer2000

Hi all,
I created a database maintenance plan with backup + transsactionlog backup+optimizations. The first two jobs work fine but the Optimizations job is failing with error:
sqlmaint.exe failed. SQLSTATE 42000, Error: 22029. The step failed.

User is admin on local box and "sysadmin" in db server role. Any suggestions to resolve this?

Thanks
Vinnie...check out this link.

http://support.microsoft.com/default.aspx?scid=kb;en-us;326485

Optimizations job failure

Anyone's help is appreciated in resolving this error:

I created a Database Maintenance plan with backup+transactionlog backup+ optimizations. The first two jobs run fine but optimizations job fails with:

sqlmaint.exe failed [SQLSTATE 42000] ( Error 22029). The step failed.

The user that is running all the jobs is "sysadmin" in database and member of administrator on local machine.

I am trying to run this when the users are using the system.

Any thoughts how I can fix this?

Thanks
VinnieIn the Job History dialog box, check the "show details" box in the upper right corner. If you know why the job failed, you'll have a much better shot at fixing it!

-PatP|||Thanks for the suggestion. The error message I wrote down is from the steps details after checking the box on right hand corner and looking through the steps information why it failed. The step 1 gave that info
sqlmaint.exe failed. SQLSTATE 42000 error 22029. The step failed.

Any other thoughts?

Thanks
Vinnie|||OK What's the second line say?|||This is the second line.

The first line was: The job Failed. The Job was invoked by User HBOCD01\VKaramc. The last step to run was step 1 (Step 1).|||...I think you've posted this subject twice. Check out the link I posted in the
other post.|||Ohhh..you mean this Link (http://support.microsoft.com/default.aspx?scid=kb;en-us;326485)

Why don't you show us the code in one step that didn't fail, and one that did...|||Thanks for the link. Though the resolutions expressed in the link text have only 2 workarounds for my problem, I guess I can work it out.

Vinnie

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

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

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 t
he
> 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, o
r
> can I tell it not to create the LDFfile?
> 2) Cani choose where the LDF file is located, I need it written to anothe
r
> 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 woul
d
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 shou
ld
> 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 th
e
>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