Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

A Script A Day - Day 1 - Database File To Volume Mapping

Today is the 1st February 2012 and as promised here is the first of script in my "A Script A Day" series.  There will be 29 scripts (yes this year is a leap year:) which I will also roll-up into a single document and make available to you some time in March.


I have used the below script many times and used as a starting point for other more specific scenarios like getting information about new servers / databases, auditing and resource allocation.  The script maps database files to logical volumes and includes some additional file information for good measure.


/*
      ----------------------------------------------------------------- 
      Summary:                Server volume to database file mapping
      SQL Server Versions:    2005 onwards
      Written by:             Chris McGowan
      -----------------------------------------------------------------

      For more SQL resources, check out SQLServer365.blogspot.com

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

      You may alter this code for your own purposes.
      You may republish altered code as long as you give due credit.
      You must obtain prior permission before blogging this code.
 
      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
     
      -----------------------------------------------------------------
*/
IF OBJECT_ID('tempdb..#DBFile') IS NULL
    CREATE TABLE #DBFile
        (
          [LogicalName] VARCHAR(200),
          [FileID] TINYINT,
          [FileName] VARCHAR(1000),
          [FileGroup] VARCHAR(100),
          [Size] VARCHAR(100),
          [MaxSize] VARCHAR(100),
          [Growth] VARCHAR(100),
          [Usage] VARCHAR(100)
        )
GO
IF OBJECT_ID('tempdb..#DBFile2') IS NULL
    CREATE TABLE #DBFile2
        (
          [VolumeLetter] CHAR(3),
          [LogicalFileName] VARCHAR(200),
          [FileID] TINYINT,
          [PhysicalFileName] VARCHAR(1000),
          [FileGroup] VARCHAR(100),
          [Size] VARCHAR(100),
          [MaxSize] VARCHAR(100),
          [Growth] VARCHAR(100),
          [Usage] VARCHAR(100)
        )
GO      
EXEC sp_Msforeachdb 'use [?];INSERT INTO #DBFile EXEC sp_helpfile'
GO
INSERT  INTO #DBFile2
        SELECT  LEFT([FileName], 3),
                [LogicalName],
                [FileID],
                [FileName],
                [FileGroup],
                [Size],
                [MaxSize],
                [Growth],
                [Usage]
        FROM    #DBFile
GO
SELECT  [VolumeLetter],
        [LogicalFileName],
        [PhysicalFileName],
        [FileID],
        [FileGroup],
        [Size],
        [MaxSize],
        [Growth],
        [Usage]
FROM    #DBFile2
ORDER BY [VolumeLetter] ASC
GO
IF OBJECT_ID('tempdb..#DBFile') IS NOT NULL
    DROP TABLE #DBFile
IF OBJECT_ID('tempdb..#DBFile2') IS NOT NULL
    DROP TABLE #DBFile2

Enjoy

Chris

Comments

Leave a comment on the original post [sqlserver365.blogspot.com, opens in a new window]

Loading comments...