asked on

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.

cwebb0222

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

John Tolmachoff

ASKER

Does this reindex all tables, or just user tables and not system tables?

John Tolmachoff

ASKER

Ran the above script. In QA, finishes fine. Create a job and run, same thing job says failed and same codes in job history.

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cwebb0222

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".

Anthony Perkins

Good point.

John Tolmachoff

ASKER

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.

John Tolmachoff

ASKER

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.