Retrieving a graphical representation of used/total space in all databases on a server
-
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.
-
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."
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
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.
-
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%5B/quote%5D
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
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
-
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.
-
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%5B/quote%5D
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.
-
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
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. 😀
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply