August 25, 2011 at 11:39 am
USE master
GO
CREATE PROC sp_track_db_growth
(
@dbnameParam sysname = NULL
)
AS
BEGIN
/***********************************************************************************************************
Tested on: SQL Server 2008
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())
SELECTCONVERT(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/1024) AS [File Size (GB)],
Growth AS [Growth Percentage (%)]
FROM
(
SELECTb.backup_start_date, a.backup_set_id, a.file_size, a.logical_name, a.[filegroup_name], a.physical_name,
(
SELECTCONVERT(numeric(5,2),((a.file_size * 100.00)/i1.file_size)-100)
FROMmsdb.dbo.backupfile i1
WHERE i1.backup_set_id =
(
SELECTMAX(i2.backup_set_id)
FROMmsdb.dbo.backupfile i2 JOIN msdb.dbo.backupset i3
ON i2.backup_set_id = i3.backup_set_id
WHEREi2.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
FROMmsdb.dbo.backupfile a JOIN msdb.dbo.backupset b
ON a.backup_set_id = b.backup_set_id
WHEREb.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
August 25, 2011 at 3:09 pm
if you insert the results of this query into a table every day you'll have metrics for the whole month and see the growth trend.
The probability of survival is inversely proportional to the angle of arrival.
August 25, 2011 at 3:16 pm
How do I put the results of a sp in a table?
August 25, 2011 at 4:51 pm
jdowling 65301 - Here is one way
-- Step 1. Create the table to save the data
CREATE TABLE GrowthofDB(C1 VARCHAR(20),C2 VARCHAR(20),DBNAME VARCHAR(100),FileGroupName VARCHAR(100),
LogicalFileName VARCHAR(100),PhysicalFileName VARCHAR(100)
,FileSize DECIMAL(10,2),Growth DECIMAL(10,2))
--Step 2 ... Insert the data by executing the SP
INSERT INTO GrowthofDB
EXEC sp_track_db_growth
August 26, 2011 at 7:36 am
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'sp_track_db_growth'.
Thats all that gives me...
August 26, 2011 at 8:51 am
jdowling 65301 (8/26/2011)
Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'sp_track_db_growth'.
Thats all that gives me...
1. I reran the code that I posted earlier, using cut and paste from this forum. No problem.
2. That said I
a. Created the SP sp_track_db_growth in my local DB NOT the Master DB.
b. Created the table in my local DB
Can you post the actual code you tested and maybe some one can be of further assistance.
August 26, 2011 at 8:59 am
Here's what it says. The first query ran fine, the sencond I ran against one of my local dbs and it gave me an error
INSERT INTO GrowthofDBEXEC sp_track_db_growth
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'sp_track_db_growth'.
August 26, 2011 at 9:08 am
jdowling 65301 (8/26/2011)
Here's what it says. The first query ran fine, the sencond I ran against one of my local dbs and it gave me an errorINSERT INTO GrowthofDBEXEC sp_track_db_growth
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'sp_track_db_growth'.
If what you have posted as your entry into SSMS ... I get the same error. Now if I execute it as
INSERT INTO GrowthofDB
EXEC sp_track_db_growth
It performs without an error
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply