Tracking and Reporting Database Growth

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

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 1 of 4

1 2 3 4 »»»

Tracking and Reporting Database Growth

Rate Topic

Display Mode

Topic Options

Author

Message

lokeshgm7

Posted Monday, June 01, 2009 10:39 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 21, 2013 9:06 AM
Points: 6, Visits: 138

Comments posted to this topic are about the item Tracking and Reporting Database Growth

Post #727128

Louise Moore

Posted Monday, June 01, 2009 10:42 PM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, April 22, 2013 9:36 PM
Points: 78, Visits: 137

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

Chris Howarth-536003

Posted Tuesday, June 02, 2009 2:07 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 30, 2013 8:03 AM
Points: 266, Visits: 1,025

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

jts_2003

Posted Tuesday, June 02, 2009 3:50 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 12:32 AM
Points: 1,063, Visits: 4,258

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

Chris Howarth-536003

Posted Tuesday, June 02, 2009 4:00 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, May 30, 2013 8:03 AM
Points: 266, Visits: 1,025

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

Blackpool Council DBA Team

Posted Tuesday, June 02, 2009 4:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, October 08, 2012 2:44 AM
Points: 6, Visits: 29

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

Vishal Singh

Posted Tuesday, June 02, 2009 4:51 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 12:30 AM
Points: 353, Visits: 487

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

Post #727296

ive.henderickx

Posted Tuesday, June 02, 2009 5:15 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, October 27, 2010 3:36 AM
Points: 1, Visits: 6

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

gpitkis

Posted Tuesday, June 02, 2009 6:40 AM

Grasshopper

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

DBA Cabuloso

Posted Tuesday, June 02, 2009 7:00 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 7:21 AM
Points: 193, Visits: 211

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 »

31 posts, Page 1 of 4

1 2 3 4 »»»

Permissions