SQL job erroring out

Answered Question
Feb 6th, 2007

SQL Server Scheduled Job 'Monitor Disk Space' (0x313172495F4A5C4D901E00F3E13A7C3A) - Status: Failed - Invoked on: 2007-02-06 11:00:02 - Message: The job failed. The Job was invoked by Schedule 35 (Schedule 1). The last step to run was step 2 (TruncateCCMLog).

In SQL Enterprise manager under the Job tab it shows up as Failed. It looks like step 1 is running fine (TruncateCDRLog). It is hanging on step 2(TruncateCCMLog)

Listed below is the actual script.

IF EXISTS (select * from tempdb..sysobjects where name = '##diskspace')

BEGIN

drop table ##diskspace

END

IF EXISTS (select * from tempdb..sysobjects where name = '##databasesize')

BEGIN

drop table ##databasesize

END

create table ##diskspace

(drive varchar(5) not null,

MB_free int not null)

insert into ##diskspace

exec master..xp_fixeddrives

create table ##databasesize

(name varchar(24) not null,

db_size varchar(13) not null,

owner varchar(50),

dbid int not null,

created varchar(11),

status varchar(340),

compatibility_level int not null)

insert into ##databasesize

exec master..sp_helpdb

DECLARE @MB_free int

DECLARE @MB_CCM varchar (13)

SET nocount on

select @MB_free = MB_free from ##diskspace where drive = 'C'

IF @@ROWCOUNT = 0

BEGIN

RAISERROR(N'Could not find information for drive C', 16, 1) with log

END

ELSE IF @MB_free < 1000

BEGIN

select @MB_CCM = db_size from ##databasesize where name = 'CCM0304'

if @@ROWCOUNT = 0

BEGIN

RAISERROR(N'Could not find CCM0304 database size information', 16, 1) with log

END

ELSE

BEGIN

RAISERROR(N'Free space on C drive is less than 1G. CCM0304 database uses %s. CCM0304 transaction log will be truncated if less than 500MB free disk space is left.', 17, 1, @MB_CCM) with log

IF @MB_free < 500

BEGIN

USE CCM0304

CHECKPOINT

BACKUP LOG CCM0304 WITH NO_LOG

DBCC shrinkfile ('CCM0304_LOG',TRUNCATEONLY)

TRUNCATE TABLE ##databasesize

insert into ##databasesize

exec master..sp_helpdb

select @MB_CCM = db_size from ##databasesize where name = 'CCM0304'

RAISERROR(N'Free space on C drive was less than 500MB. CCM0304 transaction log was truncated. CCM0304 database uses %s.', 17, 1, @MB_CCM) with log

END

END

END

drop table ##diskspace

drop table ##databasesize

Any help trying to resolve this issue would be great.

I have this problem too.
0 votes
  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 5 (2 ratings)
Loading.
marco.alghisi Wed, 02/07/2007 - 01:13

Hi Zin,

i have the same problem with my call manager.

Yesterday i have seen the old thread and i have tried all the workaround but they didn't fix the problem.

In addition to this i have also another job failed (Expired subscription clean up). Also the user og the other thread have the same error but he didn't receive any answer about it.

Bug ID CSCsg32819 solve or try to solve only the "Monitor disk space" job failure.

Any other idea ?

Thanks

marcuscastellanos Wed, 02/07/2007 - 15:17

I'm actually having the same issue with the Expired Subscription clean up job failing as well. I will try the link from zen and let y'all know.

marco.alghisi Thu, 02/08/2007 - 00:52

Dear all,

finally i have solved, with the help of a TAC engineer, our problem. Both problem originates probably by a bug of the upgrade process.

Well..... The problem are two:

- Monitor disk space job;

- Expired subscrption clean up;

Monitor disk space

Be sure that the owner of all CCM database (ART, CDR, CCM0XXX) is \Administrator.

Expired subscription clean up

Change the owner of the job to 'sa', then run the job and it's work fine. Last change back the ownership of the job to \Administrator.

Thanks a lot to Yavuz (TAC engineer) for the help.

Best regards to everybody

Marco

marcuscastellanos Thu, 02/08/2007 - 09:49

The expired subscription clean up fix worked, I am still having an issue with the Monitor disk space job.

Art, ccm0303, ccm0304, The owners are all administrator. I have multiple databases which do not have the administrator set on it though.

CDR(owner unknown), db_cra(owner db_cra), db_cra_ccdr(owner db_cra), distribution(owner sa), master(owner sa), model(owner sa), msdb(owner sa), msdb(owner sa), pubs(owner sa), SchedulerDB(owner sa), tempdb(owner sa)

Am I correct in assuming that I am going to need to run the following:

So go to the SQL Query Analyzer, from the database drop down list select database CCM030X and then run the following query. The should be

machine's hostname:

sp_changedbowner '\administrator'

On all of the databases?

Thanks in advance

marco.alghisi Fri, 02/09/2007 - 01:45

Marcus,

you have to assign owner only for CCM related database not also system database.

In your case try to assign \Administrator to CDR database.

I think it is sufficient.

The other database db_cra and db_cra_cdr are ipcc related and don't need this (i suppose).

Regards

Marco

Actions

This Discussion