How to monitor DB growth over time
-
does anyone know of a way to easily track database growth over a period of time?
-
Can't do it retroactively. You have to setup a process to check the space once a day or so and store it somewhere. I used to do this for backups since they are a good indication of data size. And they allow trending as well.
Create a database (small) to hold this. Decide what you are concerned with, data , log, both, then schedule a job to gather the information and insert it into a table with the datetime.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
See if this script can help you, posted on, where else? SQLServerCentral
This stored proc notify the user(s) if the data/log file grew. When you execute it for the first time it will get the data/log files sizes and inserts in a table. Next time when you run the script if will compare the current size with the previous files size. This script must need sqlmail configured on the server
http://qa.sqlservercentral.com/scripts/contributions/966.asp
-
Steve I agree with you that you can't check file size or file space retroactively but backup file size is stored in the backupfile table in the msdb database isn't it?
-
have a look at NetIQ's products
they will check out index sizes, database sizes, statistics, fragmentation etc over a perfiod of time and give you some nice trend graphs.
they have some really great features in there.
MVDBA
-
I actually found another cool script, that can help with tracking db growth:
You can change the #temp table for a permanent one:
declare @dbname varchar(128)
declare @fromdate smalldatetime
select @dbname = 'YourDBNameHere'
select @fromdate = getdate()-30 ---filegrowth last 30 days (can change this accordingly)
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,
BackupSizeInMB=(backup_size/1024)/1024
from #sizeinfo
order by filedate
drop table #sizeinfo
-
Heres another tack on how to check snapshot-style daily. Put this in a sp and enjoy
SET NOCOUNT ON
DECLARE @DatabaseName varchar(128)
--
-- Create temp table to hold hard drive size information
--
CREATE TABLE #diskspace
(
Drive varchar(5) not null,
MB_free int not null
 
INSERT INTO #diskspace EXEC ('master..xp_fixeddrives')
--
-- Create temp table to hold log size information
--
CREATE TABLE #logspace
(
DBname varchar(128),
LogSize decimal(9,4),
LogUsed decimal(9,7),
Status int
 
INSERT INTO #logspace EXEC ('DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS')
--
-- Create temp table to hold database size information
--
CREATE TABLE #dataspace
(
filid smallint,
filgroup smallint,
textents int,
uextents int,
dname varchar(128),
fname varchar(128)
 
--
-- Create temp table to hold sysfiles information
--
CREATE TABLE #sysspace
(
fileid smallint,
groupid smallint,
int,
[maxsize] int,
growth int,
status int,
perf int,
[name] nvarchar(128),
[filename] nvarchar(128)
 
--
-- Get all user databases and tempdb
--
DECLARE vcursor CURSOR FOR
SELECT name FROM master..sysdatabases
WHERE name NOT IN ('Northwind','pubs','distribution','master','model','msdb')
OPEN vcursor
FETCH NEXT FROM vcursor INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #dataspace EXEC ('USE ' + @DatabaseName + ' DBCC SHOWFILESTATS WITH NO_INFOMSGS')
INSERT INTO #sysspace EXEC ('USE ' + @DatabaseName + ' SELECT * FROM sysfiles WHERE status & 64 <> 0')
--
-- Gather up the database data and hard drive information
--
INSERT INTO SQLAdmin..dba_DiskSpace
SELECT RTRIM(CONVERT(char, getdate(), 111)),
@DatabaseName,
RTRIM(tmp1.dname),
RTRIM(tmp1.fname),
UPPER(LEFT(tmp1.fname, 1)),
(tmp1.textents / 16),
(tmp1.uextents / 16),
(tmp1.textents / 16) - (tmp1.uextents / 16),
tmp2.MB_FREE
FROM #dataspace tmp1, #diskspace tmp2
WHERE LEFT(tmp1.fname, 1) LIKE tmp2.Drive
ORDER BY tmp1.dname
--
-- Gather up the database log and hard drive information
--
INSERT INTO SQLAdmin..dba_DiskSpace
SELECT RTRIM(CONVERT(char, getdate(), 111)),
@DatabaseName,
RTRIM(sys.name),
RTRIM(sys.filename),
UPPER(LEFT(sys.filename, 1)),
(sys.size / 128),
CONVERT(INT,(tmp3.LogSize * (tmp3.LogUsed / 100))),
(sys.size / 128) - CONVERT(INT,(tmp3.LogSize * (tmp3.LogUsed / 100))),
tmp2.MB_FREE
FROM #sysspace sys, #diskspace tmp2, #logspace tmp3
WHERE (LEFT(sys.filename, 1) LIKE tmp2.Drive) AND (@DatabaseName = tmp3.DBname)
ORDER BY sys.name
TRUNCATE TABLE #dataspace
TRUNCATE TABLE #sysspace
FETCH NEXT FROM vcursor INTO @DatabaseName
END
CLOSE vcursor
DEALLOCATE vcursor
--
-- Drop the temp tables
--
DROP TABLE #dataspace
DROP TABLE #diskspace
DROP TABLE #logspace
DROP TABLE #sysspace
--
-- End
GO
Good Hunting!AJ Ahrens
webmaster@kritter.net
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply