March 12, 2009 at 6:35 am
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.
March 12, 2009 at 7:09 am
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...
March 12, 2009 at 8:42 am
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
March 12, 2009 at 10:41 pm
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.
March 12, 2009 at 10:44 pm
Hi,
Thanks for your reply. I have recreated the procedure and scheduled for toninght. i will update u the improvements tomorow.
Thanks,
CH&HU.
March 13, 2009 at 3:48 am
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
March 13, 2009 at 9:31 am
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