dbcc errorlog

Question

dbcc errorlog

dev_programmer

Say Hey Kid

Points: 686
More actions
January 29, 2010 at 3:57 am

#220142

Dear Experts,
I have written the below mentioned sp. I have schedule the same for a particular time interval. The sp works fine but even after that in enterprize manager of SQL2k it shows a red mark. When i check the sysjobhistory table it shows the step is run successfully but in job outcome line it show job failed with sql_message_id 2528.
CREATE PROC dberrorlogbackup as
DECLARE @FILESIZE INT
create table #temp
(
srno int,
TIMEDATE datetime,
filesize int
)
insert into #temp exec xp_enumerrorlogs
select @FILESIZE = filesize from #temp where srno = 0
if @filesize > 5000000
DBCC errorlog
else
return(0)
Drop table #temp
Pls help me on this. Thanks in Advance
Dev - the sql newbie.
Thank you,
Regards,
Dev
email id :- dev_programmer@yahoo.co.in

Viewing 5 posts - 1 through 4 (of 4 total)

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

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

it may be the proc is returning some output. I'd use EXEC master.dbo.sp_cycle_errorlog; in place of the dbcc and I'd make sure that the return statements are consistent if you're going to use one.

when the condition is false you don't drop the temp table as the statement is after the return.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 2

btw. I actually cycle my logs every night on prod and usually weekely on dev boxes - more simple solution.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 3

I tend to agree with Colin here. If you're returned some value, it might indicate an error. Does anything return when you execute this?

I would typically recycle logs once a week myself on prod.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

dev_programmer Say Hey Kid Points: 686 More actions · Answer 4

Dear Experts,

Thanks for the reply.

But when i check the jobs history this is the message displayed.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I would also try the EXEC master.dbo.sp_cycle_errorlog n let you know.

Thanks,

Dev

Thank you,
Regards,
Dev
email id :- dev_programmer@yahoo.co.in