Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2006-03-24
8
Medium Priority
?
1,557 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:John Tolmachoff
  • 4
  • 2
  • 2
8 Comments
 
LVL 1

Expert Comment

by:cwebb0222
ID: 16287042
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
 
LVL 1

Author Comment

by:John Tolmachoff
ID: 16287260
Does this reindex all tables, or just user tables and not system tables?
0
 
LVL 1

Author Comment

by:John Tolmachoff
ID: 16287354
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1000 total points
ID: 16289314
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
 
LVL 1

Expert Comment

by:cwebb0222
ID: 16289597
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16289621
Good point.
0
 
LVL 1

Author Comment

by:John Tolmachoff
ID: 16289663
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
 
LVL 1

Author Comment

by:John Tolmachoff
ID: 16330818
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question