February 19, 2010 at 5:20 pm
Comments posted to this topic are about the item Track database growth
February 22, 2010 at 2:41 pm
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
February 22, 2010 at 3:29 pm
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.
February 22, 2010 at 3:35 pm
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.
February 22, 2010 at 3:42 pm
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.
February 22, 2010 at 3:47 pm
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))
February 23, 2010 at 9:15 am
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.
March 10, 2010 at 6:28 am
Hi, great script, could you send the SSIS script.
March 12, 2010 at 6:21 am
Nice job.
April 4, 2010 at 4:05 am
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
April 4, 2010 at 5:21 pm
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
July 19, 2011 at 12:34 am
Thanks, it works fine
July 22, 2011 at 9:51 am
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.
August 10, 2011 at 2:18 am
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
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply