asked on

SQL Query database location and size

So I can find a query to give me the name and location;

SELECT name, physical_name AS current_file_location
FROM sys.master_files

But I want to add a column that gives me the size as well.

How do I do that, thanks? :)

Steve Wales

There is a column called size in sys.master_files that should give you that.

See: http://technet.microsoft.com/en-us/library/ms186782.aspx

To transform from 8K pages to MB or GB:

SizeInMB = (size * 8 / 1024)
SizeInGB = (size * 8 / 1024 / 1024)

dbaSQL

SELECT
      mf.name,
      mf.physical_name,
      df.size
FROM
      sys.master_files mf INNER JOIN sys.database_files df
       ON mf.[name] = df.[name]

liminal

ASKER

Thanks dbaSQL, right format, but only shows me the master DB. How do I get it to show me all DBs

ASKER CERTIFIED SOLUTION

dbaSQL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

liminal

ASKER

HAHA, was getting a syntax error, then I realised I had copied the top line as well.

Anyway, thanks for that... Worked like a charm :)

One more question, but happy to open other question if you want the points.

We are moving to IAAS very soon, but want to know how much data actually changes in any given time period so we can work out bandwidth for replication of the databases.

Do you have a query for that? Or a recommendation of how to get that info.

Thanks again, great help :)

dbaSQL

Sure. Take a look at this:
http://simplesqlserver.com/2013/09/10/monitoring-database-and-table-sizes/

liminal

ASKER

Thanks so much

dbaSQL

No problem. Hopefully I helped.