March 8, 2013 at 2:59 am
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
March 8, 2013 at 3:13 am
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.
March 8, 2013 at 3:16 am
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.
March 8, 2013 at 3:17 am
Or, what Anthony said 🙂
March 8, 2013 at 3:22 am
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.
March 8, 2013 at 3:38 am
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[/url]
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
March 8, 2013 at 9:01 am
I executed the procedure, but i didn't get any information...
March 8, 2013 at 11:47 am
I use backup history too. Quite a few threads , posts & scripts here and elsewhere.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply