job failing

[beechill4]

Hi,

A job i run in sql server 200 has started to fail. it previously ran without any problem.

the error message i get in the job history is:
Invalid object name 'SYSFILES'. [SQLSTATE 42S02] (Error 208).

the job executes a stored procedure

what is the cause of this, and how do i solve it?

Thanks

b

 

[cyno]

Can you give me more details of the job of what its doing???

Cyno

 

[beechill4]

hi cyno,

this is the stored procedure that is being called:

CREATE PROCEDURE USP_DATABASE_SIZES_DBA
AS

-- DECLARES
DECLARE @DBNAME VARCHAR(40)
DECLARE @STRING VARCHAR(300)

-- STORES EACH DATABASE NAME IN A TEMP TABLE
CREATE TABLE #DATABASE_NAMES
(
DBNAME VARCHAR (50)
)

INSERT INTO #DATABASE_NAMES
SELECT NAME FROM MASTER..SYSDATABASES
WHERE NAME NOT IN ('MASTER', 'MODEL', 'MSDB', 'NORTHWIND', 'TEMPDB')

-- TEMP TABLE FOR DATABASE SIZING INFO
CREATE TABLE #DBSIZES
(
FILE_TYPE VARCHAR(10),
TOTAL_SPACE INT,
SPACE_USED INT,
FREE_SPACE INT,
DBNAME VARCHAR(50),
DATE_RUN DATETIME DEFAULT GETDATE()
)


-- MAIN SECTION --> LOOP THROUGH ALL OF THE DATABASES AND GET SPACE INFO

-- CURSOR FOR GETTING DATABASE NAMES
DECLARE CURDATABASE CURSOR FOR SELECT DBNAME FROM #DATABASE_NAMES
OPEN CURDATABASE
FETCH NEXT FROM CURDATABASE INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN


-- INSERTING SPACE INFO INTO TEMP TABLE

SELECT @STRING = 'USE [' + @DBNAME + ']' + CHAR(13) + CHAR(10) + 'INSERT INTO #DBSIZES (FILE_TYPE, TOTAL_SPACE,
SPACE_USED, FREE_SPACE) SELECT FILEID, MAXSIZE,SIZE, (MAXSIZE - SIZE) FROM SYSFILES' + CHAR(13) + CHAR(10) +
'UPDATE #DBSIZES SET DBNAME = ''' + @DBNAME + ''' WHERE DBNAME IS NULL'

EXEC (@STRING)

FETCH NEXT FROM CURDATABASE INTO @DBNAME
END
CLOSE CURDATABASE
DEALLOCATE CURDATABASE


-- UPDATING THE FILE_TYPE COLUMN

UPDATE #DBSIZES SET FILE_TYPE = 'DATA FILE' WHERE FILE_TYPE = '1'
UPDATE #DBSIZES SET FILE_TYPE = 'LOG FILE' WHERE FILE_TYPE = '2'

-- CONVERTING SPACE INFO INTO MB FROM 8K PAGES.

UPDATE #DBSIZES SET SPACE_USED = (SPACE_USED*8)/1024
UPDATE #DBSIZES SET FREE_SPACE = (FREE_SPACE*8)/1024


-- RE-ARRANGING THE DATA INTO THE UTILITY..DATABASE_SIZINGS TABLE

SELECT * FROM #DBSIZES

INSERT INTO UTILITY..DATABASE_SIZINGS
(DATABASE_NAME, FILE_TYPE, TOTAL_SPACE_MB, SPACE_USED_MB, FREE_SPACE_MB, DATE_RUN)
SELECT DBNAME, FILE_TYPE, TOTAL_SPACE, SPACE_USED, FREE_SPACE, DATE_RUN FROM #DBSIZES


-- IDENTIFYING UNRESTRICTED GROWTH SETTINGS

UPDATE UTILITY..DATABASE_SIZINGS SET TOTAL_SPACE_MB = 'UNRESTRICTED' WHERE TOTAL_SPACE_MB = '-1'
UPDATE UTILITY..DATABASE_SIZINGS SET FREE_SPACE_MB = 'N/A - UNRESTRICTED' WHERE FREE_SPACE_MB < '0'


--CONVERTING SPACE TO MB FROM 8K PAGES - DONE AFTER "UNRESTRICTED IDENTIFIED"
UPDATE UTILITY..DATABASE_SIZINGS SET TOTAL_SPACE_MB = (TOTAL_SPACE_MB*8)/1024 WHERE TOTAL_SPACE_MB <> 'UNRESTRICTED'

-- CLEAN-UP : DROP THE TEMPORARY TABLES

DROP TABLE #DATABASE_NAMES
DROP TABLE #DBSIZES
GO

 

[beechill4]

and this is the result in query analyser when i execute the query:



(8 row(s) affected)


(2 row(s) affected)


(2 row(s) affected)


(2 row(s) affected)


(2 row(s) affected)


(2 row(s) affected)


(2 row(s) affected)


(2 row(s) affected)


(2 row(s) affected)


(2 row(s) affected)


(2 row(s) affected)


(2 row(s) affected)


(2 row(s) affected)

Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'SYSFILES'.

(2 row(s) affected)


(2 row(s) affected)


(7 row(s) affected)


(7 row(s) affected)


(14 row(s) affected)


(14 row(s) affected)


(14 row(s) affected)


(14 row(s) affected)


(14 row(s) affected)


(11 row(s) affected)


(2 row(s) affected)