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 «««1234»»

Tracking and Reporting Database Growth Expand / Collapse
Author
Message
Posted Tuesday, June 2, 2009 11:44 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 6, 2010 10:17 AM
Points: 511, 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
Post #727623
Posted Tuesday, June 2, 2009 12:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 13, 2010 11:01 AM
Points: 4, 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 :)

Post #727640
Posted Tuesday, June 2, 2009 5:51 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 4:49 PM
Points: 389, Visits: 154
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
Post #727816
Posted Tuesday, June 2, 2009 11:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 6, 2013 4:37 AM
Points: 33, Visits: 94
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



Post #727877
Posted Wednesday, June 3, 2009 3:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:48 AM
Points: 2,128, Visits: 1,488
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.
Post #728011
Posted Wednesday, June 3, 2009 10:46 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:42 AM
Points: 89, 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
Post #728379
Posted Thursday, June 4, 2009 6:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 10:05 AM
Points: 46, Visits: 72
Thanks. It's working now...


Post #728865
Posted Thursday, June 4, 2009 7:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 27, 2014 10:43 AM
Points: 6, Visits: 150
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
Post #728961
Posted Wednesday, July 8, 2009 4:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 23, 2012 8:33 AM
Points: 4, 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.
Post #749134
Posted Thursday, August 20, 2009 8:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 8, 2013 6:57 PM
Points: 4, Visits: 78
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
Post #774247
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse