view size for database-files, and export it to table.

  • 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.

  • 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.

    ---------------------------------------------------------------------

  • WOW!! this is exactly 100% what i was looking for.

    Thank you so much SSCrazy!!...

  • 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