Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

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? :)
0
liminal
Asked:
liminal
  • 4
  • 3
1 Solution
 
Steve WalesSenior Database AdministratorCommented:
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)
0
 
dbaSQLCommented:
SELECT
      mf.name,
      mf.physical_name,
      df.size
FROM
      sys.master_files mf INNER JOIN sys.database_files df
        ON mf.[name] = df.[name]
0
 
liminalAuthor Commented:
Thanks dbaSQL, right format, but only shows me the master DB. How do I get it to show me all DBs
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
dbaSQLCommented:
oh crap.  sorry about that.  here you go:

CREATE TABLE ##RESULTS
(
    DatabaseName sysname,
    LogicalFileName sysname,
    PhysicalFileName nvarchar(500),
    FileSize decimal (18,2),
    FreeSpace decimal (18,2)
)  
EXEC sp_msforeachdb '
      Use [?];
      Insert Into ##RESULTS (DatabaseName, LogicalFileName, PhysicalFileName, FileSize, FreeSpace)
            Select DB_NAME() AS [DatabaseName], Name,  physical_name,
            Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) Size,
            Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) -
                  Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) As FreeSpace
            From sys.database_files
      '
SELECT * FROM ##RESULTS

DROP TABLE ##RESULTS
0
 
liminalAuthor Commented:
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 :)
0
 
dbaSQLCommented:
0
 
liminalAuthor Commented:
Thanks so much
0
 
dbaSQLCommented:
No problem.  Hopefully I helped.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now