Have an INTERESTING issue !!! I have a stored procedure (SP) that returns a record set of database growth activity. I am trying to capture the output to a table with an INSERT INTO. When I execute the SP as follows
exec usp_Monitor_DB_Growth
The message returned is as follows:
Server: Msg 8115, Level 16, State 8, Procedure usp_Monitor_DB_Growth, Line 17 Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
Here is the code for the SP …
CREATE PROC usp_Monitor_DB_Growth
( @dbnameParam sysname = NULL )
AS
BEGIN
DECLARE @dbname sysname
/* Work with current database if a database name is not specified */
SET @dbname = COALESCE(@dbnameParam, DB_NAME())
TRUNCATE TABLE Monitor_DB_Growth
INSERT INTO Monitor_DB_Growth
SELECT
@dbname AS [Database Name],
'DB' AS [DB_or_TLog],
CONVERT(char, backup_start_date, 111) AS [Date],
CONVERT(char, backup_start_date, 108) AS [Time],
CONVERT(numeric(15,2),file_size/1048576) AS [File Size(MB)],
Growth AS [Growth Percentage (%)],
GetDate() AS [Date_Stats_Pulled]
FROM
(
SELECT b.backup_start_date,
a.backup_set_id,
a.file_size,
a.logical_name,
a.[filegroup_name],
a.physical_name,
(
SELECT CONVERT(numeric(15,2),((a.file_size * 100.00)/bf.file_size)-100)
FROM MSDB.dbo.backupfile bf
WHERE bf.backup_set_id =
(
SELECT MAX(bf2.backup_set_id)
FROM MSDB.dbo.backupfile AS bf2
JOIN MSDB.dbo.backupset AS bs
ON bf2.backup_set_id = bs.backup_set_id
WHERE bf2.backup_set_id < a.backup_set_id AND
bf2.file_type ='D' AND
bs.database_name = @dbname AND
bf2.logical_name = a.logical_name AND
bf2.logical_name = bf.logical_name AND
bs.type = 'D'
) AND
bf.file_type = 'D'
) AS Growth
FROM MSDB.dbo.backupfile AS a
JOIN MSDB.dbo.backupset AS b
ON a.backup_set_id = b.backup_set_id
WHERE b.database_name = @dbname AND
a.file_type = 'D' AND
b.type = 'D'
) AS Derived
WHERE (Growth <> 0.0) OR
(Growth IS NULL)
ORDER BY logical_name, [Date]
END
GO
As you can see, I have the statement of INSERT INTO Monitor_DB_Growth.
If you comment out that line of the SP, it runs fine and returns the values as expected.
Here is a copy of the table named Monitor_DB_Growth.
CREATE TABLE [dbo].[Monitor_DB_Growth] (
[Server_Name] [varchar] (50) NULL ,
[DB_or_TLog] [char] (4) NULL ,
[Date_Last_Extended] [datetime] NULL ,
[Time_Last_Extended] [datetime] NULL ,
[File_Size] [numeric](28, 0) NULL ,
[Growth_Percent] [decimal](5, 2) NULL ,
[Date_Stats_Pulled] [datetime] NULL
) ON [PRIMARY]
AS well as a copy of the MSDB.dbo.backupfile (of course you cannot script out sys tables like the example above, so here is a brief description) …
backup_set_id int 4 0
first_family_number tinyint 1 1
first_media_number smallint 2 1
filegroup_name nvarchar 128 1
page_size int 4 1
file_number numeric 9 0
backed_up_page_count numeric 9 1
file_type char 1 1
source_file_block_size numeric 9 1
file_size numeric 13 1
logical_name nvarchar 128 1
physical_drive varchar 260 1
physical_name varchar 260 1
The field in question, I believe, is the file_size. If you view this field in design mode, it shows it has precision set to 20. I am only allowed to set my value with a precision of 18.
IS THIS THE ISSUE … and can I resolve it in order to load my table? Any and all help is always appreciated !!!
PS. This SP is being run predominately from SS7. Have yet to try it out in SS2K though I believe it should fly.
Thanks
J. Kusch
exec usp_Monitor_DB_Growth
The message returned is as follows:
Server: Msg 8115, Level 16, State 8, Procedure usp_Monitor_DB_Growth, Line 17 Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
Here is the code for the SP …
CREATE PROC usp_Monitor_DB_Growth
( @dbnameParam sysname = NULL )
AS
BEGIN
DECLARE @dbname sysname
/* Work with current database if a database name is not specified */
SET @dbname = COALESCE(@dbnameParam, DB_NAME())
TRUNCATE TABLE Monitor_DB_Growth
INSERT INTO Monitor_DB_Growth
SELECT
@dbname AS [Database Name],
'DB' AS [DB_or_TLog],
CONVERT(char, backup_start_date, 111) AS [Date],
CONVERT(char, backup_start_date, 108) AS [Time],
CONVERT(numeric(15,2),file_size/1048576) AS [File Size(MB)],
Growth AS [Growth Percentage (%)],
GetDate() AS [Date_Stats_Pulled]
FROM
(
SELECT b.backup_start_date,
a.backup_set_id,
a.file_size,
a.logical_name,
a.[filegroup_name],
a.physical_name,
(
SELECT CONVERT(numeric(15,2),((a.file_size * 100.00)/bf.file_size)-100)
FROM MSDB.dbo.backupfile bf
WHERE bf.backup_set_id =
(
SELECT MAX(bf2.backup_set_id)
FROM MSDB.dbo.backupfile AS bf2
JOIN MSDB.dbo.backupset AS bs
ON bf2.backup_set_id = bs.backup_set_id
WHERE bf2.backup_set_id < a.backup_set_id AND
bf2.file_type ='D' AND
bs.database_name = @dbname AND
bf2.logical_name = a.logical_name AND
bf2.logical_name = bf.logical_name AND
bs.type = 'D'
) AND
bf.file_type = 'D'
) AS Growth
FROM MSDB.dbo.backupfile AS a
JOIN MSDB.dbo.backupset AS b
ON a.backup_set_id = b.backup_set_id
WHERE b.database_name = @dbname AND
a.file_type = 'D' AND
b.type = 'D'
) AS Derived
WHERE (Growth <> 0.0) OR
(Growth IS NULL)
ORDER BY logical_name, [Date]
END
GO
As you can see, I have the statement of INSERT INTO Monitor_DB_Growth.
If you comment out that line of the SP, it runs fine and returns the values as expected.
Here is a copy of the table named Monitor_DB_Growth.
CREATE TABLE [dbo].[Monitor_DB_Growth] (
[Server_Name] [varchar] (50) NULL ,
[DB_or_TLog] [char] (4) NULL ,
[Date_Last_Extended] [datetime] NULL ,
[Time_Last_Extended] [datetime] NULL ,
[File_Size] [numeric](28, 0) NULL ,
[Growth_Percent] [decimal](5, 2) NULL ,
[Date_Stats_Pulled] [datetime] NULL
) ON [PRIMARY]
AS well as a copy of the MSDB.dbo.backupfile (of course you cannot script out sys tables like the example above, so here is a brief description) …
backup_set_id int 4 0
first_family_number tinyint 1 1
first_media_number smallint 2 1
filegroup_name nvarchar 128 1
page_size int 4 1
file_number numeric 9 0
backed_up_page_count numeric 9 1
file_type char 1 1
source_file_block_size numeric 9 1
file_size numeric 13 1
logical_name nvarchar 128 1
physical_drive varchar 260 1
physical_name varchar 260 1
The field in question, I believe, is the file_size. If you view this field in design mode, it shows it has precision set to 20. I am only allowed to set my value with a precision of 18.
IS THIS THE ISSUE … and can I resolve it in order to load my table? Any and all help is always appreciated !!!
PS. This SP is being run predominately from SS7. Have yet to try it out in SS2K though I believe it should fly.
Thanks
J. Kusch