Arithmetic overflow error converting numeric to data type numeric. why for this script?
-
this script will give you the database name,
backup date, and subsequent growth between each
backup.
i'm getting the "Arithmetic overflow error...", but don't know why.
thoughts?
/* Work with current database if a database name is not specified */
DECLARE @dbname sysname
SET @dbname = DB_NAME()
SELECT CONVERT(char, backup_start_date, 111) AS [Date], --yyyy/mm/dd format
--CONVERT(char, backup_start_date, 108) AS [Time],
@dbname AS [Database Name],
-- [filegroup_name] AS [Filegroup Name],
--logical_name AS [Logical Filename],
--physical_name AS [Physical Filename],
CONVERT(numeric(9,2),(backed_up_page_count * 8192)/1048576) AS [File Size (MB)],
Growth AS [Growth Percentage (%)]
FROM
(
SELECT b.backup_start_date,
a.backup_set_id,
(a.backed_up_page_count),
--a.logical_name,
--a.[filegroup_name],
--a.physical_name,
(
SELECT CONVERT(numeric(5,2),
(((a.backed_up_page_count*8192) * 100.00) / (i1.backed_up_page_count*8192))-100)
FROM msdb.dbo.backupfile i1
WHERE i1.backup_set_id =
(
SELECT MAX(i2.backup_set_id)
FROM msdb.dbo.backupfile i2 JOIN msdb.dbo.backupset i3
ON i2.backup_set_id = i3.backup_set_id
WHERE i2.backup_set_id < a.backup_set_id AND
i2.file_type='D' AND
i3.database_name = @dbname AND--i2.logical_name = a.logical_name AND
--i2.logical_name = i1.logical_name ANDi3.type = 'D'
) AND
i1.file_type = 'D'
) AS Growth
FROM msdb.dbo.backupfile a JOIN msdb.dbo.backupset 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 [Date]
_________________________
-
what's your biggest database size?
MVDBA
-
Try this query...
DECLARE @dbname sysname
SET @dbname = DB_NAME()
SELECT CONVERT(char, backup_start_date, 111) AS [Date], --yyyy/mm/dd format
--CONVERT(char, backup_start_date, 108) AS [Time],
@dbname AS [Database Name],
-- [filegroup_name] AS [Filegroup Name],
--logical_name AS [Logical Filename],
--physical_name AS [Physical Filename],
CONVERT(numeric(9,2),(backed_up_page_count * 8192)/1048576) AS [File Size (MB)],
Growth AS [Growth Percentage (%)]
FROM
(
SELECT b.backup_start_date,
a.backup_set_id,
(a.backed_up_page_count),
--a.logical_name,
--a.[filegroup_name],
--a.physical_name,
(
SELECT CONVERT(numeric(5,2),
--(((a.backed_up_page_count*8192) * 100.00) / (i1.backed_up_page_count*8192))-100)
(a.backed_up_page_count*100.00/i1.backed_up_page_count)-100)
FROM msdb.dbo.backupfile i1
WHERE i1.backup_set_id =
(
SELECT MAX(i2.backup_set_id)
FROM msdb.dbo.backupfile i2 JOIN msdb.dbo.backupset i3
ON i2.backup_set_id = i3.backup_set_id
WHERE i2.backup_set_id < a.backup_set_id AND
i2.file_type='D' AND
i3.database_name = @dbname AND
--i2.logical_name = a.logical_name AND
--i2.logical_name = i1.logical_name AND
i3.type = 'D'
) AND
i1.file_type = 'D'
) AS Growth
FROM msdb.dbo.backupfile a JOIN msdb.dbo.backupset 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 [Date]
-
my database is nothing... at around 50gb.
ponnapalli,
tried your script... same issue.
thoughts?
_________________________
-
Your problem is the numeric(5,2), changing it to numeric(6,2) fixes the issue:
DECLARE @backed_up_page_count numeric(10,0)
, @backed_up_page_count1 numeric(10,0)
SELECT @backed_up_page_count = ((50 * 1073741824.0) / 8192) -- 50GB
, @backed_up_page_count1 = ((1 * 1073741824.0) / 8192) -- 1 GB
SELECT @backed_up_page_count, @backed_up_page_count1
, CONVERT(numeric(9,2),(@backed_up_page_count * 8192)/1048576)
, CONVERT(numeric(6,2),(((@backed_up_page_count*8192) * 100.00)
/ (@backed_up_page_count1*8192))-100)
Andy
-
andy... thanks for the correction, but getting the following
error:
Server: Msg 8115, Level 16, State 8, Line 4
Arithmetic overflow error converting numeric to data type numeric.
referencing line:
SELECT CONVERT(char, backup_start_date, 111) AS [Date], --yyyy/mm/dd format
_________________________
-
Hmm. Biggest database I can test this on using SQL2000 is only 42Gb, but it works there with no problems.
All my really big databases (600Gb +) are on SQL2005, and when I run the same query there I get this error:
Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.
I have my own version of your query (looks like we lifted it from the same source!), and I use mine in a stored procedure (original author properly credited, of course
) . I get no errors at all with my version, even on 600Gb databases, so I've posted it here. I haven't got aroud to doing a DIFF on the two code portions to see why mine appears to work where yours doesn't, but try it anyway:/****** Object: Stored Procedure dbo.sp_track_db_size_used Script Date: 19/05/2005 12:54:09 ******/
CREATE procedure sp_track_db_size_used
(@dbnameParam sysname = null)
as
/***********************************************************************************************************
Copyright © 2001 Narayana Vyas Kondreddi. All rights reserved.
Purpose: To calulate the file growth percentages for a given database and to show you the rate at which
your databases are growing, so that you can plan ahead for your future storage needs.
Written by: Narayana Vyas Kondreddi
Tested on: SQL Server 7.0, SQL Server 2000
Date modified: December-3-2001 01:33 AM IST
Email: vyaskn@hotmail.com
Usage: Run this script in the master database to create the stored procedure. Once it is created,
you could run it from any of your user databases. If the first parameter (database name) is
not specified, the procedure will use the current database.
Example 1:
To see the file growth information of the current database:
EXEC sp_track_db_size_used
Example 2:
To see the file growth information for pubs database:
EXEC sp_track_db_size_used 'pubs'
***********************************************************************************************************/
DECLARE @dbname sysname
/* Work with current database if a database name is not specified */
SET @dbname = COALESCE(@dbnameParam, DB_NAME())
SELECT CONVERT(char, backup_start_date, 111) AS [Date], --yyyy/mm/dd format
CONVERT(char, backup_start_date, 108) AS [Time],
@dbname AS [Database Name], [filegroup_name] AS [Filegroup Name],
logical_name AS [Logical Filename],
physical_name AS [Physical Filename],
CONVERT(numeric(9,2),(backed_up_page_count * 8192)/1048576) AS [File Size (MB)],
Growth AS [Growth Percentage (%)]
FROM
(
SELECT b.backup_start_date,
a.backup_set_id,
(a.backed_up_page_count),
a.logical_name,
a.[filegroup_name],
a.physical_name,
(
SELECT CONVERT(numeric(6,2),
(((a.backed_up_page_count*8192) * 100.00)/(i1.backed_up_page_count*8192))-100)
FROM msdb.dbo.backupfile i1
WHERE i1.backup_set_id =
(
SELECT MAX(i2.backup_set_id)
FROM msdb.dbo.backupfile i2 JOIN msdb.dbo.backupset i3
ON i2.backup_set_id = i3.backup_set_id
WHERE i2.backup_set_id < a.backup_set_id AND
i2.file_type='D' AND
i3.database_name = @dbname AND
i2.logical_name = a.logical_name AND
i2.logical_name = i1.logical_name AND
i3.type = 'D'
) AND
i1.file_type = 'D'
) AS Growth
FROM msdb.dbo.backupfile a JOIN msdb.dbo.backupset 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]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply