USE master
GO
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_filegroup_info'), 'IsProcedure') = 1 
BEGIN 
    DROP PROCEDURE dbo.sp_filegroup_info 
    IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_filegroup_info'), 'IsProcedure') = 1 
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_filegroup_info >>>' 
    ELSE 
        PRINT '<<< DROPPED PROCEDURE dbo.sp_filegroup_info >>>' 
END 
go 
CREATE PROCEDURE sp_filegroup_info
--WITH ENCRYPTION 
AS 
/********************************************************************************/
/*      Created By :    Umachandar Jayachandran (UC)                            */
/*      Created On :    16 November 2000                                        */
/*      Description:    This stored procedure can be used to obtain information */
/*                      about the data filegroup & transaction log size / usage.*/
/********************************************************************************/
/*      Resources  :    https://umachandar.com/resources.htm                 */
/********************************************************************************/
CREATE TABLE #FileDetails ( 
        FileId int , FileGroupId int , TotalExtents int , UsedExtents int , 
        "Name" nvarchar( 128 )  , "FileName" nvarchar( 500 ) , 
        TotalSize AS ( ( TotalExtents * 64.0 ) / 1024 ) , 
        UsedSize AS ( ( UsedExtents * 64.0 ) / 1024 ) 
)
CREATE TABLE #LogDetails (
        DatabaseName nvarchar( 128 ) , FileSize float , 
        "%Usage" float , Status int
)

-- Get data file info: 
INSERT INTO #FileDetails ( 
        FileId , FileGroupId , TotalExtents , UsedExtents , "Name" , "Filename" 
) 
EXECUTE( 'dbcc showfilestats with tableresults' ) 

-- Get log files info:
INSERT INTO #LogDetails (
        DatabaseName , FileSize ,  "%Usage" , Status 
)
EXECUTE( 'dbcc sqlperf( logspace ) with tableresults' )

SELECT FILEGROUP_NAME( FileGroupID )  AS FileGroupName , FileId , "Name" , "FileName" , 
       STR( TotalSize , 28 , 2 ) + ' MB' AS FileSize , 
       STR( UsedSize , 28 , 2 ) + ' MB' AS CurrentSize , 
       STR( ( ( UsedExtents * 1. ) / TotalExtents ) * 100 , 5 , 2 ) AS "%Usage" 
FROM #FileDetails 
UNION all 
SELECT '' , NULL , NULL , NULL , 
       STR( SUM( TotalSize ) , 28 , 2 ) + ' MB' AS FileSize , 
       STR( SUM( UsedSize ) , 28 , 2 ) + ' MB' AS CurrentSize , 
       STR( ( ( SUM( UsedExtents ) * 1. ) / SUM( TotalExtents ) ) * 100 , 5, 2 ) AS "%Usage" 
FROM #FileDetails
UNION ALL
SELECT '' , NULL , NULL , NULL ,
       STR( FileSize , 28 , 2 ) + ' MB' AS FileSize ,
       NULL ,
       STR( "%Usage" , 5 , 2 ) AS "%Usage"
FROM #LogDetails
WHERE DatabaseName = DB_NAME()
ORDER BY FileGroupName , FileId 
go 
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_filegroup_info'), 'IsProcedure') = 1 
BEGIN 
     GRANT EXECUTE ON dbo.sp_filegroup_info To Public 
     PRINT '<<< CREATED PROCEDURE dbo.sp_filegroup_info >>>' 
END 
ELSE 
    PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_filegroup_info >>>' 
go

This page was last updated on May 01, 2006 04:28 PM.