<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Lokesh Gunjugnur
»
Tracking and Reporting Database Growth
31 posts, Page 2 of 4
««
1
2
3
4
»
»»
Tracking and Reporting Database Growth
Rate Topic
Display Mode
Topic Options
Author
Message
Lempster
Lempster
Posted Tuesday, June 02, 2009 7:19 AM
UDP Broadcaster
Group: General Forum Members
Last Login: 2 days ago @ 8:40 AM
Points: 1,450,
Visits: 760
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
Post #727416
Simon Worth
Simon Worth
Posted Tuesday, June 02, 2009 7:33 AM
SSC Rookie
Group: General Forum Members
Last Login: Friday, June 22, 2012 11:53 AM
Points: 28,
Visits: 82
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.
Post #727428
bruce.nix
bruce.nix
Posted Tuesday, June 02, 2009 7:39 AM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, July 08, 2009 7:38 AM
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.
Post #727433
lokeshgm7
lokeshgm7
Posted Tuesday, June 02, 2009 8:08 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, March 21, 2013 9:06 AM
Points: 6,
Visits: 138
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.
Post #727449
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Tuesday, June 02, 2009 8:30 AM
SSC-Dedicated
Group: Administrators
Last Login: Yesterday @ 3:26 PM
Points: 31,425,
Visits: 13,738
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
Post #727461
Steve Jones - SSC Editor
Steve Jones - SSC Editor
Posted Tuesday, June 02, 2009 8:36 AM
SSC-Dedicated
Group: Administrators
Last Login: Yesterday @ 3:26 PM
Points: 31,425,
Visits: 13,738
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
Post #727466
lcarrethers
lcarrethers
Posted Tuesday, June 02, 2009 9:03 AM
Valued Member
Group: General Forum Members
Last Login: Monday, March 11, 2013 10:18 AM
Points: 73,
Visits: 238
I never could get this to work
Post #727490
lokeshgm7
lokeshgm7
Posted Tuesday, June 02, 2009 9:07 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, March 21, 2013 9:06 AM
Points: 6,
Visits: 138
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
Post #727493
lokeshgm7
lokeshgm7
Posted Tuesday, June 02, 2009 10:27 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, March 21, 2013 9:06 AM
Points: 6,
Visits: 138
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
Post #727573
SanjayAttray
SanjayAttray
Posted Tuesday, June 02, 2009 11:42 AM
Hall of Fame
Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924,
Visits: 1,554
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.
Post #727621
« Prev Topic
|
Next Topic »
31 posts, Page 2 of 4
««
1
2
3
4
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
