Track database growth

  • Irwan Tjanterik

    SSC-Addicted

    Points: 470

    Comments posted to this topic are about the item Track database growth

  • Mike Tutor

    Ten Centuries

    Points: 1077

    Thanks for the great script. I created a table with a capture date and put your script (with some minor modifications) into a weekly job. This way I can keep a history of database growth and use it for charting expected growth etc.

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DBStats]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[DBStats]

    GO

    CREATE TABLE [dbo].[DBStats] (

    [DBName] [varchar] (100) NOT NULL ,

    [CaptureDT] [datetime] NOT NULL ,

    [FileLogicalName] [varchar] (100) NOT NULL ,

    [Filename] [varchar] (200) NULL ,

    [FileMBSize] [int] NULL ,

    [FileGrowth] [varchar] (20) NULL ,

    [FileMBGrowth] [int] NULL ,

    [DriveName] [varchar] (50) NULL ,

    [DriveMBEmpty] [int] NULL ,

    [FileMBUsed] [int] NULL ,

    [FileMBEmpty] [int] NULL ,

    [FilePercentEmpty] [numeric](5, 2) NULL

    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[DBStats] WITH NOCHECK ADD

    CONSTRAINT [PK_DBStats] PRIMARY KEY CLUSTERED

    (

    [DBName],

    [CaptureDT],

    [FileLogicalName]

    ) ON [PRIMARY]

    GO

  • Mike Tutor

    Ten Centuries

    Points: 1077

    One other change I made so that this works for all databses:

    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

    I added the brackets since I have databases with '.' (periods) in them.

  • Irwan Tjanterik

    SSC-Addicted

    Points: 470

    Hi

    The issue with the db name is when you have a space in the database name or '.' character.. I noticed this after I post the script, so a bit too late.

    btw I am currently using the script with SSIS to go around all my sql server instance and get information and stored it into my DBA database location. If you like I can post the SSIS script too.

    I also used this data to forecast that when I am running out of space I will get notified before it ran out of space.

  • Mike Tutor

    Ten Centuries

    Points: 1077

    The SSIS script would be sweet, expecially since I am just now (finally) moving all my databases off of 2000 to 2008 so I am new to SSIS.

    I noticed after my last post that once I put the brackets around the database name, the FileMBUsed, FileMBEmpty, and FilePercentEmpty columns do not work on the .LDF files. I haven't debugged it yet, but I'll figure it out.

  • Irwan Tjanterik

    SSC-Addicted

    Points: 470

    Try this out

    SET NOCOUNT ON

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    DECLARE @dbname VARCHAR(200),

    @sql VARCHAR(8000)

    SET @sql = ''

    SET @dbname = ''

    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'

    AND DATABASEPROPERTYEX(name, 'IsInStandBy') = 0

    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 'RunDate' = CAST(CONVERT(VARCHAR(12),GETDATE(),101) AS DATETIME),

    '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))

  • wmt

    Mr or Mrs. 500

    Points: 572

    Great script.

    For the database name problem, all you have to do is use the QUOTENAME function i.e.

    SET @sql = 'USE ' + QUOTENAME(@dbname) + 'DBCC SHOWFILESTATS WITH NO_INFOMSGS'

    works for me.

  • Mike Tutor

    Ten Centuries

    Points: 1077

    Thanks wmt,

    I had come up with another solution for that problem(put the brackets on here, remove them there, ugh), but yours is certainly more elegant.

  • DLRDBA

    SSC Rookie

    Points: 46

    Hi, great script, could you send the SSIS script.

  • peymaster-660178

    SSC Veteran

    Points: 204

    Nice job.

  • Edward Sun

    SSC Enthusiast

    Points: 150

    Irwan,

    I like your script. But one thing I need to point out is that 'master..xp_fixeddrives' doesn't work with the mounted volumes supported by sql2005 and above.

    Ed

  • Irwan Tjanterik

    SSC-Addicted

    Points: 470

    Hi Ed

    Thanks for letting me know, I guess when I wrote the script it is purely to ensure that I know when my databases are running out of space, as for mounted volumes, I won't normally put my databases in there.

    Regards

    Irwan

  • lepoto.masedi

    Grasshopper

    Points: 19

    Thanks, it works fine

  • pkrudysz

    Right there with Babe

    Points: 742

    Mike Tutor (2/22/2010)


    Thanks for the great script. I created a table with a capture date and put your script (with some minor modifications) into a weekly job. This way I can keep a history of database growth and use it for charting expected growth etc.

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DBStats]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [dbo].[DBStats]

    GO

    CREATE TABLE [dbo].[DBStats] (

    ...

    GO

    And that's what makes this useful. We need to know how much a file has grown over a period of time, and predict how much it will grow in the future.

  • Henrik Staun Poulsen

    SSCertifiable

    Points: 6376

    Hi Irwan,

    I had to do a :

    FileCurrentSize = (cast(SF.size as bigint) * 8)/1024,

    in order to avoid an integer overflow.

    Best regards,

    Henrik Staun Poulsen

    http://www.stovi.com

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply