December 2, 2010 at 1:34 pm
I am trying to run the following query on a sql server to identify how long my databases are taking. However, I get an error "Arithmetic overflow error converting numeric to data type varchar." when running the command against a 500GB database. I can run this same query on every other database on the server, but fails on this database.
Any ideas?
SELECT
s.database_name
, m.physical_device_name
, cast(s.backup_size/1000000 as varchar(14))+' '+'MB' as bkSize
, CAST (DATEDIFF(second,s.backup_start_date , s.backup_finish_date)AS VARCHAR(10))+' '+'Seconds' TimeTaken
, s.backup_start_date
FROM
msdb.dbo.backupset s
INNER JOIN
msdb.dbo.backupmediafamily m
ON
s.media_set_id = m.media_set_id
WHERE
s.database_name = 'DB1'
AND s.type = 'D'
ORDER BY
database_name
, backup_start_date
, backup_finish_date
December 2, 2010 at 1:38 pm
I figured it out.
Had to make the backup size varchar bigger.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply