SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tracking and Reporting Database Growth


Tracking and Reporting Database Growth

Author
Message
randall.c.newcomb
randall.c.newcomb
Mr or Mrs. 500
Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)

Group: General Forum Members
Points: 529 Visits: 181
I use a similar technique on a server I work with. The primary benefit of this information is to be able to talk to business owners in a way they understand. You can get user buy-in for a data retention policy when they understand what it would cost to keep large amounts of old data online.

[begin example]
ME: Mary, we have been tracking database growth. According to this chart we project that we will run out of space sometime in mid-November. Our choices are to either add disk space or cut back on the amount of data we store.

MARY: How much will the extra disk storage cost?

ME: For $XXXX we can get enough extra storage to keep us running for another 18 months. Then we will have to add additional storage.

MARY: How about cutting back on the data stored? How much data do we store now?

ME: We have transactional and summary data back to 2006. If we only kept 6 months of transactional data it would allow use to run indefinitely with the current storage. We could archive the older data in case we need to pull it up again for some reason.
[end example]

- Randall Newcomb
mike.wade
mike.wade
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 40
Interesting Discussion so far.

My opinion is that autogrowth should be considered a contingency feature. Every time there is a growth there will likely be disk fragmentation as a result, so autogrowth should be kept to a minimum and databases sized with sufficient growth for a decent period of time. Fewer growths = less disk fragmentation.

So for me whilst i find it useful for capacity planning how big the database is over time, i tend to focus more on avoiding autogrowths and detecting them. This is a function and view I wrote which uses the default trace to report on the recent auto growths and shrinks.




USE [master]
GO

/****** Object: View [dbo].[vw_DBA_FileAutoGrowths] Script Date: 06/02/2009 19:09:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[udf_DBA_DefaultTraceFilename]() RETURNS varchar(255) AS
BEGIN

DECLARE @Filename varchar(255)

SELECT @Filename=LEFT(t.Path,LEN(t.Path)-CHARINDEX('\',REVERSE(t.Path)))+'\log.trc'
FROM sys.traces t
CROSS JOIN sys.configurations c
WHERE c.name='default trace enabled' AND c.value=1

RETURN @Filename

END

GO




CREATE VIEW [dbo].[vw_DBA_FileAutoGrowths] AS

/*
Purpose: Returns a list of auto-growths and auto shrinks that have occurred across all the databases
Author: Michael I Wade
Created: 24 December 2008
Dependencies: dbo.udf_DBA_DefaultTraceFilename
*/

SELECT t.DatabaseName,
t.StartTime,
t.EndTime,
CASE WHEN t.EventClass IN (92,94) THEN 'Data File' ELSE 'Log File' END FileType,
CASE WHEN t.EventClass IN (92,93) THEN 'Auto-Grow' ELSE 'Auto-Shrink' END ChangeType,
t.[Filename],
t.IntegerData*8.0/1024 ChangeInKB,
t.ApplicationName,
t.LoginName
FROM ::fn_trace_gettable(dbo.udf_DBA_DefaultTraceFilename(),default) t
WHERE t.EventClass IN (92,93,94,95)

GO






Calling SELECT * FROM vw_DBA_FileAutoGrowths
will tell you all the recent growths and what application / login caused it (This bit is handy for those annoying users who do stupid things like carteasean join inserts!)

Feel free to use my code, but just give me credit if you republish it Smile
Nitya
Nitya
SSC-Addicted
SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)

Group: General Forum Members
Points: 419 Visits: 162
Go to Tools->References->

Check the latest version of Microsoft ActiveX Data Objects and say OK.

Now Hit Debug->Compile VBA Project. It shouldn't give you any error.

Now upon running the function it will populate the Cells from A2 onwards

Cheers,
Nitya
wsaharem
wsaharem
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 96
Couple of points I noticed that I think would improve your solution.

1. The database table is not very normalized and included information not related to space, but related to state or mode of the DB. It might be better to create a Server table and reference this in your DBINFORMATION table. (or DBINFORMATION could be changed to DBSizes)

2. A simple query to master.sys.databases would get you a list of all the databases. (eliminating sp_msforeachdb) Since you don't have to be "in" each DB, you can run your query and only change the from statement using either a cursor or while loop.

3. The way your query is written, you are running this on a specific machine and storing it in the database on the target machine. Look into Powershell or C# or SSIS to reference a "server" table and collect information from multiple servers.

4. You might add the file type to your query.
case when FileProperty(Name,''IsLogFile'') = 1 then ''Log'' else ''Data'' end as FileType

You could then query based on the filetype versus having to parse the name if you were looking for Log versus Data files.

Hope that Helps
Bill



Lempster
Lempster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3216 Visits: 1657
mike.wade (6/2/2009)
This is a function and view I wrote which uses the default trace to report on the recent auto growths and shrinks.


Mike,

Your function is useful because it returns extra information. If anyone just wants basic information about autogrowth for a particular database, it is available via the Standard Reports that can be executed from SSMS.
es_casey
es_casey
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 167
Does anyone have code to track space used/remaining under mount points? For example, xp_fixeddrives only reports what is visible at the root, not under the mount points themselves.

esc
MatthieuQ
MatthieuQ
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 72
Thanks. It's working now...



Datamate Technology
Datamate Technology
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 182
Mike.wade,

Like randall.c.newcomb mentioned, the format of the report was primarily meant to update the non-technical staff with information they would understand in their language. You code provides a mode drilled down view of db growth info...I am fine with it if you wish to tweak code to accomodate your stuff and republish it as a new article..It will be another way for extracting db information


Cheers
panoslondon1
panoslondon1
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 25
I think this article is a copy from http://www.mssqltips.com/tip.asp?tip=1426, So lets give some credit to that.

It works Ok, but do agree with the others that Excel is not the best way to use it. Reporting services and a SQL Job will be the best way to use it, instead of relying on Excel to register values.
grubbsdw
grubbsdw
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 80
I don't see a response to your question. For this error, I added a reference (Tools --> References) to Microsoft ActiveX Data Objects 2.5 Library since I'm using Excel 2003
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search