Tracking and Reporting Database Growth

  • Datamate Technology

    Old Hand

    Points: 399

    Comments posted to this topic are about the item Tracking and Reporting Database Growth

  • Louise Moore

    SSC Veteran

    Points: 230

    My workplace was similar to your's - they'd never had a DBA before (and I'd never been one before either). I got tasked with a similar thing - but I used SQLH2 - its a free microsoft tool that does something very similar to what you are doing here.

  • Chris Howarth-536003

    SSCommitted

    Points: 1775

    The idea of the central reporting of database statistics is great, but surely using Excel to connect to each SQL Server instance and generate reports like this can't be considered to be an Enterprise-level solution?

    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.

    Chris

  • jts2013

    Hall of Fame

    Points: 3226

    For me, the best thing about this post was the mention of the stored procedure sp_Msforeachdb, which I've not heard of before - it's a very useful tool and I can see I will be using it a lot!

    Also worth mentioning that the code works in both SQL 2000 and SQL 2008 - always useful when you have a mixture of versions to support.

  • Chris Howarth-536003

    SSCommitted

    Points: 1775

    sp_MSForEachDB has been around since at least SQL Server 6.5, - it is UNDOCUMENTED and, therefore, should not be used in Production code.

    Chris

  • Blackpool Council DBA Team

    SSC Journeyman

    Points: 80

    While I think the article has merit, the fact that the table is created in msdb breaks another rule . . . . . . . I'm fairly sure that Microsoft strongly recoment that you dont use the system DB's for your own objects

    Create an 'Admin' database on each server and create all of your objects within that . . . you can still access the stored procedures and system tables in the system db's using the full syntax (database.schema.objectname).

    The use of undocumented stored procedures is always questionable . . . I have been caught out when they have been dropped . . . (last one was when they dropped xp_getfiledetails) . . . but there are alternatives, and certainly one of the prime considerations when you are upgrading to another version of SQL Server should be . . . what will stop working when we do this

  • Vishal Singh

    SSCertifiable

    Points: 5830

    The solution seems to be okay for beginner levels or if doing for KB purposes. But I do not see it as an Enterprise level solutions but I rather go with SQL Server performance Dashboard if i am running SQL 2005 or higher.

    Also, when I tried this I got error message "User-Defined type not defined"

    on line "Dim cnPubs As ADODB.Connection":hehe:

    However, I appreciate the efforts for knowledge sharing.

    -Forum Etiquette: How to post Performance Problems[/url]

    -Forum Etiquette: How to post data/code to get the best help [/url]

  • ive.henderickx

    SSC Rookie

    Points: 45

    The solution in the article is much more detailed and I'm certainly going to try it, but if you regularly take backups and want a quick result, you can query db growth history like this (its the total dbsize, incl log and accurate as long as there's no compression involved):

    select

    database_name,

    BackupDate = convert(varchar(10),backup_start_date, 111)

    ,SizeInGigs=( backup_size/1024000000)

    from msdb..backupset

    where

    type = 'd'

    order by database_name, backup_start_date desc

  • gpitkis

    SSC Enthusiast

    Points: 182

    MsForEachDB can be easily replaced by using variables in SSIS.

    But the idea is great!

    I have a complete set of scripts that works for sql2000 and higher edditions and it gives me detailed information on every instance, when I need it. The difference is,that I put that data in a separate DB.

  • DBA Cabuloso

    SSCommitted

    Points: 1733

    The first script is incorrect therefore should be corrected.

    The name of the table in the CREATE id dbInformation and in the ALTER is dbInfo.

    Please correct it.

    Lucas Benevides

    DBA Cabuloso

    ________________
    DBA Cabuloso
    Lucas Benevides

  • Lempster

    SSCoach

    Points: 15651

    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 couldn't agree more Chris. We use exactly the approach you mention, plus we set a configurable default threshold for each data and log file so that we get an email alert if a database is running short on space. That enables us to tweak the autogrowth settings (or take whatever action we deem appropriate) well before users notice anything untoward. This is also useful for identifying databases that have been incorrectly sized when created.

    Lempster

  • Simon Worth

    SSC Rookie

    Points: 48

    For an exercise on how to query database size and growth this is a fun project, but I would agree with other posts that this solution may not be enterprise ready yet.

    I would take the table you are using for tracking growth out of MSDB and put it in a User database instead. It's not best practice to use system db for custom user applications and you may find the table vanishes if you apply a service pack or hotfix at some point. It probably wouldn't, but better safe than sorry.

    The create DDL has an error in it - as someone else pointed out. The table created is called DBInformation and the ALTER statement is looking for DBInfo. I'm guessing you have both of those tables in MSDB and that's why you didn't get an error when testing the script before posting it here. You may want to drop that table in MSDB so you don't get confused later.

    Connecting Excel to multiple servers to get the information is not ideal - especially if you want to give the spreadsheet to others for review. There could be security issues involved that would require troubleshooting down the road. You may want to consider consolidating the information from many servers into 1 db on 1 reporting server. SSIS will make this very easy for you using variables, configurations, and expressions.

    You may also want to fire up SSRS for reporting as well to make things easier. That way you can review the results any time you wish or have it emailed to you at the an interval that is appropriate.

    Like I said, this is a good beginning to your monitoring project - and a great way to get familiar with some inner workings of SQL Server. Long term however, you will more than likely want a third party application to do this for you. The last thing you want is to be the person that has to maintain the custom solution and troubleshoot issues when they arise. Especially since you are using undocumented stored procedures in your code - this solution could break with any service pack or upgrade. Same goes for sp_MSForEachTable - it's undocumented and may be removed in newer releases.

    Good job! Happy hunting.

  • bruce.nix

    Valued Member

    Points: 55

    Using the ADODB.* early binding requires you set a reference to ActiveX Objects in Excel. Within the VBA editor Tools>References and then look through the list for the most recent. Otherwise you will get an error.

  • Datamate Technology

    Old Hand

    Points: 399

    Hey ....

    Thanks for the posts and feed back guys. I have corrected the mistake with the table name at the alter table statement. I have also created a user database called TRACKDBGROWTH and create the table within that database so as to free the sytem databases as per Microsoft's practice.

    I know this is not a perfect solution but I just created it as a simple report for my manager to query and see the information in excel and I know for sure this cannot be deployed at an enterprise level. Since the number of SQL installations is pretty low, this was a STOP and GO solution rather than full fledged monitoring deployment. I am sure this is possible with SQL Dashboard and SSIS too and possibly much easier. We also will go for a dedicated third party tool (I am currently evaluating SCOM and Quest Spotlight) eventually. But this article needs to be treated like a convenient store at the corner for basic commodities and not a Walmart.

    About using a central repository, the Site security doesnt provide me the option to run a distributed query to dump the data into one place from multiple machines. So yes That thought had crossed my mind.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720952

    Lokesh, the updated article is approved, so the corrections should be visible.

    Good job on the article.

Viewing 15 posts - 1 through 15 (of 31 total)

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