Solved

SQL Query database location and size

Posted on 2014-02-06
8
317 Views
Last Modified: 2014-02-10
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
Comment
Question by:liminal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 39840871
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
 
LVL 17

Expert Comment

by:dbaSQL
ID: 39840880
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
 

Author Comment

by:liminal
ID: 39840926
Thanks dbaSQL, right format, but only shows me the master DB. How do I get it to show me all DBs
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Accepted Solution

by:
dbaSQL earned 500 total points
ID: 39841013
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
 

Author Comment

by:liminal
ID: 39841035
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
 
LVL 17

Expert Comment

by:dbaSQL
ID: 39841067
0
 

Author Closing Comment

by:liminal
ID: 39848707
Thanks so much
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 39849065
No problem.  Hopefully I helped.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question