Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Determine database grow per month Expand / Collapse
Author
Message
Posted Friday, March 8, 2013 2:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 12:32 PM
Points: 150, Visits: 808
Is it possible to determinate how much disk space an specific database grow per month?

in sql server 2008

Any one advice me,,






Thanks
Jerry
Post #1428469
Posted Friday, March 8, 2013 3:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 5,143, Visits: 4,948
If you haven't put in custom auditing the only way would be to query msdb.dbo.backupset and compare the backup sizes, but it might not be a true representation depending on how much white space is in the database.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1428480
Posted Friday, March 8, 2013 3:16 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 1,879, Visits: 3,011
Hi Jerry,
You might be able to get a rough idea from backup history. Definitely only a guide though!

select server_name, database_name, backup_start_date, backup_size
from msdb.dbo.backupset
where type = 'D'
and database_name = 'dbname'
order by backup_start_date desc

For more accurate measure, you'll need to collect size information yourself on a regular basis.
I believe there's tools out there that do that kind of thing, or you can roll your own.
Post #1428483
Posted Friday, March 8, 2013 3:17 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 1,879, Visits: 3,011
Or, what Anthony said
Post #1428485
Posted Friday, March 8, 2013 3:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 5,143, Visits: 4,948
CREATE TABLE [DatabaseFileUsage](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[DatabaseName] [sysname] NOT NULL,
[FileID] [int] NULL,
[FileSizeMB] [decimal](18, 2) NULL,
[SpaceUsedMB] [decimal](18, 2) NULL,
[FreeSpaceMB] [decimal](18, 2) NULL,
[LogicalName] [sysname] NOT NULL,
[FileLocation] [sysname] NOT NULL,
[DateCollected] [date] NULL,
CONSTRAINT [PK_DatabaseFileUsage] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
)
GO
CREATE PROCEDURE [InsertDatabaseFileUsage]
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = REPLACE(
CAST(
(
SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +
'INSERT INTO [#####].[dbo].[DatabaseFileUsage] (DatabaseName, FileID, FileSizeMB, SpaceUsedMB, FreeSpaceMB, LogicalName, FileLocation)' + CHAR(13) + CHAR(10) +
'SELECT ' + CHAR(13) + CHAR(10) +
'DatabaseName = DB_NAME(), ' + CHAR(13) + CHAR(10) +
'a.FILEID, ' + CHAR(13) + CHAR(10) +
'[FILE_SIZE_MB] = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[SPACE_USED_MB] = CONVERT(DECIMAL(12, 2), ROUND(fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +') / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'[FREE_SPACE_MB] = CONVERT(DECIMAL(12, 2), ROUND((a.size - fileproperty(a.NAME, ' + CHAR(39) + 'SpaceUsed' + CHAR(39) +')) / 128.000, 2)), ' + CHAR(13) + CHAR(10) +
'a.NAME, a.FILENAME ' + CHAR(13) + CHAR(10) +
'FROM dbo.sysfiles a;' + CHAR(13) + CHAR(10)
FROM sys.databases
FOR XML PATH('')
)
AS NVARCHAR(MAX)
),
'&#x 0D;',CHAR(13) + CHAR(10) -- REMOVE THE SPACE ON THIS LINE BETWEEN the x and the 0
)
--SELECT @SQL
EXECUTE sp_executesql @SQL
END

This is my custom routine.

Create the table in a central database
Change ##### to the name of the central database
Create the procedure, ensure you remove the space on the line with the comment (have to put the space in as the string is a reserved XML phrase and gets removed in the forums)
Schedule a job to run the proc nightly and then you can do growth trends.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1428488
Posted Friday, March 8, 2013 3:38 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
solomon.jernas (3/8/2013)
Is it possible to determinate how much disk space an specific database grow per month?

in sql server 2008

Any one advice me,,

Thanks
Jerry


Check out this excelelnt website about creating a DBA Repository (which also includes monitoring database growth trends : http://sql-ution.com/


---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1428493
Posted Friday, March 8, 2013 9:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 12:32 PM
Points: 150, Visits: 808
I executed the procedure, but i didn't get any information...


Post #1428632
Posted Friday, March 8, 2013 11:47 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:10 PM
Points: 2,826, Visits: 8,463
I use backup history too. Quite a few threads , posts & scripts here and elsewhere.


Post #1428719
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse