SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Database Growth Track
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

poratips
Posting Yak Master

104 Posts

Posted - 02/07/2013 :  16:11:36  Show Profile  Reply with Quote
Hi,
I would like to store the database growth (Data file size + Index Siie) into a table and send an email?
Could you please guide me how can i handle this for my all database?
I would like to create table like:

Server name, Database, IndexSize, DatafileSize, Total Size, CollectionTime.
So each time I have DB Size with Data and Index.
Which one is the best method to use IndexSize, DatafileSize or Backup size?

I am planning to create DBTrack DB and i will create Table with above columns but need to know how can i handle whole proces

Thanks,

srimami
Posting Yak Master

160 Posts

Posted - 02/08/2013 :  18:27:08  Show Profile  Reply with Quote
sp_spaceused gives the details of all the fields you were asking.
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1677 Posts

Posted - 02/14/2013 :  19:33:01  Show Profile  Reply with Quote
We have a SQL job that runs at regular intervals, gets the current usage numbers, logs them to a table (separate DBA database) and sends an email to the appropriate people if certain limits are exceeded.
HTH

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2047 Posts

Posted - 02/16/2013 :  06:49:01  Show Profile  Visit jackv's Homepage  Reply with Quote
if using sp_spaceused , use the @updateusage parameter, for updated information

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

ahmeds08
Aged Yak Warrior

India
625 Posts

Posted - 03/06/2013 :  05:02:25  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
check this one:


------------------------------Data file size----------------------------
if exists (select * from tempdb.sys.all_objects where name like '%#dbsize%')
drop table #dbsize
create table #dbsize
(Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default ('NA'), file_Size_MB decimal(30,2)default (0),Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0))
go

insert into #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB)
exec sp_msforeachdb
'use ;
select DB_NAME() AS DbName,
CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) ,
CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')),
sum(size)/128.0 AS File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB
from sys.database_files where type=0 group by type'





go

-------------------log size--------------------------------------
if exists (select * from tempdb.sys.all_objects where name like '#logsize%')
drop table #logsize
create table #logsize
(Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default (0),log_Free_Space_MB decimal(30,2)default (0))
go

insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB)
exec sp_msforeachdb
'use ;
select DB_NAME() AS DbName,
sum(size)/128.0 AS Log_File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB
from sys.database_files where type=1 group by type'


go
--------------------------------database free size
if exists (select * from tempdb.sys.all_objects where name like '%#dbfreesize%')
drop table #dbfreesize
create table #dbfreesize
(name sysname,
database_size varchar(50),
Freespace varchar(50)default (0.00))

insert into #dbfreesize(name,database_size,Freespace)
exec sp_msforeachdb
'use ;SELECT database_name = db_name()
,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'')
,''unallocated space'' = ltrim(str((
CASE
WHEN dbsize >= reservedpages
THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576
ELSE 0
END
), 15, 2) + '' MB'')
FROM (
SELECT dbsize = sum(convert(BIGINT, CASE
WHEN type = 0
THEN size
ELSE 0
END))
,logsize = sum(convert(BIGINT, CASE
WHEN type <> 0
THEN size
ELSE 0
END))
FROM sys.database_files
) AS files
,(
SELECT reservedpages = sum(a.total_pages)
,usedpages = sum(a.used_pages)
,pages = sum(CASE
WHEN it.internal_type IN (
202
,204
,211
,212
,213
,214
,215
,216
)
THEN 0
WHEN a.type <> 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0
END)
FROM sys.partitions p
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
LEFT JOIN sys.internal_tables it
ON p.object_id = it.object_id
) AS partitions'
-----------------------------------



if exists (select * from tempdb.sys.all_objects where name like '%#alldbstate%')
drop table #alldbstate
create table #alldbstate
(dbname sysname,
DBstatus varchar(55),
R_model Varchar(30))

--select * from sys.master_files

insert into #alldbstate (dbname,DBstatus,R_model)
select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,'status')),recovery_model_desc from sys.databases
--select * from #dbsize

insert into #dbsize(Dbname,dbstatus,Recovery_Model)
select dbname,dbstatus,R_model from #alldbstate where DBstatus <> 'online'

insert into #logsize(Dbname)
select dbname from #alldbstate where DBstatus <> 'online'

insert into #dbfreesize(name)
select dbname from #alldbstate where DBstatus <> 'online'

select

d.Dbname,d.dbstatus,d.Recovery_Model,
(file_size_mb + log_file_size_mb) as DBsize,
d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB,
l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace
from #dbsize d join #logsize l
on d.Dbname=l.Dbname join #dbfreesize fs
on d.Dbname=fs.name
order by Dbname


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000