Track database growth

, 2014-09-23 (first published: )

Hi,

I think it is time for me to start contributing script which I have written and hopefully you may find this script useful to keep track your database growth and to be aware of when you are going to run out of space.

Basically the script is used for the following:

- Keeping Track of your Database file sizes (MDF,NDF or LDF) .

- Calculate the size of the file when it grows, depending on the setting (% or MB growth), so you will know if you are running out of drive space, so preventable steps can be taken beforehand.

- Can be used for capacity planning based on the used size not the file size, e.g. your database may have 100GB mdf size, but only 40% is currently used.

- Gives you information of current empty percentage,as I always try to ensure we have 20% of free space and increase it during off-peak hours when it is needed.

The script is tested on SQL 2000, 2005 & 2008 & it will provide the following column:

- DBName : Database Name

- FileLogicalName : File Logical Name

- Filename : Database Filename

- FileMBSize : Current File Size in MB

- FileGrowth : Types of the growth, e.g. increase by 5% or 50MB

- FileMBGrowth : Size of the file when after it grows based on the Filegrowth type

- DriveName : DriveName Location

- DriveMBEmpty : Freespace size of your current drive in MB

- FileMBused : Current size of the file being used, you may have 100GB where you only used 80GB

- FileMBEmpty : Unused space size of the file

- FilePercentEmpty : Unused space size of the file in percentage

-- Author: Irwan Tjanterik
-- Date: 18 Feb 2010

SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @dbname VARCHAR(200),
    @sql VARCHAR(8000)
    
SET @sql = ''
SET @dbname = ''
    

--DROP TABLE #TMP_ServerDrive
--DROP TABLE #TMP_LogSpace
--DROP TABLE #TMP_DBFileInfo
--DROP TABLE #TMP_DataSpace
--DROP TABLE #TMP_DB


CREATE TABLE #TMP_ServerDrive(
    [DriveName] VARCHAR(5) PRIMARY KEY,
    [FreeDriveSpace] BIGINT)

INSERT INTO #TMP_ServerDrive
EXEC master..xp_fixeddrives


CREATE TABLE #TMP_LogSpace ( 
    [DBName] VARCHAR(200) NOT NULL PRIMARY KEY,
    [LogSize] MONEY NOT NULL,
    [LogPercentUsed] MONEY NOT NULL,
    [LogStatus] INT NOT NULL) 

SELECT @sql = 'DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS' 

INSERT INTO #TMP_LogSpace
EXEC(@sql)

CREATE TABLE #TMP_DBFileInfo (
    [DBName] VARCHAR(200),
    [FileLogicalName] VARCHAR(200),
    [FileID] INT NOT NULL,
    [Filename] VARCHAR(250) NOT NULL,
    [Filegroup] VARCHAR(100) NOT NULL,
    [FileCurrentSize] BIGINT NOT NULL,
    [FileMaxSize] VARCHAR(50) NOT NULL,
    [FileGrowth] VARCHAR(50) NOT NULL,
    [FileUsage] VARCHAR(50) NOT NULL,
    [FileGrowthSize] BIGINT NOT NULL)
    
CREATE TABLE #TMP_DB ( 
    [DBName] VARCHAR(200) PRIMARY KEY
) 
    
INSERT INTO #TMP_DB 
SELECT DBName = LTRIM(RTRIM(name))
FROM master.dbo.sysdatabases 
WHERE category IN ('0', '1','16')
    AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE' 
ORDER BY name 


CREATE TABLE #TMP_DataSpace ( 
    [DBName] VARCHAR(200) NULL,
    [Fileid] INT NOT NULL,
    [FileGroup] INT NOT NULL,
    [TotalExtents] MONEY NOT NULL,
    [UsedExtents] MONEY NOT NULL,
    [FileLogicalName] sysname NOT NULL,
    [Filename] VARCHAR(1000) NOT NULL
) 

SELECT @dbname = MIN(dbname) FROM #TMP_DB 

WHILE @dbname IS NOT NULL 
BEGIN 
 
    SET @sql = 'USE ' + @dbname + '
        INSERT INTO #TMP_DBFileInfo (
            [DBName],
            [FileLogicalName],
            [FileID],
            [Filename],
            [Filegroup],
            [FileCurrentSize],
            [FileMaxSize],
            [FileGrowth],
            [FileUsage],
            [FileGrowthSize])
        SELECT DBName = ''' + @dbname + ''',
            FileLogicalName = SF.name, 
            FileID = SF.fileid, 
            Filename = SF.filename, 
            Filegroup = ISNULL(filegroup_name(SF.groupid),''''), 
            FileCurrentSize = (SF.size * 8)/1024, 
            FileMaxSize =    CASE SF.maxsize WHEN -1 THEN N''Unlimited'' 
                            ELSE CONVERT(VARCHAR(15), (CAST(SF.maxsize AS BIGINT) * 8)/1024) + N'' MB'' END, 
            FileGrowth = (case SF.status & 0x100000 when 0x100000 then 
                            convert(varchar(3), SF.growth) + N'' %'' 
                        else 
                            convert(varchar(15), ((CAST(SF.growth AS BIGINT) * 8)/1024)) + N'' MB'' end), 
            FileUsage = (case WHEN SF.status & 0x40 = 0x40 then ''Log'' else ''Data'' end),
            FileGrowthSize = CASE SF.status & 0x100000 WHEN 0x100000 THEN
                                ((((CAST(SF.size AS BIGINT) * 8)/1024)* SF.growth)/100) + ((CAST(SF.size AS BIGINT) * 8)/1024)
                            ELSE
                                ((CAST(SF.size AS BIGINT) * 8)/1024) + ((CAST(SF.growth AS BIGINT) * 8)/1024)
                            END
        FROM sysfiles SF
        ORDER BY SF.fileid' 
     
    EXEC(@sql) 

    SET @sql = 'USE ' + @dbname + '
                DBCC SHOWFILESTATS WITH NO_INFOMSGS'
                
    INSERT INTO #TMP_DataSpace (
        [Fileid],
        [FileGroup],
        [TotalExtents],
        [UsedExtents],
        [FileLogicalName],
        [Filename])
    EXEC (@sql)
    
    UPDATE #TMP_DataSpace
    SET [DBName] = @dbname
    WHERE ISNULL([DBName],'') = ''
     
    SELECT @dbname = MIN(dbname) FROM #TMP_DB WHERE dbname > @dbname 
 
END 


SELECT 'DBName' = DFI.DBName,
    'FileLogicalName' = DFI.FileLogicalName,
    'Filename' = DFI.[Filename],
    'FileMBSize' = DFI.FileCurrentSize,
    'FileGrowth' = DFI.FileGrowth,
    'FileMBGrowth' = DFI.FileGrowthSize,
    'DriveName' = SD.DriveName,
    'DriveMBEmpty' = SD.FreeDriveSpace,
    'FileMBUsed' = CAST(ISNULL(((DSP.UsedExtents * 64.00) / 1024), LSP.LogSize *(LSP.LogPercentUsed/100)) AS BIGINT),
    'FileMBEmpty' = DFI.FileCurrentSize - CAST(ISNULL(((DSP.UsedExtents * 64.00) / 1024), LSP.LogSize *(LSP.LogPercentUsed/100)) AS BIGINT),
    'FilePercentEmpty' = (CAST((DFI.FileCurrentSize - CAST(ISNULL(((DSP.UsedExtents * 64.00) / 1024), LSP.LogSize *(LSP.LogPercentUsed/100)) AS BIGINT)) AS MONEY) / CAST(CASE WHEN ISNULL(DFI.FileCurrentSize,0) = 0 THEN 1 ELSE DFI.FileCurrentSize END AS MONEY)) * 100
FROM #TMP_DBFileInfo DFI
LEFT OUTER JOIN #TMP_ServerDrive SD
    ON LEFT(LTRIM(RTRIM(DFI.[FileName])),1) = LTRIM(RTRIM(SD.DriveName))
LEFT OUTER JOIN #TMP_DataSpace DSP
    ON LTRIM(RTRIM(DSP.[Filename])) = LTRIM(RTRIM(DFI.[Filename]))
LEFT OUTER JOIN #TMP_LogSpace LSP
    ON LtRIM(RTRIM(LSP.DBName)) = LTRIM(RTRIM(DFI.DBName))

Rate

4.26 (19)

Share

Share

Rate

4.26 (19)

Related content