Tracking and Reporting Database Growth

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720963

    Chris Howarth (6/2/2009)


    It continues to amaze me that people don't use Integration Services (to pull data into a central repository) and Reporting Services to assist with tasks of this nature - particularly as they're both included 'in the box' and are relatively easy to use.

    I think part of the reason SSIS isn't used is that it's a programming environment, and it's not familiar for many DBAs. Lots of people have sysadmin backgrounds and the idea of programming, even in the visual SSIS way, is foreign, and intimidating. However, they could use Linked Servers if they really needed to do this.

    Also, most companies aren't "enterprises". Most companies are smaller, relatively few servers, and this would work.

    I did work in an enterprise, and we had each server gather all its information, then rolling that up with SQLCMD instead of DTS (at the time) because it worked well. We stored it in a central server, and we would have a missing row(s) if one server didn't report information. I think if I had to do it, I would use SSIS, make simple transforms that pulled data from each server to a central location.

    In that company, however, we did have to mark an Excel sheet with our initials that signified we had checked on the servers for ISO/SOX compliance. It's one thing to make a report, but another to be sure someone checked it (or at least signified they did). This would work for that, get an Excel sheet mailed to each person, let someone initial it and save it off as a verified report.

  • lcarrethers

    SSCommitted

    Points: 1917

    I never could get this to work

  • Datamate Technology

    Old Hand

    Points: 399

    Its currnetly being edited. I used Bold characters to type the database name and it inturn has added HTML tags to it which the SQL Engine would not understand. I will waiting for the editor to repost the updated article

  • Datamate Technology

    Old Hand

    Points: 399

    The table name have been corrected at all ends and Hopefully we shouldnt have users complaining about it anymore but I guess thats just wishful thinking

  • SanjayAttray

    SSChampion

    Points: 13157

    Good article. I am running something similar to extract report on daily basis. But, had not used Excel. Don't know if using Excel across different servers would be a good idea or not? This could be accomplished easily by SSRS, if you had got data in a table.

    Any one here ?

    SQL DBA.

  • randall.c.newcomb

    Say Hey Kid

    Points: 685

    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

    SSC Journeyman

    Points: 92

    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 🙂

  • Nitya

    Say Hey Kid

    Points: 699

    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

    SSC Veteran

    Points: 200

    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

    SSCoach

    Points: 15651

    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

    SSC Veteran

    Points: 245

    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

    SSC Enthusiast

    Points: 168

    Thanks. It's working now...

  • Datamate Technology

    Old Hand

    Points: 399

    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

    SSC Enthusiast

    Points: 174

    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

    Valued Member

    Points: 62

    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

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply