Track database growth

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Irwan Tjanterik » Track database growth

15 posts, Page 1 of 2

1 2 »»

Track database growth

Rate Topic

Display Mode

Topic Options

Author

Message

Irwan Tjanterik

Posted Friday, February 19, 2010 5:20 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 5:28 PM
Points: 64, Visits: 282

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

Post #869519

Mike Tutor

Posted Monday, February 22, 2010 2:41 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, March 22, 2013 1:30 PM
Points: 298, Visits: 87

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

Post #870756

Mike Tutor

Posted Monday, February 22, 2010 3:29 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, March 22, 2013 1:30 PM
Points: 298, Visits: 87

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.

Post #870782

Irwan Tjanterik

Posted Monday, February 22, 2010 3:35 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 5:28 PM
Points: 64, Visits: 282

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.

Post #870789

Mike Tutor

Posted Monday, February 22, 2010 3:42 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, March 22, 2013 1:30 PM
Points: 298, Visits: 87

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.

Post #870795

Irwan Tjanterik

Posted Monday, February 22, 2010 3:47 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 5:28 PM
Points: 64, Visits: 282

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

Post #870797

wmt

Posted Tuesday, February 23, 2010 8:27 AM

Valued Member

Group: General Forum Members
Last Login: Yesterday @ 7:17 AM
Points: 63, Visits: 698

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.

Post #871172

Mike Tutor

Posted Tuesday, February 23, 2010 9:15 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, March 22, 2013 1:30 PM
Points: 298, Visits: 87

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.

Post #871227