SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Retrieving a graphical representation of used/total space in all databases on a server


Retrieving a graphical representation of used/total space in all databases on a server

Author
Message
dougznospam-mailbox
dougznospam-mailbox
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 110
At my company we deal with tons of very large databases ranging from 200GB to 3TB in size. Disk space is a constant problem, and I always need to be able to quickly see the breakdown of used space in all the database files on a given server.

SQL 2000 offered a nice graphical representation of the used/total space, but that disappeared in 2005. And even in 2000 you could only see it for a single database, which was rather annoying. So I wrote my tool to do this, and quite frankly I think it does a better job for this particular task than any other method I've seen. I hope you agree.

This HTML Application allows you to connect to a remote server (you must have sysadmin rights or similar) retrieves a list of all the databases, then loop through each one to figure out the used / total space for every file.

http://dougzuck.com/hta


RBarryYoung
RBarryYoung
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19264 Visits: 9518
Nice idea. I can't get it to work though... keeps throwing the error "ADODB.Connection: Safety settings on this computer prohibit accessing a data source on another domain."

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
dougznospam-mailbox
dougznospam-mailbox
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 110
Hmmm, that sounds odd. I've been using this for years in multiple domain environments with no trouble. Additionally, it's just a straight ADO connection using integrated security, so as long as you have admin rights on the remote machine, it shouldn't be a problem. How are you launching it? You should be saving the .hta file to your local machine and then just doubleclick on it to launch it. Is that what you're doing?

If you're able to connect to the database server with SQL Mgmt Studio, then I can see no reason why you shouldn't be able to connect using this HTA. You might want to try running the hta locally on the actual SQL server to see if you can get it to work like that before you try to run it remotely, but like I said, I've used this in many different environments without any trouble. I'd be curious to hear the specifics of your environment and how you're trying to run it. Might want to look at the authentication for the SQL server in the SQL Configuration Manager on that machine.

http://dougzuck.com/hta
RBarryYoung
RBarryYoung
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19264 Visits: 9518
dougznospam-mailbox (6/12/2009)
Hmmm, that sounds odd. I've been using this for years in multiple domain environments with no trouble. Additionally, it's just a straight ADO connection using integrated security, so as long as you have admin rights on the remote machine, it shouldn't be a problem. How are you launching it? You should be saving the .hta file to your local machine and then just doubleclick on it to launch it. Is that what you're doing?

If you're able to connect to the database server with SQL Mgmt Studio, then I can see no reason why you shouldn't be able to connect using this HTA. You might want to try running the hta locally on the actual SQL server to see if you can get it to work like that before you try to run it remotely, but like I said, I've used this in many different environments without any trouble. I'd be curious to hear the specifics of your environment and how you're trying to run it. Might want to look at the authentication for the SQL server in the SQL Configuration Manager on that machine.

http://dougzuck.com/hta

Well, it's my laptop and the SQL Server instances (2005 & 2009) are all local and there is not "other domain". I am using just as you said and I am admin everywhere. I'm running XP Professional SP3, SQL 2005 SP3 and SQL 2008 (none work).

I must say that I haven't seen classic ASP + VBscript + ADO in a coon's age so I am not sure that I have ever used ADO to connect to my SQL Servers. However, I have definitely used ADO to connect to my Access DBs, and I've also used ODBC to connect to my SQL Servers, and I used a ton of MS tools, open source tools and my own stuff to connect to my SQL servers, never a problem.

I used VS 2005 to debug it and it's definitely the "objConnection.Open" statement that it is failing on.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Ale Pelc
Ale Pelc
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: 1237 Visits: 584
Hi there,
Thanks for sharing this simple yet useful scripts. One thing that you should change though is the was you execute the Use DB because you can have a DB with a character like "-" that will fail. I found this on my laptop with a sharepoint DB. Usually, when you create a new web application in sharepoint and you don't change the DB name, it uses custom name that contains that character, so changing this
objConnection.Execute("use " & dbname)


to this
objConnection.Execute("use [" & dbname & "]")


do the work.

Thanks again for sharing these stuff,
Alejandro

Alejandro Pelc
dougznospam-mailbox
dougznospam-mailbox
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 110
That's a great point that hadn't occurred to me since we never name databases with slash characters. Interestingly our SharePoint databases are named automatically with underscores, not slashes. Nevertheless I will definitely make this change. Thanks again.

http://dougzuck.com
dougznospam-mailbox
dougznospam-mailbox
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 110
RBarryYoung (6/12/2009)
dougznospam-mailbox (6/12/2009)
Hmmm, that sounds odd. I've been using this for years in multiple domain environments with no trouble. Additionally, it's just a straight ADO connection using integrated security, so as long as you have admin rights on the remote machine, it shouldn't be a problem. How are you launching it? You should be saving the .hta file to your local machine and then just doubleclick on it to launch it. Is that what you're doing?

If you're able to connect to the database server with SQL Mgmt Studio, then I can see no reason why you shouldn't be able to connect using this HTA. You might want to try running the hta locally on the actual SQL server to see if you can get it to work like that before you try to run it remotely, but like I said, I've used this in many different environments without any trouble. I'd be curious to hear the specifics of your environment and how you're trying to run it. Might want to look at the authentication for the SQL server in the SQL Configuration Manager on that machine.

http://dougzuck.com/hta

Well, it's my laptop and the SQL Server instances (2005 & 2009) are all local and there is not "other domain". I am using just as you said and I am admin everywhere. I'm running XP Professional SP3, SQL 2005 SP3 and SQL 2008 (none work).

I must say that I haven't seen classic ASP + VBscript + ADO in a coon's age so I am not sure that I have ever used ADO to connect to my SQL Servers. However, I have definitely used ADO to connect to my Access DBs, and I've also used ODBC to connect to my SQL Servers, and I used a ton of MS tools, open source tools and my own stuff to connect to my SQL servers, never a problem.

I used VS 2005 to debug it and it's definitely the "objConnection.Open" statement that it is failing on.



That's definitely a bummer. This post (http://thedotnet.com/nntp/303788/showpost.aspx) discusses the same problem you're having, and while there's no solution, it does sound like maybe it's related to your specific laptop configuration-- possibly the IE security settings. I bet if you tried running the HTA from a different machine you'd have success. If I happen to come across any solutions I'll keep you posted.
RBarryYoung
RBarryYoung
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19264 Visits: 9518
Thanks Doug, I got it fixed. That link that you provided did not have my answer, but did provide the clue that I needed to figure it out. Apparently, the sequence of copying your hta to my system and saving in my filesystem allowed the XP SP2 security features to identify it as foreign. It then got flagged in the filesystem as " This file came from another computer and might be blocked...etc..." Which I coudl see through explorer.

Being so flagged, IE would not let the client-running script have access to any of my local resources, because it could have been some virus, trojan or worm, injected into my browser client to exploit my local account rights to infect my system.

All I had to do was to clear that flag on the HTA file and it worked fine.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
dougznospam-mailbox
dougznospam-mailbox
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 110
RBarryYoung (6/12/2009)
Thanks Doug, I got it fixed. That link that you provided did not have my answer, but did provide the clue that I needed to figure it out. Apparently, the sequence of copying your hta to my system and saving in my filesystem allowed the XP SP2 security features to identify it as foreign. It then got flagged in the filesystem as " This file came from another computer and might be blocked...etc..." Which I coudl see through explorer.

Being so flagged, IE would not let the client-running script have access to any of my local resources, because it could have been some virus, trojan or worm, injected into my browser client to exploit my local account rights to infect my system.

All I had to do was to clear that flag on the HTA file and it worked fine.


Excellent! Glad to hear that it's working. :-D
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