June 1, 2009 at 10:39 pm
Comments posted to this topic are about the item Tracking and Reporting Database Growth
June 1, 2009 at 10:42 pm
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.
June 2, 2009 at 2:07 am
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
June 2, 2009 at 3:50 am
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.
June 2, 2009 at 4:00 am
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
June 2, 2009 at 4:47 am
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
June 2, 2009 at 4:51 am
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.
June 2, 2009 at 5:15 am
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
June 2, 2009 at 6:40 am
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.
June 2, 2009 at 7:00 am
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
June 2, 2009 at 7:19 am
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
June 2, 2009 at 7:33 am
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.
June 2, 2009 at 7:39 am
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.
June 2, 2009 at 8:08 am
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.
June 2, 2009 at 8:30 am
Lokesh, the updated article is approved, so the corrections should be visible.
Good job on the article.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply