July 7, 2005 at 11:56 am
Hi,
I have a requirement where I need to find database growth from january first to june .I want to find the db growth on test server where I don't have all data that is from jan to june.Is there any way I can find the database growth .
Any help is appreciated
Thanks,
Sree
Thanks,
SR
July 7, 2005 at 1:45 pm
Here is a script that I got from this site that works well for me (apologies to the original author, but I don't remember so I can't give credit where it's due). It works nicely and will return mdf, ldf, total size, and size of the backup file.
Change the date parameter and it should return as long as you haven't deleted history data:
/***********************************************************Check growth of .LDF and .MDF from backuphistory.
Lines returned depends on the frequency of full backups
Parameters: database name
fromdate (date from which info is requiered in
smalldatetime)
Results best viewed in grid
***********************************************************/
--- Change these vars for your database
declare @dbname varchar(128)
declare @fromdate smalldatetime
select @dbname = 'YourDbName'
select @fromdate = getdate()-30 ---filegrowth last 30 days
create table #sizeinfo
(
filedate datetime null,
dbname nvarchar(128) null,
Dsize numeric (20,0) null,
Lsize numeric (20,0) null,
backup_set_id int null,
backup_size numeric (20,0) null
)
--- tmp pivot table to get mdf en ldf info in one line
insert #sizeinfo
select
filedate=bs.backup_finish_date,
dbname=bs.database_name,
SUM(CASE file_type WHEN 'D' THEN file_size ELSE 0 END) as Dsize,
SUM(CASE file_type WHEN 'L' THEN file_size ELSE 0 END) as Lsize,
bs.backup_set_id,
bs.backup_size
from msdb..backupset bs, msdb..backupfile bf
where bf.backup_set_id = bs.backup_set_id
and rtrim(bs.database_name) = rtrim(@dbname)
and bs.type = 'D'
and bs.backup_finish_date >= @fromdate
group by bs.backup_finish_date, bs.backup_set_id, bs.backup_size, bs.database_name
order by bs.backup_finish_date, bs.backup_set_id, bs.backup_size, bs.database_name
select
Date=filedate,
Dbname=dbname,
MDFSizeInMB=(Dsize/1024)/1024,
LDFSizeInMB=(Lsize/1024)/1024,
TotalFIleSizeInMB=((Dsize+Lsize)/1024)/1024,
BackupSizeInGB=(backup_size/1024)/1024
from #sizeinfo
order by filedate
drop table #sizeinfo
My hovercraft is full of eels.
July 8, 2005 at 9:31 am
I had to do a similar function where I had to report, via email, when a database had less than 10% of space. Maybe my script can be edited to fit your needs.
David
***************
CREATE procedure dbo.sp_DB_Information -- 2005.05.11 David J. Paskiet
as
declare @id int -- The object id of @objname.
declare @type character(2) -- The object type.
declare @pages int -- Working variable for size calc.
--declare @dbname sysname
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
declare @DataFileSize_Used decimal(15,2)
declare @log_used decimal(15,2)
declare @total_db_file_size decimal(15,2)
/**************************************************************
** These temp tables will hold the data for the calculations **
** needed for the proc. **
***************************************************************/
create table #spt_space
(
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
create table #db_information(
server_name varchar(30),
database_name varchar(30),
database_size_MB decimal(15,2),
total_DbFile_size decimal(15,2),
database_used_MB decimal(15,2),
db_used_percent decimal(15,2),
db_free_percent decimal(15,2),
logfile_size_MB decimal(15,2),
Logfile_used_MB decimal(15,2),
log_percent_free decimal(15,2),
log_percent_used decimal(15,2),
index_size_kb decimal(15,2),
warning_flag bit default 0
)
CREATE TABLE #db_file_information(
fileid integer,
theFileGroup integer,
Total_Extents integer,
Used_Extents integer,
db varchar(30),
file_Path_name varchar(200)--,
)
create table #log (
DatabaseName varchar(128),
Log_Size decimal (15,7),
LogSpace_usedPercent decimal (15,7),
Status int--,
--Space_used decimal(15,2)
)
set nocount on
begin
-- clear the table to be poplated at the end of this proc
delete from admindb.dbo.db_information where server_name = @@servername and database_name = db_name()
-- Get the database size, this is not the size used!
select @dbsize = sum(convert(dec(15),size)) from dbo.sysfiles where (status & 64 = 0)
-- get the logfile size
select @logsize = sum(convert(dec(15),size)) from dbo.sysfiles where (status & 64 <> 0)
--get the bytes per page as described above
select @bytesperpage = low from master.dbo.spt_values where number = 1 and type = 'E'
--calculate the pages per megb value
select @pagesperMB = 1048576 / @bytesperpage
-- Populate the fisrt table
insert into #db_information(server_name, database_name, database_size_MB,logfile_size_MB)
values (@@servername, db_name(),str((@dbsize + @logsize) / @pagesperMB,15,2),str(@logsize/@pagesperMB,15,2))
-- Get the size of the datafiles
insert into #db_file_information exec('DBCC showfilestats')
-- place the data into another table that can be modified.
select *,cast(((Total_Extents-Used_Extents)/(Total_extents*1.0))*100 as decimal(15,2)) as percent_free into #db_file_information_final from #db_file_information
-- Get teh actual size of the datafiles in MB and update the table
select @DataFileSize_Used = sum(Used_Extents*64.)/1024. , @total_db_file_size = sum(total_Extents*64.)/1024. from #db_file_information_final
update #db_information set database_used_mb = @DataFileSize_Used , total_DbFile_size=@total_db_file_size where server_name = @@servername and database_name = db_name()
/* Now calculate the summary data. */
-- populate #log
insert into #log exec ('dbcc sqlperf (logspace)')
select @log_Used = (log_size * (logspace_usedpercent/100)) from #log where databasename = db_name()
update #db_information
set logfile_used_mb = @log_Used,
log_percent_free = ((log_size-@log_used)/(log_size*1.))*100,
log_percent_used =logspace_usedpercent
from #log where databasename = db_name()
update #db_information
set db_used_percent= ((database_used_MB/(database_size_MB*1.))*100),
db_free_percent = ((database_size_MB-database_used_MB)/(database_size_mb*1.))*100
end
update #db_information set warning_flag = 1 where db_free_percent < 10 or log_percent_free <10
insert into admindb..db_information select * from #db_information
--select * from #db_information
/*
select * from #db_file_information_final
select * from #log
select * from #spt_space
DBCC showfilestats
*/
drop table #spt_space
drop table #db_information
drop table #db_file_information
drop table #log
drop table #db_file_information_final
return (0) -- sp_spaceused
GO
Kindest Regards,
David
** Obstacles are those frightening things that appear when we take our eyes off the goal. **
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply