locked
Looking for a query to return all .ldf and .mdf files on a give database server RRS feed

  • Question

  • I used to have a script that would display all the .ldf and .mdf files and their sizes on a give SQL server. Does anyone have one that they can share?
    Orange County District Attorney
    Monday, May 11, 2009 8:26 PM

Answers

  • Here is one that I use...

    USE MASTER 
    
    GO 
    
    
    CREATE TABLE #TMPSPACEUSED ( 
      DBNAME    VARCHAR(50), 
      FILENME   VARCHAR(50), 
      SPACEUSED FLOAT) 
    
    INSERT INTO #TMPSPACEUSED 
    EXEC( 'sp_msforeachdb''use ?; Select ''''?'''' DBName, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''') 
    
    SELECT 
             A.NAME AS DATABASENAME, 
             B.NAME AS FILENAME, 
             CASE B.TYPE  
               WHEN 0 THEN 'DATA' 
               ELSE TYPE_DESC 
             END AS FILETYPE, 
             CASE  
               WHEN (B.SIZE * 8 / 1024.0) > 1000 THEN CAST(CAST(((B.SIZE * 8 / 1024) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB' 
               ELSE CAST(CAST((B.SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB' 
             END AS FILESIZE, 
             CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2))    SPACEFREE_MB, 
             B.PHYSICAL_NAME 
    FROM     SYS.DATABASES A 
             JOIN SYS.MASTER_FILES B 
               ON A.DATABASE_ID = B.DATABASE_ID 
        
             JOIN #TMPSPACEUSED D 
               ON A.NAME = D.DBNAME 
                  AND B.NAME = D.FILENME 
    ORDER BY DATABASENAME
              
    
    DROP TABLE #TMPSPACEUSED

    Ken Simmons http://columbusga.sqlpass.org
    Monday, May 11, 2009 8:37 PM

All replies

  • Here is one that I use...

    USE MASTER 
    
    GO 
    
    
    CREATE TABLE #TMPSPACEUSED ( 
      DBNAME    VARCHAR(50), 
      FILENME   VARCHAR(50), 
      SPACEUSED FLOAT) 
    
    INSERT INTO #TMPSPACEUSED 
    EXEC( 'sp_msforeachdb''use ?; Select ''''?'''' DBName, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''') 
    
    SELECT 
             A.NAME AS DATABASENAME, 
             B.NAME AS FILENAME, 
             CASE B.TYPE  
               WHEN 0 THEN 'DATA' 
               ELSE TYPE_DESC 
             END AS FILETYPE, 
             CASE  
               WHEN (B.SIZE * 8 / 1024.0) > 1000 THEN CAST(CAST(((B.SIZE * 8 / 1024) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB' 
               ELSE CAST(CAST((B.SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB' 
             END AS FILESIZE, 
             CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2))    SPACEFREE_MB, 
             B.PHYSICAL_NAME 
    FROM     SYS.DATABASES A 
             JOIN SYS.MASTER_FILES B 
               ON A.DATABASE_ID = B.DATABASE_ID 
        
             JOIN #TMPSPACEUSED D 
               ON A.NAME = D.DBNAME 
                  AND B.NAME = D.FILENME 
    ORDER BY DATABASENAME
              
    
    DROP TABLE #TMPSPACEUSED

    Ken Simmons http://columbusga.sqlpass.org
    Monday, May 11, 2009 8:37 PM
  • Ken,

    This absolutely perfect. Thanks very, very much.
    Orange County District Attorney
    Monday, May 11, 2009 8:40 PM
  • Hello Ken 

    Why i am getting freespace_MB as a -ve value.

    This are the relevant output for tempdb database:

    Output of : select name, fileproperty(name,'spaceused') from sysfiles 

    tempdev 2320
    templog 541691
    tempdev1 3544
    tempdev2 3816

    Output of : select name,size from sys.master_files where database_id = 2

    tempdev 1024
    templog 64
    tempdev1 2560
    tempdev2 2560


    Output of : sp_helpdb
    tempdb  24318.75 MB sa 2 Apr 25 2009

    tempdev 1 PRIMARY 2547520 KB Unlimited 10% data only templog 2 NULL 10923712 KB Unlimited 10% log only tempdev1 3 PRIMARY 5715584 KB Unlimited 10% data only tempdev2 4 PRIMARY 5715584 KB Unlimited 10% data only


    Output of your script: 

    tempdb tempdev DATA 8.00 MB -13.69
    tempdb templog LOG 0.50 MB -3293.14
    tempdb tempdev1 DATA 20.00 MB -15.19
    tempdb tempdev2 DATA 20.00 MB -16.44


    And i see this article in connect.microsoft.com but i am not able to understand it.


    Thanks,
    Nimit
    Monday, May 11, 2009 9:36 PM
  • Nimit,

    Try to run DBCC UPDATEUSAGE(tempdb) and see if the numbers get any better.
    Ken Simmons http://columbusga.sqlpass.org
    Tuesday, May 12, 2009 12:10 AM