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

Rate

Share

Share

Rate