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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy