Reindex job fails

Question

Reindex job fails

yogi123

SSCrazy

Points: 2932
More actions
August 2, 2012 at 11:57 am

#261983

Hi every one ,
we have a job for DBReindex on the databse everyday.
I am using this store proc
ALTER procedure [dbo].[usp_SysStarsReIndexAll] as
declare @TableName varchar(75)
declare c_tblName cursor for
select name
from sysobjects
where type = 'U'
open c_tblName
fetch c_tblName into @TableName
while @@FETCH_STATUS = 0
begin
Dbcc DBReindex (@TableName,'',0/*, SORTED_DATA_REORG -- SQL 2005 change no longer an option*/)
fetch c_tblName into @TableName
end
close c_tblName
deallocate c_tblName
I got this error
Executed as user: PPSCENTRAL\mrcluster. ...ntact 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.

Viewing 15 posts - 1 through 14 (of 14 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

What happens when you execute the proc outside of a sql job?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

yogi123 SSCrazy Points: 2932 More actions · Answer 2

SQLRNNR (8/2/2012)
What happens when you execute the proc outside of a sql job?

It runs successfully outside the sql job

Lowell SSC Guru Points: 323518 More actions · Answer 3

was the password recently changed for the user PPSCENTRAL\mrcluster , and the job not updated withthe updated password, maybe?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

If it runs successfully outside of the job, then we will need more info from the error output of the job. Output all job step history to a text file and give us the full error from that file.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Lowell (8/2/2012)
was the password recently changed for the user PPSCENTRAL\mrcluster , and the job not updated withthe updated password, maybe?

I think the account is working since the dbcc command is working for the most part. One of the indexes is failing on the reindex though.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

yogi123 SSCrazy Points: 2932 More actions · Answer 6

SQLRNNR (8/2/2012)
If it runs successfully outside of the job, then we will need more info from the error output of the job. Output all job step history to a text file and give us the full error from that file.

sorry , but can you please tell me where i find the log error files in system.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

yogi123 (8/2/2012)
SQLRNNR (8/2/2012)
If it runs successfully outside of the job, then we will need more info from the error output of the job. Output all job step history to a text file and give us the full error from that file.
sorry , but can you please tell me where i find the log error files in system.

You need to set that in the advanced options. You configure the location.

Here is an article on how to get there. You should see the location to set the values once you get to the right place.

http://msdn.microsoft.com/en-us/library/ms191142.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lowell SSC Guru Points: 323518 More actions · Answer 8

a post here by Gail Shaw(Gilamonster) says it might be the log has filled up on the database:

http://www.sqlteam.com/forums/topic.asp?topic_id=132921

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

yogi123 SSCrazy Points: 2932 More actions · Answer 9

yogi123 (8/2/2012)
SQLRNNR (8/2/2012)
If it runs successfully outside of the job, then we will need more info from the error output of the job. Output all job step history to a text file and give us the full error from that file.
sorry , but can you please tell me where i find the log error files in system.

Thanks, I set up for error log file. and this schedule job running tonight so i will provide the error log file tomorrow . thanks again.

yogi123 SSCrazy Points: 2932 More actions · Answer 10

Lowell (8/2/2012)
a post here by Gail Shaw(Gilamonster) says it might be the log has filled up on the database:
http://www.sqlteam.com/forums/topic.asp?topic_id=132921

Log backup successfully executed every 15 min everyday.

so i guess log backup might not be issue.

Lowell SSC Guru Points: 323518 More actions · Answer 11

the reindexing can be a huge operation; 1.5x the size of the indexes being rebuilt, as i seem to remember it. so the reindexing of a bunch of indexes that take say, a 500meg table needs 750meg.

it would be very easy for the reindexing operaiton to bloat the database.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

yogi123 SSCrazy Points: 2932 More actions · Answer 12

Lowell (8/2/2012)
the reindexing can be a huge operation; 1.5x the size of the indexes being rebuilt, as i seem to remember it. so the reindexing of a bunch of indexes that take say, a 500meg table needs 750meg.
it would be very easy for the reindexing operaiton to bloat the database.

ok, so it mean I am doing re-indexing for my DATABASE, which is 90GB so i need 140GB to reindex?

Lowell SSC Guru Points: 323518 More actions · Answer 13

yogi123 (8/2/2012)
Lowell (8/2/2012)
the reindexing can be a huge operation; 1.5x the size of the indexes being rebuilt, as i seem to remember it. so the reindexing of a bunch of indexes that take say, a 500meg table needs 750meg.
it would be very easy for the reindexing operaiton to bloat the database.
ok, so it mean I am doing re-indexing for my DATABASE, which is 90GB so i need 140GB to reindex?

books online has some better examples than the way i explained it, but if your db was 90 gig, you might need as much as 90+90+45 = 225 total disk space to support it, or 1.5x the db in additional space.

http://msdn.microsoft.com/en-us/library/ms191183.aspx

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

yogi123 SSCrazy Points: 2932 More actions · Answer 14

yogi123 (8/2/2012)
SQLRNNR (8/2/2012)
If it runs successfully outside of the job, then we will need more info from the error output of the job. Output all job step history to a text file and give us the full error from that file.
sorry , but can you please tell me where i find the log error files in system.

Hello

I attach error file so please find an attachment and give me some possible solution

Thanks