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
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
Comments posted to this topic are about the item Tracking and Reporting Database Growth
Louise Moore
Louise Moore
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 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.
Chris Howarth-536003
Chris Howarth-536003
Old Hand
Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)

Group: General Forum Members
Points: 347 Visits: 1159
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
jts2013
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1184 Visits: 5009
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
Chris Howarth-536003
Old Hand
Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)

Group: General Forum Members
Points: 347 Visits: 1159
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
Blackpool Council DBA Team
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: 41
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
Vishal Singh
SSC-Addicted
SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)SSC-Addicted (428 reputation)

Group: General Forum Members
Points: 428 Visits: 517
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

-Forum Etiquette: How to post data/code to get the best help
ive.henderickx
ive.henderickx
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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
gpitkis
gpitkis
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 50
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
DBA Cabuloso
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 Visits: 274
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
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