get-sqldatabase issue

Apr 13, 2011 at 8:37 PM

I am trying to use get-SqlDatabase to grab database names and sizes from across my environment but keep running into issues with named instances. The below runs fine until I add a server instance into my sqlmachines table like tester02\test02. Can someone help me with this issue?

#Define Server and Database Repository

$ServerRepository = "tester\tester01"

$DatabaseRepository = "PerformanceDatabase"
#Get Server listing from database table

$Servers = get-sqldata -sqlserver $ServerRepository -dbname $DatabaseRepository -qry "Select InstanceName from SQLMachines"

foreach($Server in $Servers)
{
$dbnames = Get-SqlDatabase $Server.InstanceName | Select name, size
foreach ($dbname in $dbnames)
 {
 $servername = $Server.ServerName
 $databasename = $dbname.name
 $CurrentSize = $dbname.size
 Write-Host "$servername $databasename $CurrentSize";

 }
}

 

 

Coordinator
Apr 14, 2011 at 12:29 AM
I'm having trouble reproducing the issue you mention.

I have table with the following values:

Z003\R2
Z003\SQLEXPRESS

And your scripts produces the following output:

AdventureWorks 172
AdventureWorks2008R2 181.625
AdventureWorksDW 69.125
AdventureWorksDW2008R2 75.1875
AdventureWorksLT 7.1875
AdventureWorksLT2008R2 7.1875
backupdw 6.0625
DBA_Admin 3
dbutility 3
MDW 3
Northwind 4.25
pubs 3
pubscopy 3
ReportServer$R2 11
ReportServer$R2TempDB 3
rsdd 6
spacedm 3
SQLPSX 3
ssisconfig 3

On Wed, Apr 13, 2011 at 4:37 PM, king6080 <notifications@codeplex.com> wrote:

From: king6080

I am trying to use get-SqlDatabase to grab database names and sizes from across my environment but keep running into issues with named instances. The below runs fine until I add a server instance into my sqlmachines table like tester02\test02. Can someone help me with this issue?

#Define Server and Database Repository

$ServerRepository = "tester\tester01"

$DatabaseRepository = "PerformanceDatabase"
#Get Server listing from database table

$Servers = get-sqldata -sqlserver $ServerRepository -dbname $DatabaseRepository -qry "Select InstanceName from SQLMachines"

foreach($Server in $Servers)
{
$dbnames = Get-SqlDatabase $Server.InstanceName | Select name, size
foreach ($dbname in $dbnames)
{
$servername = $Server.ServerName
$databasename = $dbname.name
$CurrentSize = $dbname.size
Write-Host "$servername $databasename $CurrentSize";

}
}

Read the full discussion online.

To add a post to this discussion, reply to this email (SQLPSX@discussions.codeplex.com)

To start a new discussion for this project, email SQLPSX@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com


Apr 14, 2011 at 2:06 PM
I found the problem. I put a 0 instead of a O in my instance name. Thanks!!


From: [email removed]
To: [email removed]
Date: Wed, 13 Apr 2011 17:29:18 -0700
Subject: Re: get-sqldatabase issue [SQLPSX:253785]

From: cmille19
I'm having trouble reproducing the issue you mention.

I have table with the following values:

Z003\R2
Z003\SQLEXPRESS

And your scripts produces the following output:

AdventureWorks 172
AdventureWorks2008R2 181.625
AdventureWorksDW 69.125
AdventureWorksDW2008R2 75.1875
AdventureWorksLT 7.1875
AdventureWorksLT2008R2 7.1875
backupdw 6.0625
DBA_Admin 3
dbutility 3
MDW 3
Northwind 4.25
pubs 3
pubscopy 3
ReportServer$R2 11
ReportServer$R2TempDB 3
rsdd 6
spacedm 3
SQLPSX 3
ssisconfig 3

On Wed, Apr 13, 2011 at 4:37 PM, king6080 <notifications@codeplex.com> wrote:
From: king6080
I am trying to use get-SqlDatabase to grab database names and sizes from across my environment but keep running into issues with named instances. The below runs fine until I add a server instance into my sqlmachines table like tester02\test02. Can someone help me with this issue?
#Define Server and Database Repository
$ServerRepository = "tester\tester01"
$DatabaseRepository = "PerformanceDatabase"
#Get Server listing from database table
$Servers = get-sqldata -sqlserver $ServerRepository -dbname $DatabaseRepository -qry "Select InstanceName from SQLMachines"
foreach($Server in $Servers)
{
$dbnames = Get-SqlDatabase $Server.InstanceName | Select name, size
foreach ($dbname in $dbnames)
{
$servername = $Server.ServerName
$databasename = $dbname.name
$CurrentSize = $dbname.size
Write-Host "$servername $databasename $CurrentSize";
}
}


Read the full discussion online.
To add a post to this discussion, reply to this email (SQLPSX@discussions.codeplex.com)
To start a new discussion for this project, email SQLPSX@discussions.codeplex.com
You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.
Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com

Read the full discussion online.
To add a post to this discussion, reply to this email (SQLPSX@discussions.codeplex.com)
To start a new discussion for this project, email SQLPSX@discussions.codeplex.com
You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.
Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com
Coordinator
Apr 14, 2011 at 2:21 PM
No worries. Happens to all of us. I spent a half hour last week trying to figure out why one of my scripts wasn't working--I had misspelled process (a PowerShell keyword).



On Thu, Apr 14, 2011 at 10:06 AM, king6080 <notifications@codeplex.com> wrote:

From: king6080

I found the problem. I put a 0 instead of a O in my instance name. Thanks!!


From: [email removed]
To: [email removed]
Date: Wed, 13 Apr 2011 17:29:18 -0700
Subject: Re: get-sqldatabase issue [SQLPSX:253785]

From: cmille19

I'm having trouble reproducing the issue you mention.

I have table with the following values:

Z003\R2
Z003\SQLEXPRESS

And your scripts produces the following output:

AdventureWorks 172
AdventureWorks2008R2 181.625
AdventureWorksDW 69.125
AdventureWorksDW2008R2 75.1875
AdventureWorksLT 7.1875
AdventureWorksLT2008R2 7.1875
backupdw 6.0625
DBA_Admin 3
dbutility 3
MDW 3
Northwind 4.25
pubs 3
pubscopy 3
ReportServer$R2 11
ReportServer$R2TempDB 3
rsdd 6
spacedm 3
SQLPSX 3
ssisconfig 3

On Wed, Apr 13, 2011 at 4:37 PM, king6080 <notifications@codeplex.com> wrote:
From: king6080
I am trying to use get-SqlDatabase to grab database names and sizes from across my environment but keep running into issues with named instances. The below runs fine until I add a server instance into my sqlmachines table like tester02\test02. Can someone help me with this issue?
#Define Server and Database Repository
$ServerRepository = "tester\tester01"
$DatabaseRepository = "PerformanceDatabase"
#Get Server listing from database table
$Servers = get-sqldata -sqlserver $ServerRepository -dbname $DatabaseRepository -qry "Select InstanceName from SQLMachines"
foreach($Server in $Servers)
{
$dbnames = Get-SqlDatabase $Server.InstanceName | Select name, size
foreach ($dbname in $dbnames)
{
$servername = $Server.ServerName
$databasename = $dbname.name
$CurrentSize = $dbname.size
Write-Host "$servername $databasename $CurrentSize";
}
}


Read the full discussion online.
To add a post to this discussion, reply to this email (SQLPSX@discussions.codeplex.com)
To start a new discussion for this project, email SQLPSX@discussions.codeplex.com
You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.
Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com

Read the full discussion online.
To add a post to this discussion, reply to this email (SQLPSX@discussions.codeplex.com)
To start a new discussion for this project, email SQLPSX@discussions.codeplex.com
You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.
Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com

Read the full discussion online.

To add a post to this discussion, reply to this email (SQLPSX@discussions.codeplex.com)

To start a new discussion for this project, email SQLPSX@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com


Apr 14, 2011 at 3:24 PM

 I am trying to use get-sqldatabase to gather basic info for all databases in my environment and one info point is size. How do I find out the unit of measurement for space properties? Get-Member shows me the property but not the UOM.

Coordinator
Apr 14, 2011 at 3:40 PM
Although get-member is useful, if you want more details take a look at the MSDN documentation for the particular class. Here's a link to the database class:

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database_properties.aspx

Size is in MB's


On Thu, Apr 14, 2011 at 11:24 AM, king6080 <notifications@codeplex.com> wrote:

From: king6080

I am trying to use get-sqldatabase to gather basic info for all databases in my environment and one info point is size. How do I find out the unit of measurement for space properties? Get-Member shows me the property but not the UOM.

Read the full discussion online.

To add a post to this discussion, reply to this email (SQLPSX@discussions.codeplex.com)

To start a new discussion for this project, email SQLPSX@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com


Apr 14, 2011 at 4:07 PM

Awesome!! Thank you for all your help.

Apr 14, 2011 at 9:40 PM

One last thing..I think

I have my preliminary script completed and it runs from my powershell command line just fine but now I want to run it from within my SQL Agent Job. My google search did not turn up anything I recognized as a how-to. Can you either give me an example or a link for accomplishing the task?

Coordinator
Apr 15, 2011 at 1:01 AM
Since you're using SQLPSX which requires PowerShell V2 you'll need to create a cmdxec job step (job which calls an operating system command). SQL Server 2008 higher has a PowerShell job step type, however this uses sqlps (the host that ships with SQL 2008 and SQL 2008 R2) and does not support PowerShell V2.
There is an example of a PowerShell job in the \WindowsPowerShell\Modules\SQLServer\Database\SQLPSX.Job.sql the job has a step:
C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.EXE -command "C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules\SQLServer\run-SmoToCsvFile.ps1 2>&1"


On Thu, Apr 14, 2011 at 5:40 PM, king6080 <notifications@codeplex.com> wrote:

From: king6080

One last thing..I think

I have my preliminary script completed and it runs from my powershell command line just fine but now I want to run it from within my SQL Agent Job. My google search did not turn up anything I recognized as a how-to. Can you either give me an example or a link for accomplishing the task?

Read the full discussion online.

To add a post to this discussion, reply to this email (SQLPSX@discussions.codeplex.com)

To start a new discussion for this project, email SQLPSX@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com


Apr 19, 2011 at 12:41 AM

 At first, I had issues that indicated a problem error with the powershell script but to make sure it was not a permission issue I ran the SQL Agent using my account and the script completed successfully. I am going to lower the account privileges to narrow down rights to those absolutely needed but at least the script completed successfully as intended. Thanks for all your help!! 

command line used - C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.EXE -command "C:\Users\mharrell\PSScripts\poll_databases2.ps1"

Oct 4, 2012 at 9:08 PM

I know this post is old, but I do wonder why you would want to run this job as a SQL Agent job. Why not just run it using the Windows Task Scheduler. I have run into numerous issues using SQL Agent to run PowerShell jobs that have essentially nothing to do with SQL Server. By this I mean that I do not need to be logged into SQL Server to run the script or do anything with the information.

I do something like this:


Import-Module SQLServer

$Instance = "NODE\INSTANCE,12345"
foreach ($db in Get-SqlDatabase $Instance | Select Name) {   
$Results=Get-SqlDatabase $Instance $db.name | Get-SqlDataFile | Select Server, dbname, FileGroup, FileName, Size, UsedSpace, FreeSpace, timestamp | Out-DataTable;   
$Results+=Get-SqlDatabase $Instance $db.name | Get-SqlLogFile | Select Server, dbname, FileGroup, FileName, Size, UsedSpace, FreeSpace, timestamp | Out-DataTable;   
Write-DataTable -ServerInstance $Instance -Database dbamon -TableName DatabaseFileFreeSpace -Data $Results;}

What I am trying to figure out is how to get the same info for the system databases.

Coordinator
Oct 4, 2012 at 10:24 PM

 

Get-SqlDatabase with the -force parameter will return system databases as well as user databases.

 

Nov 28, 2012 at 7:55 PM

cmille19! You are me new hero! Thank you so much!