April 30, 2008 at 10:57 am
Is there a way to easily locate the .mdf, .ldf and .bak file sizes for every database on an instance of SQL Server 2005? I am sure this information is stored in the system DBs but not sure how to retrieve it.
April 30, 2008 at 11:12 am
Monitor Database Growth
This code provides a way of monitoring the growth of all your databases within a single instance. The first part is the creation of a monitoring table with the initial load of current databases and sizes. The second part is the SQL that can be put in a scheduled job to automate the growth monitoring.
It is recommended that this job is run weekly or monthly, depending on how fast your databases grow. Also, the code was written for SQL 2005 but can easily be altered for SQL 2000 by changing sys.Master_Files to sysaltfiles and sys.databases to sysdatabases. Make sure to change your column names appropriately if you make this alteration!
--PART 1
If exists (Select name from sys.objects where name = 'DBGrowthRate' and Type = 'U')
Drop Table dbo.DBGrowthRate
Create Table dbo.DBGrowthRate (DBGrowthID int identity(1,1), DBName varchar(100), DBID int,
NumPages int, OrigSize decimal(10,2), CurSize decimal(10,2), GrowthAmt varchar(100),
MetricDate datetime)
Select sd.name as DBName, mf.name as FileName, mf.database_id, file_id, size
into #TempDBSize
from sys.databases sd
join sys.master_files mf
on sd.database_ID = mf.database_ID
Order by mf.database_id, sd.name
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
'0.00 MB' as GrowthAmt, GetDate() as MetricDate
from #TempDBSize tds
where tds.database_ID not in (Select Distinct DBID from DBGrowthRate
where DBName = tds.database_ID)
Group by tds.database_ID, tds.DBName)
Drop table #TempDBSize
Select *
from DBGrowthRate
--Above creates initial table and checks initial data
--PART 2
--Below is the code run weekly to check the growth.
Select sd.name as DBName, mf.name as FileName, mf.database_id, file_id, size
into #TempDBSize2
from sys.databases sd
join sys.master_files mf
on sd.database_ID = mf.database_ID
Order by mf.database_id, sd.name
If Exists (Select Distinct DBName from #TempDBSize2
where DBName in (Select Distinct DBName from DBGrowthRate))
and Convert(varchar(10),GetDate(),101) > (Select Distinct Convert(varchar(10),Max(MetricDate),101) as MetricDate
from DBGrowthRate)
Begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
dgr.CurSize as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
Convert(varchar(100),(Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024))
- dgr.CurSize)) + ' MB' as GrowthAmt, GetDate() as MetricDate
from #TempDBSize2 tds
join DBGrowthRate dgr
on tds.database_ID = dgr.DBID
Where DBGrowthID = (Select Distinct Max(DBGrowthID) from DBGrowthRate
where DBID = dgr.DBID)
Group by tds.database_ID, tds.DBName, dgr.CurSize)
End
Else
IF Not Exists (Select Distinct DBName from #TempDBSize2
where DBName in (Select Distinct DBName from DBGrowthRate))
Begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
'0.00 MB' as GrowthAmt, GetDate() as MetricDate
from #TempDBSize2 tds
where tds.database_ID not in (Select Distinct DBID from DBGrowthRate
where DBName = tds.database_ID)
Group by tds.database_ID, tds.DBName)
End
--Select *
--from DBGrowthRate
----Verifies values were entered
Drop table #TempDBSize2
April 30, 2008 at 12:13 pm
Thank you, that is a solution for DB size and to track growth. i will be using this for overall DB monitoring.
I am also interested in managing hard drive resources and would like to track the actual file sizes for .mdf, .ldf and .bak files. Do you have a suggestion on where these file details are kept in the system databases?
April 30, 2008 at 12:28 pm
This query will return ALL databases and their respective file location
use master
select name, filename, dbid, convert(char(12),crdate)
from dbo.sysdatabases
where sid <> 0x01
order by name
April 30, 2008 at 12:37 pm
For .mdf, .ndf and .ldf files there's the sys.master_files catalog view; the size column is in 8KB pages.
For .bak there's the msdb..backupfile backup history table. That would show where the .bak files were written to but you'd still have to do something to verify the files were still there.
April 30, 2008 at 3:12 pm
I was able to pull the .mdf, .ldf and .bak information out of the tables specified. Thanks to all for the quick answers. I should be able to watch database growth and file size changes by server on a periodic basis very quickly now.
Thanks again 🙂
April 12, 2010 at 11:19 am
Alif,
I just wanted to say thanks, this code is great!
April 13, 2010 at 4:13 am
Hi Friends,
The above codes are awesome and really helped me out in maintaining the DB growth.
If you want the individual file growth than the below mentioned can be helped you out.
This stored Procedure helps you to calculate the specific size of the .mdf,.ndf,.ldf files
on running this SP on daily,weekly,monthly basis you can easily track the database growth.
The disadvantage is that you have to maintain the data on regular basis for tracking the database
files growth.but still its a wonderfull code which helps you a lot.
create proc sp_filesize
as
set nocount on
-- Database Size
print '*** Database Size ***'
create table #db_size (dbno int identity,
dbdate datetime,
dbname nvarchar(50),
datasize float,
logsize float(53),
indexsize float(53),
actual float)
declare @datasize float
declare @logsize float
declare @indexsize float
declare @usedspace float
declare @dbsize float
declare @datacmd nvarchar (100)
declare @logcmd nvarchar (100)
declare @indexcmd nvarchar (100)
declare @usedspacecmd nvarchar (1000)
declare @dbsizecmd nvarchar (1000)
declare @actual nvarchar (100)
declare @dbname char (25)
declare c1 cursor for select name from master..sysdatabases
open c1
fetch c1 into @dbname
while @@fetch_status = 0
begin
set @datacmd='select @datasize=size from ['+@dbname+'].dbo.sysfiles where fileid=1'
exec sp_executesql @datacmd, N'@datasize float output', @datasize output
set @datasize=round(@datasize/128,0)
set @logcmd='select @logsize=size from ['+@dbname+'].dbo.sysfiles where fileid=2'
exec sp_executesql @logcmd, N'@logsize float output', @logsize output
set @logsize=round(@logsize/128,0)
set @indexcmd='select @indexsize=size from ['+@dbname+'].dbo.sysfiles where fileid=3'
exec sp_executesql @indexcmd, N'@indexsize float output', @indexsize output
set @indexsize=round(@indexsize/128,0)
insert into #db_size (dbdate,dbname,datasize,logsize,indexsize,actual)
values (getdate(),@dbname,@datasize,@logsize,@indexsize,@usedspace)
fetch next from c1 into @dbname
end
select
cast(dbno as varchar(5)) "DBno",
cast(dbdate as varchar(20)) "DBDate",
cast(dbname as varchar(20)) "DBName",
cast(datasize as varchar(10)) "datasize",
cast(logsize as varchar(10)) "logsize",
cast(indexsize as varchar(10)) "indexsize"
from #db_size
drop table #db_size
close c1
deallocate c1
Regards
Maneesh
January 8, 2014 at 1:32 pm
can anybody help to setup a job 'part 2' on sql agent? I tried to set up a job but failed, would you plz kindly help me to set up a job to monitor the database growth?
April 30, 2015 at 11:45 am
Wjhy are you passing "0.0 MB" as Griowthrate????
IF Not Exists (Select Distinct DBName from #TempDBSize2
where DBName in (Select Distinct DBName from DBGrowthRate))
Begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
'0.00 MB' as GrowthAmt, GetDate() as MetricDate
from #TempDBSize2 tds
where tds.database_ID not in (Select Distinct DBID from DBGrowthRate
where DBName = tds.database_ID)
Group by tds.database_ID, tds.DBName)
Thanks.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply