Why is this Stored Procedure for Indexrebuild is failing ?

  • Hi All,

    We have a procedure scheduled everyday for Reindexing on database from past 3yrs. It was working fine till last month. Its failing because of below reason:

    Date3/6/2009 4:00:02 AM

    LogJob History (ReIndexinAudit)

    Step ID1

    ServerINCHDB

    Job NameReIndexinAudit

    Step NameStart

    Duration00:08:44

    Sql Severity23

    Sql Message ID0

    Operator Emailed

    Operator Net sent

    Operator Paged

    Retries Attempted0

    Message

    Executed as user: NT AUTHORITY\SYSTEM. ...ation currently 78% and Scan Density currently 25 [SQLSTATE 01000] (Message 0) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) Executing DBCC DBREINDEX (dbo.$AgCWHExceedAmendment) - fragmentation currently 23% and Scan Density currently 39 [SQLSTATE 01000] (Message 0) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) Executing DBCC DBREINDEX (dbo.$AgCWHExceedAmendmentItems) - fragmentation currently 2% and Scan Density currently 87 [SQLSTATE 01000] (Message 0) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) Executing DBCC DBREINDEX (dbo.$AgCWHExceedIBDocPreReceipt) - fragmentation currently 96% and Scan Density currently 13 [SQLSTATE 01000] (Message 0) DBCC execution completed. If DBCC printed error messages, cont... The step failed.

    Please find the attached script for SP.

    Am unable to get resolved this issue. Please any one help me reg the same. As its blocked and i need to make working this fine.

    Replies please... Urgent.

    Thanks,

    CH&HU.

  • Do you have the full error message? Is the output saved to a report file?

    Check your sql server error logs... you might see something there.

    and also run a dbcc checkdb on the database...

  • check your below statement in proc, i think you have to eliminate your square brackets or use them as i provided updated statement,

    SELECT @execstr = 'USE ' + @DatabaseName + ' DBCC DBREINDEX (['+ RTRIM(@tableschema) + '.' + RTRIM(@tablename) + '],' + RTRIM(@indexname) + ')'

    I updated this statement as:

    SELECT @execstr = 'USE ' + @DatabaseName + ' DBCC DBREINDEX (['+ RTRIM(@tableschema) + '].[' + RTRIM(@tablename) + '],' + RTRIM(@indexname) + ')'

    hope it will resolve.

    Tariq
    master your setup, master yourself.
    http://mssqlsolutions.blogspot.com

  • Hi I have seen and checked the SQL Server error logs. There i can see the log reports of of 3/1/2009 and after 3/2/2009 and finalyy 3/10/2009...

    I am wonder to see where are the 8 days log files gone.?

    Can you please help me regarding the same.....

    Thanks,

    CH&HU.

  • Hi,

    Thanks for your reply. I have recreated the procedure and scheduled for toninght. i will update u the improvements tomorow.

    Thanks,

    CH&HU.

  • I have a script that you're welcome to use, if you have difficulties getting your script working.

    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    Ola Hallengren

    http://ola.hallengren.com

  • CH&HU,

    I just mentioned only one statement to correct, you have to see other statements as well that are using the same logic to build object name.

    Thanks

    Tariq
    master your setup, master yourself.
    http://mssqlsolutions.blogspot.com

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply