Scheduled job to reindex tables of a DB ends with failed, yet steps complete without errors in QA

I have done research and I see others reporting and asking but no resolutions as it appears the ones asking did not respond to responces.

I have a job that reindexes various tables on a DB on a weekly basis. The job completes with failed as below:

Executed as user: DOMAIN.MOC\psa. ...es, contact your system administrator. [SQLSTATE 01000] (Message 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)  DBCC execu...  The step failed.

Here is the only step of the job: (I am not the one that created the job.)

-- Addresses Table
dbcc dbreindex(tblAddresses, '', 90)

-- Alerts Table
dbcc dbreindex(tblAlerts, '', 95)

-- Audit Log
dbcc dbreindex(tblAuditLog, '', 95)

-- Companies
dbcc dbreindex(tblCompanies, '', 90)

-- Company Status Log
dbcc dbreindex(tblCompanyStatusLog, '', 95)

-- Credit Memo Header
dbcc dbreindex(tblCreditMemoHeader, '', 95)

-- Customer Quote Header
dbcc dbreindex(tblCustQuoteHeader, '', 90)

-- Customer Quote Details
dbcc dbreindex(tblCustQuoteDetail, '', 90)

-- Invoice Header
dbcc dbreindex(tblInvoiceHeader, '', 90)

-- Invoice Details
dbcc dbreindex(tblInvoiceDetails, '', 90)

-- Invoice Inspections
dbcc dbreindex(tblInvoiceInspection, '', 90)

-- Lookup Table
dbcc dbreindex(tblLookup, '', 90)

-- Messages
dbcc dbreindex(tblMessages, '', 90)

-- Notes
dbcc dbreindex(tblNotes, '', 90)

-- Parts Put away
dbcc dbreindex(tblPPA, '', 90)

-- PN Alternates
dbcc dbreindex(tblPnAlternates, '', 95)

-- PN Header
dbcc dbreindex(tblPnHeader, '', 95)

-- PN Stocklines
dbcc dbreindex(tblPnStkLines, '', 90)

-- PN Stockline Transactions
dbcc dbreindex(tblPnStkLinesTrans, '', 90)

-- PO Header
dbcc dbreindex(tblPoHeader, '', 95)

-- PO Items all indexes
dbcc dbreindex(tblPoItems, '', 90)

-- PO Receive Batch
dbcc dbreindex(tblPoReceiveBatch, '', 90)

-- Quarantine
dbcc dbreindex(tblQuarantine, '', 90)

-- Quarantine release details
dbcc dbreindex(tblQuarantineRelease, '', 90)

-- PO Receivers
dbcc dbreindex(tblReceivers, '', 90)

-- Rma Headers
dbcc dbreindex(tblRmaHeader, '', 90)

-- Rma Details
dbcc dbreindex(tblRmaDetails, '', 90)

-- Rma SubDetails
dbcc dbreindex(tblRmaSubDetails, '', 90)

-- Rolodex
dbcc dbreindex(tblRolodex, '', 90)

-- RTV Header
dbcc dbreindex(tblRtvHeader, '', 90)

-- RTV Items
dbcc dbreindex(tblRtvItems, '', 90)

-- Sales Order Header
dbcc dbreindex(tblSalesOrderHeader, '', 90)

-- Sales Order Details
dbcc dbreindex(tblSalesOrderDetail, '', 90)

-- Sales Order Reservations
dbcc dbreindex(tblSalesOrderRes, '', 90)

-- Security Table
dbcc dbreindex(tblSecuritySettings, '', 90)

-- Ship Via
dbcc dbreindex(tblShipVia, '', 95)

-- Terms
dbcc dbreindex(tblTerms, '', 98)

-- Titles
dbcc dbreindex(tblTitles, '', 98)

-- Users
dbcc dbreindex(tblUsers, '', 90)

-- Vendor Quote Header
dbcc dbreindex(tblVendQuoteHeader, '', 90)

-- Vendor Quote Details
dbcc dbreindex(tblVendQuoteDetail, '', 90)

-- Customer quote / vendor quote cross reference
dbcc dbreindex(tblXrefCqVq, '', 90)

-- Sales order item / purchase order item cross reference
dbcc dbreindex(tblXrefSoPo, '', 90)

-- Number Log
dbcc dbreindex(tblNumberLog, '', 99)

-- Zip codes
dbcc dbreindex(tblZipcodes, '', 99)

If I run the step pasting all lines into QA it runs to completion. Doing research, others have seen the same thing. Here is the output copied from the rpt file that I configured the query to output to:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Any insight and help to resolving this, if nothing other than to figure a way for the job to stop reporting failure if it is indeed succeeding as I thing it is.
LVL 1
John TolmachoffNetwork AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cwebb0222Commented:
Try this script and see if it works for you.  It will loop through each table in your database and rebuild all the indexes so run it during a period of low activity.  I run this script on our production servers as a scheduled job with no problems.  BTW I got the script from: http://www.sql-server-performance.com/rebuilding_indexes.asp

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor
0
John TolmachoffNetwork AdministratorAuthor Commented:
Does this reindex all tables, or just user tables and not system tables?
0
John TolmachoffNetwork AdministratorAuthor Commented:
Ran the above script. In QA, finishes fine. Create a job and run, same thing job says failed and same codes in job history.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anthony PerkinsCommented:
As you have surmised, I suspect this is a case of a false positive generated by the messages.  As I have been unable to duplicate, here are some random thoughts:

1. Try reducing the number of DBCC REINDEX to half and half again, until (or if) it succeeds.
2. Try adding WITH NO_INFOMSGS to the DBCC REINDEX

If none of that helps, let us know:
A. What Service Pack of SQL Server 2000 are you are using?
B. Do you get the failed message when you execute the job manually or only when you schedule the job?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cwebb0222Commented:
Verify this:

Open the properties of your job in Enterprise Manager.  Take a look at the one and only job step and make sure that "On Success Action" is not set to "Quit the job reporting failure".  
0
Anthony PerkinsCommented:
Good point.
0
John TolmachoffNetwork AdministratorAuthor Commented:
I was sure it was set to "Quit the Job reporting success" but you made me look and double check anyways.

Yes, it is set to "Quit the job reporting success."

I will try the suggestions by acperkins on Monday.
0
John TolmachoffNetwork AdministratorAuthor Commented:
After a lot of trial and error, the problem was found, although I do not know what exactly is the problem.

Tried all the suggestions and combinations of them.

It came down to adding NO_INFOMSGS and breaking the step into multiple steps. I then moved lines between steps until I located one line that was causing what ever step it was in to fail. I verified this by then commenting the line out and rerunning and it then succeeded. I have sent this information to the programmer for him to fix as all I care about is that the job shows success.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.