January 5, 2010 at 2:33 am
Hi everyone,
can u please help with a script that does following;
I need a resultset that shows me the logical files for each database and their sizes.
Example; if i have a db names IP_GK and it has two datafiles and one logfile.. i would like a resultset that shows it like this:
database, logicalname, filetype, path, filename, size
IP_GK , IP_GKData1, Datatype, f:\data\, sale1.mdf, 1024kb
IP_GK , IP_GKData2, Datatype, f:\data\, sale1.ndf, 2048kb
IP_GK , IP_GKLog, Logtype, f:\data\, salelog.mdf, 512kb
can anyone please help me with a script, that can generate this resultset.
I want it so i can schedule it daily, and put it in a table.
Monthly i will put the data in excel, and make an graph... so i can see the size growth of each file.
Thanks u guys.
Regards Arash Soleymani from Denmark Copenhagen.
January 5, 2010 at 7:33 am
this query returns the values you require
SELECT
A.NAME as 'Database',
b.name as 'logical_name',
b.type_desc as 'datatype',
b.physical_name as 'path',
SIZE *8/1024 AS 'FILE SIZE IN MB'
from master.sys.databases a
INNER JOIN MASTER.sys.master_files b ON A.database_id = b.database_id
WHERE A.database_id > 4 and b.type_desc = 'rows'
ORDER BY A.NAME
you could create a table up front with matching column definitions to the select query and then add the select into an insert statement. Adding a column with a default value of getdate() may be useful to you if you are collecting historical data.
I have added a where clause as I am presuming you don't require system databases and growth in log files is not really relevant to amount of data in the database.
---------------------------------------------------------------------
January 5, 2010 at 7:37 am
WOW!! this is exactly 100% what i was looking for.
Thank you so much SSCrazy!!...
January 5, 2010 at 7:50 am
you're welcome
---------------------------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply