Growth Rate - 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 2008 Forums

SQL Server Administration (2008)

Growth Rate

Author

Topic

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2013-10-06 : 22:53:13

Can anyone help me as I use the SP_TRACK_GROWTH which still shows less than what the current size of the database.

The LDF Growth rate is set to 1000MB and when I execute the SP it still shows not exactly with the Growth Rate to the MDF size.

How to get this accurately, can anyone please show.

Thanks in Advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-07 : 12:44:50

What is sp_track_growth? Where did you get it?

You can run this code to track database growth and then store it into a table over time:


select 
                DATABASE_NAME = db_name(s_mf.database_id), 
                DATABASE_SIZE = convert(bigint, convert(bigint, sum(s_mf.size))*8), 
                REMARKS = convert(varchar(254),null) 
            from sys.master_files s_mf 
            where 
                s_mf.state = 0 and 
                has_dbaccess(db_name(s_mf.database_id)) = 1 
            group by s_mf.database_id 
            order by 1

Here's some custom code I wrote a while back that can track the information for multiple SQL instances: http://weblogs.sqlteam.com/tarad/archive/2008/12/16/How-to-track-database-growth-across-multiple-SQL-Server-instances.aspx

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-07 : 12:50:56

I am not familiar with sp_track_growth. Googled for it and found a sp_track_db_growth that someone had written. If that is what you are referring to, it very well could be that that procedure was tested only on SQL 7 and SQL 2000; SQL 2008 has added a lot of features that can affect the way data is stored and can provide insights into how it is stored.

For example:

SELECT OBJECT_NAME(object_id),* FROM sys.dm_db_partition_stats;

SELECT * 
   FROM sys.dm_os_performance_counters 
   WHERE counter_name IN 
   ( 
       'Data File(s) Size (KB)', 
       'Log File(s) Size (KB)', 
       'Log File(s) Used Size (KB)', 
       'Percent Log Used' 
   )

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-07 : 12:51:55

This has been happening to me a lot lately :) I type something, get distracted and then click submit a while later!

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2013-10-07 : 17:58:20

Thanks all for responding to this problem. As James quoted it right that SP_TRACK_GROWTH is good for me to clinch the desired result.
I have got it from the Web and is available.

Code:

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_track_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())
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

But is not exactly what I have seen on corresponding MDF file size with accuracy.

Ex:

MDF on Disk - 100 GB

Through above SP is less than 100GB

This is where I couldn't substantiate accurately.

Any further help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-07 : 22:42:18

Well what size is it from the stored procedure? Please provide more info. It's likely just a small conversion issue.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2013-10-08 : 17:34:08

Thanks for responding again.

SIZE in Growth Percentage in Growth

286357.44 0.41

Actually Size on Disk is 288GB as against this SP has delivered 286GB which is short of 2GB, this makes inaccurate, though the percentage in Growth is correct.

As rightly said, there requires a small conversion into the SP.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-08 : 18:22:26

I think you need to look at this at the KB level instead of GB level. What does the query I posted show? And what does the DIR command show for that file. It'll be in bytes, but we can convert to KB easily.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2013-10-08 : 18:34:27

It is quite easily convertible to KB level and even then it is not matching up with exact numbers.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-08 : 18:59:44

But does the query I posted or the query James posted give you the "correct" answer?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2013-10-08 : 19:18:49

Unfortunately it is not accepting as there are 2 entries on dual instances (2000 and 2008 R2 Versions) needs to generate this SP which I have used.

How can I separate to run this SP to execute exclusively for 2000 based DB (2008 R 2 is done).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-09 : 12:28:01

I do not understand your last reply.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2013-10-13 : 17:22:59

SP has bee executed on the same databases on 2008 R 2 and on 2000 Server.

But on 2000 Server the same DB is not reflecting the size of the DB not coinciding with existing MDF size.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-14 : 11:40:40

I don't have any 2000 instances installed anymore to be able to help you with this. If I recall correctly, the formula is slightly different for older versions.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources