April 26, 2005 at 7:46 am
I have been asked to develop a spreadsheet that details exacly how much spaced each database uses in my company. I know I can manually go through them in Enterprise Manager, but seriously, I have over 30 SQL Servers and well over 1000 databases to document!
Is there a way to write a script that will walk through each database on a server, and report the space usage for the databases and log files? Can I expand this to pass it a list of servers?
Thanks,
Clif
"Give a lazy man a job, and he will find an easy way to do it!"
April 26, 2005 at 7:59 am
Here is a script on this site that can get you started:
http://www.sqlservercentral.com/scripts/contributions/687.asp
Michelle
April 26, 2005 at 8:53 am
The script in this example needs more documentation to fully understand it. Like, what is the sp sp_MSforeachdb?
April 26, 2005 at 8:59 am
It's basically an hidden cursor that you can use to execute a dynamic sql statement against each database
run this to see what I mean :
EXEC sp_MSforeachdb 'Select ''?'''
it'll return 1 select statement for each database on the server with the dbname.
this will return the number of objects in the database :
EXEC sp_MSforeachdb 'Select ''?'' as DbName, count(*) as Objects from ?.dbo.SysObjects'
April 26, 2005 at 9:01 am
I forgot to mention that there's also another one like this :
sp_msForEachTable that does pretty much the same thing but for all tables in the database. Those sps are however unsupported by Microsoft, which means that they won't help you if you need troubleshooting and that they might not be there in the next release of sql server.
April 27, 2005 at 5:32 am
Hi. Did you make the sps work? I have a different solution that I modified using a script oroginally done by a Mr. Eli Lieber. Its quite a bit of work but let me know so I can post it. Patrick.
April 27, 2005 at 5:41 am
Yes, I modified the script to use a temp table, but it worked fine. Thank you everyone for your help! I now showed my boss that we have 690 GB of databases!
April 27, 2005 at 8:36 am
'display all db sizes in mb for a server
Option Explicit
dim objDB
with CreateObject("SQLDMO.SQLServer")
.LoginSecure = TRUE
.Connect "."
for each objDB in .Databases
wscript.echo objDB.Name & vbtab & objDB.Size
next
end with
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply