Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Tracking and Reporting Database Growth Expand / Collapse
Author
Message
Posted Monday, June 1, 2009 10:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 27, 2014 10:43 AM
Points: 6, Visits: 150
Comments posted to this topic are about the item Tracking and Reporting Database Growth
Post #727128
Posted Monday, June 1, 2009 10:42 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 7, 2013 10:27 PM
Points: 78, Visits: 141
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.
Post #727129
Posted Tuesday, June 2, 2009 2:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 6:34 AM
Points: 288, Visits: 1,078
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
Post #727207
Posted Tuesday, June 2, 2009 3:50 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:25 AM
Points: 1,105, Visits: 4,902
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.
Post #727259
Posted Tuesday, June 2, 2009 4:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 6:34 AM
Points: 288, Visits: 1,078
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
Post #727267
Posted Tuesday, June 2, 2009 4:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 20, 2014 3:21 AM
Points: 6, Visits: 32
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
Post #727293
Posted Tuesday, June 2, 2009 4:51 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 16, 2014 11:30 PM
Points: 365, Visits: 510
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"

However, I appreciate the efforts for knowledge sharing.


-Forum Etiquette: How to post Performance Problems

-Forum Etiquette: How to post data/code to get the best help
Post #727296
Posted Tuesday, June 2, 2009 5:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:18 AM
Points: 1, Visits: 11
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
Post #727311
Posted Tuesday, June 2, 2009 6:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 18, 2012 12:48 PM
Points: 14, Visits: 48
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.
Post #727386
Posted Tuesday, June 2, 2009 7:00 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:29 PM
Points: 229, Visits: 251
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
Post #727404
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse