Using EXEC sp_track_db_growth - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using EXEC sp_track_db_growth

Author

Topic

jpiscit1
Posting Yak Master

130 Posts

Posted - 2003-04-29 : 14:14:17

When I run this query
EXEC sp_track_db_growth,
I get the following error.

Server: Msg 8115, Level 16, State 8, Procedure sp_track_db_growth, Line 45
Arithmetic overflow error converting numeric to data type numeric.

It works fine on all other datbases..

Anythought??

Thanks, John

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-29 : 14:23:46

Is sp_track_db_growth a system stored procedure or one that your company developed because I couldn't find it on any of our SQL Servers here?

The problem is that there is a value that is bigger than what the data type supports.

For example, say I have a value of 2,200,000,000 and my data type for it is int, you will get the error that you have because int does not support values that are larger than 2,147,483,647.

So you need to figure out which variable is blowing up and then modify the variable's data type to whatever data type that is needed. Often you will have to change the logic of your code to support the new data type. For instance, I recently had to change an int variable to float (I couldn't use bigint because the database is 7.0) and I had to make adjustments to my code to support float.

Tara

Edited by - tduggan on 04/29/2003 14:24:29

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-29 : 14:58:03

Do sp_helptext sp_track_db_growth

To see what it's doing...I'm sure it's like Tara mentioned though...

Brett

8-)

jpiscit1
Posting Yak Master

130 Posts

Posted - 2003-04-30 : 08:33:25

The stored Procedure is one I got from here used to track database growth.

Here it is:

CREATE PROC sp_track_db_growth
(
@dbnameParam sysname = NULL
)
AS
BEGIN

/***********************************************************************************************************
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
http://vyaskn.tripod.com

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_growth

Example 2:
To see the file growth information for pubs database:

EXEC sp_track_db_growth '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),file_size/1048576) AS [File Size (MB)],
Growth AS [Growth Percentage (%)]
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(5,2),((a.file_size * 100.00)/i1.file_size)-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]

END

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources