Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Retrieving a graphical representation of used/total space in all databases on a server Expand / Collapse
Author
Message
Posted Thursday, June 11, 2009 8:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 22, 2010 12:06 PM
Points: 14, 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

Post #733465
Posted Thursday, June 11, 2009 9:38 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 9,294, Visits: 9,492
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."
Post #733487
Posted Friday, June 12, 2009 9:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 22, 2010 12:06 PM
Points: 14, 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
Post #733949
Posted Friday, June 12, 2009 10:00 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 9,294, Visits: 9,492
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."
Post #733988
Posted Friday, June 12, 2009 11:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:03 PM
Points: 1,109, Visits: 556
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
Post #734042
Posted Friday, June 12, 2009 1:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 22, 2010 12:06 PM
Points: 14, 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
Post #734106
Posted Friday, June 12, 2009 1:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 22, 2010 12:06 PM
Points: 14, 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.

Post #734109
Posted Friday, June 12, 2009 7:23 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 9,294, Visits: 9,492
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."
Post #734213
Posted Saturday, June 13, 2009 5:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 22, 2010 12:06 PM
Points: 14, 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.
Post #734463
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse