June 19, 2009 at 12:38 pm
Hello,
I have gone through the script section on this website. I saw code to get the database size of the server you are logged into.
However, I have server databases on serveral servers that my boss wants me to check. How would you write code to check the size a database on a specific server other than the one you are logged onto?
Thanks,
Tony
Things will work out. Get back up, change some parameters and recode.
June 19, 2009 at 12:47 pm
June 19, 2009 at 4:56 pm
Thanks for responding.
No our management would not allow that. They want the servers to be at a minimal configuration and no added software configuration. They thought is absolute vanilla servers.
Whatever solution I get will either be through TSQL, some kind of code, or opening each server in remote desktops and then into enterprise manager for that server.
Tony
Things will work out. Get back up, change some parameters and recode.
June 19, 2009 at 5:16 pm
If this is code run only occasionally you could use openrowset method which is remote access without the need for a linked server.
You could use a table with server names listed and loop through that to run your query against each server
---------------------------------------------------------------------
June 19, 2009 at 7:51 pm
I would use Powershell and SMO - in fact, that is exactly what I use. I run a script every morning that hits all 160 SQL Servers and returns database size and much more.
I don't have the script handy right now, but you can google Powershell and SMO and find plenty of examples.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 21, 2009 at 5:23 pm
I was thinking of trying this with vbscript or something. If you find an example of hitting different databases and getting the database size, I would appreciate it if you could post an example.
Thanks.
Things will work out. Get back up, change some parameters and recode.
June 21, 2009 at 9:05 pm
Using Powershell, the first step is to load the SMO libraries:
PS> [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.SMO") | Out-Null
The next step is to setup the list of the SQL Server instances we want to access, for example, in the file servers.txt we would have:
SERVERA
SERVERB
SERVERC\Instance1
SERVERC\Instance2
Now, we need to get that list of servers into a variable in Powershell:
PS> $servers = get-content servers.txt
Next step is to loop through each server:
$servers | % {
$sql = New-Object ('Microsoft.sqlserver.management.smo.server') $_
#now we can loop through each table and get the space usage
$sql.Databases | % {
$dataUsage = 0; $_.Tables | % {$dataUsage += $_.DataSpaceUsed}; $dataUsage
$indexUsage = 0; $_.Tables | %{$indexUsage += $_.IndexSpaceUsed}; $indexUsage
}
}
This is just a basic outline that should get you started. There are lots of examples of using SMO in multiple scripting languages. A google search will give you plenty of examples.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply