Wednesday 1 February 2012

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.co.uk

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

      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

1 comment:

  1. Hi Chris

    If this is only needed to support SQL Server 2005 or Higher then an alternative formulation that allows more flexibility could be:

    IF OBJECT_ID('tempdb..#DBFile') IS NULL
    CREATE TABLE #DBFile
    (
    [DatabaseName] VARCHAR(128),
    [VolumeLetter] CHAR(3),
    [LogicalFileName] VARCHAR(200),
    [FileID] TINYINT,
    [filegroup_id] SMALLINT,
    [PhysicalFileName] VARCHAR(1000),
    [FileGroup] VARCHAR(128),
    [Size] VARCHAR(100),
    [MaxSize] VARCHAR(100),
    [Growth] VARCHAR(100),
    [Usage] VARCHAR(100)
    )
    GO

    INSERT INTO #DBFile
    select
    DatabaseName = DB_NAME(database_id),
    VolumeLetter = LEFT([physical_name], 3),
    LogicalFileName = name,
    fileid = file_id,
    filegroup_id = data_space_id,
    PhysicalFileName = physical_name,
    FileGroup = '',
    'size' = convert(nvarchar(15), convert (bigint, size) * 8) + N' KB',
    'maxsize' = (
    case max_size
    when -1 then N'Unlimited'
    else convert(nvarchar(15), convert (bigint, max_size) * 8) + N' KB'
    end),
    'growth' = (
    case is_percent_growth
    when 1 then convert(nvarchar(15), growth) + N'%'
    else convert(nvarchar(15), convert (bigint, growth) * 8) + N' KB'
    end),
    'usage' = (
    case type
    when 2 then 'log only'
    else 'data only'
    end)
    from sys.master_files

    EXEC sp_Msforeachdb 'use [?];UPDATE #DBFile SET FileGroup = FILEGROUP_NAME(filegroup_id) WHERE DB_NAME() = DatabaseName'

    Select * from #DBFile

    IF OBJECT_ID('tempdb..#DBFile') IS NOT NULL
    DROP TABLE #DBFile

    This will grab almost all information from sys.master_files. In order to get the FileGroup name then it is necessary to Cursor across each database. This type of script is much easier to extend than having only the output of sp_helpfile.

    ReplyDelete