venki83k (1/8/2013)
HiI am executing this against database i got above error .
Basically using this to track db growth of all database on a server.colud you please suggest anybody on this.
insert all_dbs_file_size(dbs_name,rec_model,dbf_size_in_MB,log_size_in_mb)
select
d.name
,d.recovery_model_desc
,convert(decimal(18,2),(sum(size)*8)/1024.0) as dbf_size_in_mb
,(select (size*8)/1024.0 from sys.sysaltfiles where dbid=saf.dbid and groupid=0) as log_size_in_mb
from sys.sysaltfiles saf
join sys.databases d on saf.dbid=d.database_id
where groupid>0
group by dbid,d.name,d.compatibility_level,d.recovery_model_desc
Regards,
Sreen.
Don't hijack threads of other posters, always create a new thread for your request
Anyways, here is the solution for your problem
INSERTall_dbs_file_size( dbs_name, rec_model, dbf_size_in_MB, log_size_in_mb )
SELECTD.name, D.recovery_model_desc,
CONVERT(DECIMAL(18,2), SUM( CASE WHEN S.groupid = 1 THEN S.size ELSE 0 END ) * 8 / 1024.00 ) AS dbf_size_in_mb,
CONVERT(DECIMAL(18,2), SUM( CASE WHEN S.groupid = 0 THEN S.size ELSE 0 END ) * 8 / 1024.00 ) AS log_size_in_mb
FROMsys.sysaltfiles AS S
INNER JOIN sys.databases AS D ON S.dbid = D.database_id
GROUP BY D.name, D.recovery_model_desc
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/