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 & Integrity Check Jobs failing?

Thanks in advance for any info.
What would cause Optimization & Integrity Check jobs to fail? The agent info
shows SQL State = 42000, which, according to BOL is "Syntax error,
permission violation, or other nonspecific error. " Hmm... where to go from
here?
Thanks,
MorganI think I have found out what it is...
After further investigation, the server is running SQL Server personal
edition and was exceeding the 5 user limit, which I am guessing is causing
the jobs to not run and reporting back a generic error message.
"Morgan" <abcd@.senditon.com> wrote in message
news:ercGkiL7DHA.1428@.TK2MSFTNGP12.phx.gbl...
> Thanks in advance for any info.
> What would cause Optimization & Integrity Check jobs to fail? The agent
info
> shows SQL State = 42000, which, according to BOL is "Syntax error,
> permission violation, or other nonspecific error. " Hmm... where to go
from
> here?
>
> Thanks,
> Morgan
>
>|||The "5 user limit" shouldn't cause this. That is only a throttling mechanism
(very misunderstood, though). Are you using the maint wizard? If so, specify
a report file in the maint plan and check for the real error message in that
report file.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Morgan" <abcd@.senditon.com> wrote in message
news:OPPPEXM7DHA.2300@.TK2MSFTNGP10.phx.gbl...
> I think I have found out what it is...
> After further investigation, the server is running SQL Server personal
> edition and was exceeding the 5 user limit, which I am guessing is causing
> the jobs to not run and reporting back a generic error message.
> "Morgan" <abcd@.senditon.com> wrote in message
> news:ercGkiL7DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > Thanks in advance for any info.
> >
> > What would cause Optimization & Integrity Check jobs to fail? The agent
> info
> > shows SQL State = 42000, which, according to BOL is "Syntax error,
> > permission violation, or other nonspecific error. " Hmm... where to go
> from
> > here?
> >
> >
> > Thanks,
> >
> > Morgan
> >
> >
> >
>|||Thanks, Tibor. Unfortunately we had to burn the SQL Server install and
install EE on the box. If it starts happening again, we'll hit the logs for
more detail.
Thanks again.
Morgan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OfAd4CO7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> The "5 user limit" shouldn't cause this. That is only a throttling
mechanism
> (very misunderstood, though). Are you using the maint wizard? If so,
specify
> a report file in the maint plan and check for the real error message in
that
> report file.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Morgan" <abcd@.senditon.com> wrote in message
> news:OPPPEXM7DHA.2300@.TK2MSFTNGP10.phx.gbl...
> > I think I have found out what it is...
> > After further investigation, the server is running SQL Server personal
> > edition and was exceeding the 5 user limit, which I am guessing is
causing
> > the jobs to not run and reporting back a generic error message.
> >
> > "Morgan" <abcd@.senditon.com> wrote in message
> > news:ercGkiL7DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > > Thanks in advance for any info.
> > >
> > > What would cause Optimization & Integrity Check jobs to fail? The
agent
> > info
> > > shows SQL State = 42000, which, according to BOL is "Syntax error,
> > > permission violation, or other nonspecific error. " Hmm... where to go
> > from
> > > here?
> > >
> > >
> > > Thanks,
> > >
> > > Morgan
> > >
> > >
> > >
> >
> >
>

Optimizations & Integrity Check Jobs failing?

Thanks in advance for any info.
What would cause Optimization & Integrity Check jobs to fail? The agent info
shows SQL State = 42000, which, according to BOL is "Syntax error,
permission violation, or other nonspecific error. " Hmm... where to go from
here?
Thanks,
MorganI think I have found out what it is...
After further investigation, the server is running SQL Server personal
edition and was exceeding the 5 user limit, which I am guessing is causing
the jobs to not run and reporting back a generic error message.
"Morgan" <abcd@.senditon.com> wrote in message
news:ercGkiL7DHA.1428@.TK2MSFTNGP12.phx.gbl...
> Thanks in advance for any info.
> What would cause Optimization & Integrity Check jobs to fail? The agent
info
> shows SQL State = 42000, which, according to BOL is "Syntax error,
> permission violation, or other nonspecific error. " Hmm... where to go
from
> here?
>
> Thanks,
> Morgan
>
>|||The "5 user limit" shouldn't cause this. That is only a throttling mechanism
(very misunderstood, though). Are you using the maint wizard? If so, specify
a report file in the maint plan and check for the real error message in that
report file.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Morgan" <abcd@.senditon.com> wrote in message
news:OPPPEXM7DHA.2300@.TK2MSFTNGP10.phx.gbl...
> I think I have found out what it is...
> After further investigation, the server is running SQL Server personal
> edition and was exceeding the 5 user limit, which I am guessing is causing
> the jobs to not run and reporting back a generic error message.
> "Morgan" <abcd@.senditon.com> wrote in message
> news:ercGkiL7DHA.1428@.TK2MSFTNGP12.phx.gbl...
> info
> from
>|||Thanks, Tibor. Unfortunately we had to burn the SQL Server install and
install EE on the box. If it starts happening again, we'll hit the logs for
more detail.
Thanks again.
Morgan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OfAd4CO7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> The "5 user limit" shouldn't cause this. That is only a throttling
mechanism
> (very misunderstood, though). Are you using the maint wizard? If so,
specify
> a report file in the maint plan and check for the real error message in
that
> report file.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "Morgan" <abcd@.senditon.com> wrote in message
> news:OPPPEXM7DHA.2300@.TK2MSFTNGP10.phx.gbl...
causing
agent
>

Optimization Jobs Fails

My SQL Server 2000 database optimization job fails each time it runs. This
job was created from the SQL Server database maintenance plan.
The "Reorganize data and index pages" and "Reorganize pages with the
original amount of free space" are checked parameters.
Error
Executed as user TEAM\SQL_ADMIN_ACCT. sqlmaint.exe failed. [SQLSTATE 42000]
[Error 22029]. The step failed.
Please help me resolve the error listed above.
Thanks,
That error message doesn't give us anything to go on, it is only Agent telling us it failed. Specify
a report file for the plan and look in that report file. Or open the maint wiz folder in EM, and
look at the history for a failed execution from there.
My guess is that you have some indexes on views or computed columns and maint wiz doesn't set the
needed SET setting in order to reorg such indexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:9343D047-7572-4A1F-A9BA-0CD9644B6550@.microsoft.com...
> My SQL Server 2000 database optimization job fails each time it runs. This
> job was created from the SQL Server database maintenance plan.
> The "Reorganize data and index pages" and "Reorganize pages with the
> original amount of free space" are checked parameters.
> Error
> Executed as user TEAM\SQL_ADMIN_ACCT. sqlmaint.exe failed. [SQLSTATE 42000]
> [Error 22029]. The step failed.
> Please help me resolve the error listed above.
> Thanks,
>

Optimization Jobs Fails

My SQL Server 2000 database optimization job fails each time it runs. This
job was created from the SQL Server database maintenance plan.
The "Reorganize data and index pages" and "Reorganize pages with the
original amount of free space" are checked parameters.
Error
Executed as user TEAM\SQL_ADMIN_ACCT. sqlmaint.exe failed. [SQLSTATE 420
00]
[Error 22029]. The step failed.
Please help me resolve the error listed above.
Thanks,That error message doesn't give us anything to go on, it is only Agent telli
ng us it failed. Specify
a report file for the plan and look in that report file. Or open the maint w
iz folder in EM, and
look at the history for a failed execution from there.
My guess is that you have some indexes on views or computed columns and main
t wiz doesn't set the
needed SET setting in order to reorg such indexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:9343D047-7572-4A1F-A9BA-0CD9644B6550@.microsoft.com...
> My SQL Server 2000 database optimization job fails each time it runs. Thi
s
> job was created from the SQL Server database maintenance plan.
> The "Reorganize data and index pages" and "Reorganize pages with the
> original amount of free space" are checked parameters.
> Error
> Executed as user TEAM\SQL_ADMIN_ACCT. sqlmaint.exe failed. [SQLSTATE 4
2000]
> [Error 22029]. The step failed.
> Please help me resolve the error listed above.
> Thanks,
>

Optimization Jobs Fails

My SQL Server 2000 database optimization job fails each time it runs. This
job was created from the SQL Server database maintenance plan.
The "Reorganize data and index pages" and "Reorganize pages with the
original amount of free space" are checked parameters.
Error
Executed as user TEAM\SQL_ADMIN_ACCT. sqlmaint.exe failed. [SQLSTATE 42000]
[Error 22029]. The step failed.
Please help me resolve the error listed above.
Thanks,That error message doesn't give us anything to go on, it is only Agent telling us it failed. Specify
a report file for the plan and look in that report file. Or open the maint wiz folder in EM, and
look at the history for a failed execution from there.
My guess is that you have some indexes on views or computed columns and maint wiz doesn't set the
needed SET setting in order to reorg such indexes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:9343D047-7572-4A1F-A9BA-0CD9644B6550@.microsoft.com...
> My SQL Server 2000 database optimization job fails each time it runs. This
> job was created from the SQL Server database maintenance plan.
> The "Reorganize data and index pages" and "Reorganize pages with the
> original amount of free space" are checked parameters.
> Error
> Executed as user TEAM\SQL_ADMIN_ACCT. sqlmaint.exe failed. [SQLSTATE 42000]
> [Error 22029]. The step failed.
> Please help me resolve the error listed above.
> Thanks,
>

Optimization and integrity check errors

At an installation of SharePointPortal Server 2003 were created automatically
3 databases.
The jobs for optimizations and integrity check for one of these databases
produce always errors:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver] [SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER'
I changed repeatedly QUOTED_IDENTIFIER, but the errors appeared again.
I used directly the DBCC statements for all tables of the database and I
became no errors.
What can I do?
Thank you in advance for your help
Andreas Marner
Sounds like you have a computed column or an Indexed view in the database.
There is a bug with the MP that does not allow it to handle these very well.
I recommend you create your own scheduled job to issue the backups and
reindexing for at least those db's.
Andrew J. Kelly SQL MVP
"Marner" <marner@.discussions.microsoft.com> wrote in message
news:6F859E50-AE75-4587-95F8-F4697BF4FD6F@.microsoft.com...
> At an installation of SharePointPortal Server 2003 were created
automatically
> 3 databases.
> The jobs for optimizations and integrity check for one of these databases
> produce always errors:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
SQL
> Server Driver] [SQL Server]DBCC failed because the following SET options
have
> incorrect settings: 'QUOTED_IDENTIFIER'
> I changed repeatedly QUOTED_IDENTIFIER, but the errors appeared again.
> I used directly the DBCC statements for all tables of the database and I
> became no errors.
> What can I do?
> Thank you in advance for your help
> Andreas Marner

Optimization and integrity check errors

At an installation of SharePointPortal Server 2003 were created automatically
3 databases.
The jobs for optimizations and integrity check for one of these databases
produce always errors:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver] [SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER'
I changed repeatedly QUOTED_IDENTIFIER, but the errors appeared again.
I used directly the DBCC statements for all tables of the database and I
became no errors.
What can I do?
Thank you in advance for your help
Andreas MarnerSounds like you have a computed column or an Indexed view in the database.
There is a bug with the MP that does not allow it to handle these very well.
I recommend you create your own scheduled job to issue the backups and
reindexing for at least those db's.
--
Andrew J. Kelly SQL MVP
"Marner" <marner@.discussions.microsoft.com> wrote in message
news:6F859E50-AE75-4587-95F8-F4697BF4FD6F@.microsoft.com...
> At an installation of SharePointPortal Server 2003 were created
automatically
> 3 databases.
> The jobs for optimizations and integrity check for one of these databases
> produce always errors:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
SQL
> Server Driver] [SQL Server]DBCC failed because the following SET options
have
> incorrect settings: 'QUOTED_IDENTIFIER'
> I changed repeatedly QUOTED_IDENTIFIER, but the errors appeared again.
> I used directly the DBCC statements for all tables of the database and I
> became no errors.
> What can I do?
> Thank you in advance for your help
> Andreas Marner

Opinions needed on AutoMate to replace SQLAgent

We have been told by the director over the DBAs that we may be standardizing ALL scheduled jobs and tasks (including SQL jobs) onto 1 tool called AutoMate (by NetworkAutomation), although I suspect the decision has already been made. I've argued that a standard for batch jobs is good but SQL has a job scheduler designed for SQL and integrated with SQL that works extremely well, but don't think I'm getting through.
Has anyone used AutoMate as a replacement for SQLAgent? I am open to hearing both pros and cons please. Thank you.

Signed, Frustrated DBAI don't see anything on their site that even mentions sql server

What about sercurity?

Looks like you'd have to write bat file with osql and then schedule them

They're not thinking of disabling the agent are they?

That would be bad|||I guess it depends on how much functionality you are willing to give up, how much developer time you are willing to invest on an ongoing basis to support giving up that functionality, and how much the folks at AutoMate are willing to pay your managment.

It is their call on what tools to use. If they are willing to spend enough, they can have whatever they want. You might suggest hiring a 24 by 265 operations staff... That can be more powerful and flexible than any scheduling system, if you can afford it.

-PatP|||I'm a MSFT bigot, but have to admit their scheduling tools are in the minor leagues. I have zero experience with AutoMate, however I have worked in what was at the time the world's largest client server environment and we used DEC's (remember them?) high-end scheduler to manage our jobs.

One thing that high-end scheduler's have that SQL Agent lacks is the ease with which job dependencies can be created and monitored. Frankly, SQL Agent was an after-thought and until Microsoft gets serious about it, will remain nothing but an excellent DTS task scheduler.

It seems you can combine AutoMate and SQL Agent and have a much improved environment.

And, PatP, what about the other 100 days ;) With or without a scheduling system, you'll want operations staff available 24x7x365 for any signifcant system.|||I don't see anything on their site that even mentions sql server
<snip>
Looks like you'd have to write bat file with osql and then schedule them

This is essentially what we do, we just use both SQL Agent to schedule SQL Server jobs, and where we need to also run non-SQL Server jobs, we either schedule them from within the agent using a job of type "operating system command" that references (typically) a BAT file on the server somewhere, or go the other way 'round (BAT to SQL Server) as Brett mentions.

I'm not sure it really matters what TOOL you use to do the scheduling, but why buy a new tool when Uncle Bill has provided the basics to get the same stuff done? Either way, you're going to have a mix, since the tool can't get into SQL Server directly, and SQL Server can't get into the tool directly.|||And, PatP, what about the other 100 days ;) With or without a scheduling system, you'll want operations staff available 24x7x365 for any signifcant system.Ummm, I was thinking weekends... Yeah, that's the ticket!

Jeeze Louise! A guy makes a typo... ;)

-PatP|||MaxA,
I've had no complaints with job dependancy by using the Advanced tab of each step defined -OR- in the one case where that wasn't good enough I did a query against sysjobshistory prior to continuing several step later. It was a work around, but it did work.

OSCI|||I don't dispute that you can accomplish what you need with SQL Agent, however I do dispute whether or not the cost/benefit is there. I value people's time over the cost of a tool like this, thus I usually lean towards a 3rd party solution that would free your valuable time to solve more trying problems.

I also direct DBA's, so my view is a bit different than a DBA's view. However, I would ask the DBA's to make that decision and then support it 100% because I know they're a lot smarter than me. And, they're the ones who would have to deal with any problems.

How would you prefer to spend your time - managing SQL Agent jobs or improving your data architecture? I think your director has the right idea, but the wrong approach. Leading extremely talented people can be very challenging, and we all make mistakes (even PatP made a typo today - sorry PatP, but you are always spot on so I gotta give ya a hard time).|||(even PatP made a typo today - sorry PatP, but you are always spot on so I gotta give ya a hard time).Could I interest you in some wonderful waterfront real estate in Louisiana?

-PatP|||Actually, now that I think about it in more detail, I'd be willing to debate the value of a servicable and well understood standard scheduler versus a wiz-bang proprietary one.

I tend to value people's time more than the cost of a software package too. However, I try to keep my eye on the "big picture"... Every employee with SQL Server experience I've interviewed has a good understanding of SQL Agent. Every consultant I've ever dealt with does too.

Even if another package could save five percent of the job setup time due to the features it provided, I don't think I could ever recoup the time that I'd loose explaining the wiz-bang to everyone that needed to use it... The time we spend scheduling and managing jobs is tiny compared to the time we spend explaining the ins and outs of our systems to folks.

If you had a mostly closed shop, with few newcomers, and a huge number of scheduled jobs, you might be able to justify a scheduler with the power of some of the DEC (and more often DECUS) products. 99% of the jobs I see do just fine with the SQL Agent features, and they really couldn't make use of much more. Your milage may vary, but I'd be surprised if many shops could justify the additional complexity of a third party scheduler!

-PatP|||To me it looks like this tool is much more than just a scheduler; it can also be used for automated testing: are my servers up and running, do my client applications work as they should, or do I send some notification (email) that one of the servers is down or about to.

Would you have a single point of failure?

If this tool is to replace all schedulers (how many are there?), including the one in SQL Server, is it just to do that? I would hope management is clear on this, since I'd be making hours on finding out exactly what I can and cannot do with this tool.|||One thing that high-end scheduler's have that SQL Agent lacks is the ease with which job dependencies can be created and monitored. Frankly, SQL Agent was an after-thought and until Microsoft gets serious about it, will remain nothing but an excellent DTS task scheduler.

Can I have a hit of what you're smoking?

DTS? What the hell is that?|||I appreciate all of the feedback and hope to have more over the next few days. I would like to ask if you haven't voted in the poll to please do so if you find the poll an accurate measure for your opinion.

Thank you again.

OSCI

Operators

Hi,
Can a job be assigned to notify 2 operators?
If i click on the drop down box of E-mail operator in Jobs,
i will see the 2nd operator.
But why can't the 2nd operator receive the notification?
If i press on Test to send an email at the Operators,
it can send a test email.
What should i do to let the other operator receive emails
when jobs are successful?
--
Thanks in advance,
SusannaCreate a new operator called User1&User2. In the EmailAddress of this user
put User1@.email.com;User2@.email.com
--
HTH. Ryan
"Susanna" <Susanna@.discussions.microsoft.com> wrote in message
news:CA4D4AC0-B3D5-4B26-9D06-62F9E55C418A@.microsoft.com...
> Hi,
> Can a job be assigned to notify 2 operators?
> If i click on the drop down box of E-mail operator in Jobs,
> i will see the 2nd operator.
> But why can't the 2nd operator receive the notification?
> If i press on Test to send an email at the Operators,
> it can send a test email.
> What should i do to let the other operator receive emails
> when jobs are successful?
> --
> Thanks in advance,
> Susanna|||Hi Ryan,
your example works!
--
Thanks,
Susanna
"Ryan" wrote:
> Create a new operator called User1&User2. In the EmailAddress of this user
> put User1@.email.com;User2@.email.com
> --
> HTH. Ryan
>
> "Susanna" <Susanna@.discussions.microsoft.com> wrote in message
> news:CA4D4AC0-B3D5-4B26-9D06-62F9E55C418A@.microsoft.com...
> > Hi,
> > Can a job be assigned to notify 2 operators?
> > If i click on the drop down box of E-mail operator in Jobs,
> > i will see the 2nd operator.
> > But why can't the 2nd operator receive the notification?
> > If i press on Test to send an email at the Operators,
> > it can send a test email.
> > What should i do to let the other operator receive emails
> > when jobs are successful?
> >
> > --
> > Thanks in advance,
> > Susanna
>
>

Operating system error 1450(Insufficient system resources exist to complete the requested se

Hello!

Hopefully someone can help me.

I have scripts to refresh database as SQL daily jobs. (O.S is Win2K3 and SQL server 2000 and SP4) It was worked and I got the following message this morning from SQL error log.

Internal I/O request 0x5FDA3C50: Op: Read, pBuffer: 0x0D860000, Size: 65536, Position: 25534864896, RetryCount: 10, UMS: Internal: 0x483099C8, InternalHigh: 0x0, Offset: 0xF1FF1E00, OffsetHigh: 0x5, m_buf: 0x0D860000, m_len: 65536, m_actualBytes: 0, m_errcode: 1450, BackupFile: \\XAPROD12MASTER\XAPROD\XAPROD_db_200701290000.BAK

BackupMedium::ReportIoError: read failure on backup device '\\XAPROD12MASTER\XAPROD\XAPROD_db_200701290000.BAK'. Operating system error 1450(Insufficient system resources exist to complete the requested service.).

This sounds exactly like an OS bug that we saw a long time ago where the Windows Remote File cache was behaving poorly and running out of its own buffers.

The best approach is to take this to Windows and see if there's a patch.