Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Track database growth Expand / Collapse
Author
Message
Posted Friday, February 19, 2010 5:20 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 10:05 PM
Points: 64, Visits: 296
Comments posted to this topic are about the item Track database growth
Post #869519
Posted Monday, February 22, 2010 2:41 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 26, 2014 7:50 AM
Points: 310, Visits: 94
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
Posted Monday, February 22, 2010 3:29 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 26, 2014 7:50 AM
Points: 310, Visits: 94
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
Posted Monday, February 22, 2010 3:35 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 10:05 PM
Points: 64, Visits: 296
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
Posted Monday, February 22, 2010 3:42 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 26, 2014 7:50 AM
Points: 310, Visits: 94
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
Posted Monday, February 22, 2010 3:47 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 10:05 PM
Points: 64, Visits: 296
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
Posted Tuesday, February 23, 2010 8:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, May 31, 2014 4:12 AM
Points: 64, Visits: 732
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
Posted Tuesday, February 23, 2010 9:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 26, 2014 7:50 AM
Points: 310, Visits: 94
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
Posted Wednesday, March 10, 2010 6:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 27, 2012 11:08 AM
Points: 16, Visits: 134
Hi, great script, could you send the SSIS script.
Post #880138
Posted Friday, March 12, 2010 6:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:28 PM
Points: 6, Visits: 88
Nice job.
Post #881694
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse