March 1, 2016 at 12:41 am
Hi Team,
We have received one request and asking us to provide the Mdf file growth details of one year. IS there any way to capture this information.
Please advise.
March 1, 2016 at 10:20 am
To get that information exactly, you'd need to have something set up to capture it. Having said that, you can get somewhat close with what SQL Server gives you.
Autogrowth events are recorded in the default trace, but unless the server is incredibly idle, it won't go back a year.
You can get close by looking at the backup_size column for full backups for that DB in the backupset table in msdb, assuming you don't purge old backup information.
Even that won't be exactly right, since that's for the entire database. If you have multiple data files and are looking just for the size of the primary data file, then you'll want to join the backupset table to the backupfile table and look at the file_size column for the file you're interested in.
That's still not quite perfect, since that reported size excludes any empty space in the file, so if you have a lot of empty space in the file that number could be very different than the size of the mdf in the operating system.
Still, if your mdf has been growing over the last year, then there's probably not a huge amount of free space in the file, so this could be a good enough approximation. Try it out and see if it makes sense for what you need.
Cheers!
March 8, 2016 at 12:43 pm
Thanks for you detailed explanation
March 25, 2016 at 1:21 pm
Sure there is. Here is a table to load and a Stored Proc to load it. Simply schedule this SP to run, once a week or once a month and then simply query the table.
/****** Object: StoredProcedure [dbo].[CP_Server_FileSizing] Script Date: 03/25/2016 15:19:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CP_Server_FileSizing] AS
DECLARE @dbid INT
DECLARE @MaxId INT
DECLARE @dbName SYSNAME
IF NOT EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME ='SQL_Statistics')
BEGIN
CREATE TABLE STATS.dbo.SQL_Statistics (
Svr_Name varchar( 15),
[DBName] [varchar] (75) NULL ,
[DB_DevName] [varchar] (75) NULL,
[DB_Filename] [varchar] (100) NULL ,
[DB_Size] [int] NULL ,
[Stat_Time] [datetime] NULL
)
END
/** The DB_Size returned is in 8KB Page Blocks. So Multiply
by 8 to get KB then do the rest of the math againt 1024 **/
SET @MaxId = (SELECT MAX(dbid) FROM MASTER.dbo.sysdatabases)
SET @dbid = 1
WHILE @dbid <= @MaxId
BEGIN
SET @dbName = (SELECT name FROM MASTER.dbo.sysdatabases WHERE dbid = @dbid)
IF (@dbname IS NOT NULL)
BEGIN
EXEC ('SET QUOTED_IDENTIFIER OFF
insert into SQL_Statistics SELECT "' + @@ServerName +'" AS SVR_Name,
"' + @dbname +'" AS DB_Name,
RTRIM(name) AS DB_DevName,
RTRIM(filename) AS DB_Filename,
RTRIM(size) AS DBSize, CURRENT_TIMESTAMP as Stat_Time FROM [' + @dbname + '].dbo.sysfiles')
SET @dbid = @dbid + 1
END
ELSE
SET @dbid = @dbid + 1
END
GO
------------------------------------------------------------------------------------------------------------------------------------------------
Table DDL:
/****** Object: Table [dbo].[SQL_Statistics] Script Date: 03/25/2016 15:20:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SQL_Statistics](
[Svr_Name] [varchar](15) NULL,
[DBName] [varchar](75) NULL,
[DB_DevName] [varchar](75) NULL,
[DB_Filename] [varchar](100) NULL,
[DB_Size] [int] NULL,
[Stat_Time] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
April 13, 2016 at 2:35 am
This was removed by the editor as SPAM
April 13, 2016 at 8:58 am
jacksonandrew321 (4/13/2016)
you may need to trigger the database growth event or set alert for the database.
How will that help determine the growth over a year? Why not just sample the size from sys.masterfiles on a regular basis and store it in a table?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 15, 2016 at 7:37 am
As Jacob suggests, you can look at backup history for an approximation.
Something like this will give you an average database size per month, which may be "good enough" for history. Set up a data capture going forward if you want better detail.
select
substring(a.database_name,1,30) as 'Database',
datepart(year,a.backup_start_date) as 'year',
datepart(month,a.backup_start_date) as 'month' ,
avg(cast((a.backup_size /1073741824) as decimal (9,2)))as 'Avg Gig'
FROM msdb.dbo.backupset a
join msdb.dbo.backupset b on a.server_name = b.server_name and a.database_name = b.database_name
WHERE a.type = 'D' and b.type = 'D' -- FULL Backup
and a.database_name = 'My_Database'
GROUP BY a.database_name, datepart(year,a.backup_start_date),datepart(month,a.backup_start_date)
order bya.database_name, datepart(year,a.backup_start_date) desc,datepart(month,a.backup_start_date) desc
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply