SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Growth Rate
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pdset
Constraint Violating Yak Guru

300 Posts

Posted - 10/06/2013 :  22:53:13  Show Profile  Reply with Quote
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

USA
36941 Posts

Posted - 10/07/2013 :  12:44:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/

Edited by - tkizer on 10/07/2013 12:49:01
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 10/07/2013 :  12:50:56  Show Profile  Reply with Quote
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' 
   )
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 10/07/2013 :  12:51:55  Show Profile  Reply with Quote


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

pdset
Constraint Violating Yak Guru

300 Posts

Posted - 10/07/2013 :  17:58:20  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 10/07/2013 :  22:42:18  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

pdset
Constraint Violating Yak Guru

300 Posts

Posted - 10/08/2013 :  17:34:08  Show Profile  Reply with Quote
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.

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 10/08/2013 :  18:22:26  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

pdset
Constraint Violating Yak Guru

300 Posts

Posted - 10/08/2013 :  18:34:27  Show Profile  Reply with Quote
It is quite easily convertible to KB level and even then it is not matching up with exact numbers.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 10/08/2013 :  18:59:44  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

pdset
Constraint Violating Yak Guru

300 Posts

Posted - 10/08/2013 :  19:18:49  Show Profile  Reply with Quote
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).
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 10/09/2013 :  12:28:01  Show Profile  Visit tkizer's Homepage  Reply with Quote
I do not understand your last reply.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

pdset
Constraint Violating Yak Guru

300 Posts

Posted - 10/13/2013 :  17:22:59  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 10/14/2013 :  11:40:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000