Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tracking and Reporting Database Growth


Tracking and Reporting Database Growth

Author
Message
Lempster
Lempster
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2773 Visits: 1657
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
Simon Worth
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 85
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
bruce.nix
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 6
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.
lokeshgm7
lokeshgm7
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 155
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
Steve Jones
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: Administrators
Points: 42356 Visits: 18876
Lokesh, the updated article is approved, so the corrections should be visible.

Good job on the article.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: Administrators
Points: 42356 Visits: 18876
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
lcarrethers
lcarrethers
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 294
I never could get this to work
lokeshgm7
lokeshgm7
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 155
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
lokeshgm7
lokeshgm7
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 155
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
SanjayAttray
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4073 Visits: 1619
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.
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