List all Databases where size is above 5 GB in CSV Format using PowerShell

Hello Experts!

I need to find a way to query all my SQL Servers and instances (I have a few, and I can list the IP Addresses in a text file) for all the Databases which take up more than 5GB, and I need it done in PowerShell which will export it into CSV.

I found some things that can get me started:
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
$servers = Get-Content 'servers.txt'
foreach ($sv in $servers)

Open in new window


I've found a lot of information in this Link  but I can't make heads or tails out of it since I'm not really proficient with PowerShell Scripting (Or scripting at all).

Can Any of you help me with this task?
LVL 9
David SankovskySenior SysAdminAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
Based on: http://www.sqlserver-dba.com/2013/04/sql-database-size-report-using-powershell.html

set-location C:\Temp
 
$minDbSizeInGB = 20

$isodate=Get-Date -format s
$isodate=$isodate -replace(":","")
$basepath=(Get-Location -PSProvider FileSystem).ProviderPath
 
$instancepath=$basepath + "\servers.txt"  #create a file which has the names of your servers. one per line#

$outputfilehtml="\sql_server_db_sizes_" + $isodate + ".html" #this is the file to which th eoutput will be written
$outputfilecsv="\sql_server_db_sizes_" + $isodate + ".txt" #this is the file to which th eoutput will be written

$filePath = ""

$dt = new-object "System.Data.DataTable"
foreach ($instance in get-content $instancepath)
{
	$instance
	$cn = new-object System.Data.SqlClient.SqlConnection "server=$instance;database=msdb;Integrated Security=sspi"
	$cn.Open()
	$sql = $cn.CreateCommand()
	$sql.CommandText = "
		select @@servername as ServerName,
			DB_NAME(mf.database_id) as [db_name],
			ltrim(rtrim(type_desc)) type_desc,
			ltrim(rtrim(str(convert(dec(14,2),size) / 128,14,2))) as DATABASE_SIZE_MB,
			ltrim(rtrim(str(convert(dec(14,2),size) / 128,14,2)/convert(dec(8,2),1024.00))) as DATABASE_SIZE_GB
		from sys.master_files as mf
			INNER JOIN sys.databases as da ON da.database_id = mf.database_id
        where str(convert(dec(14,2),size) / 128,14,2)/convert(dec(8,2),1024.00) > " + $minDbSizeInGB + "
	"
	$rdr = $sql.ExecuteReader()
	$dt.Load($rdr)
	$cn.Close()
}
 
$dt | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Html -head $reportstyle -body "SQL Server Database Sizes" | Set-Content $outputfilehtml 
$dt | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | Export-Csv $outputfilecsv

Invoke-Item $outputfilecsv
Invoke-Item $outputfilehtml

Open in new window

David SankovskySenior SysAdminAuthor Commented:
Thanks MlandaT, however it seems that this script is to be run ON the SQL server itself.
I need a PS script that can be run on any server inside the same environemnt and will connect to all the SQL servers there, preferably will also be able to identify the instances by itself.
MlandaTCommented:
Nope. It does not need to be run on the SQL Server. It's just assuming that you are using Windows Authentication on all  you servers.

You can edit the connection string on line 20
Alternatively, you can pass the username and password in the file, server.txt, then parse the values out
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

David SankovskySenior SysAdminAuthor Commented:
Hi.

I tried Running the script with only one host in the text file. I got the following errors:


Windows PowerShell
Copyright (C) 2009 Microsoft Corporation. All rights reserved.

PS C:\Users\administrator.WBX> cd..
PS C:\Users> cd..
PS C:\> cd .\SqlReports
PS C:\SqlReports> .\report.ps1
wbx-sql-p08
Exception calling "Open" with "0" argument(s): "A network-related or instance-specific error occurred while establishin
g a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct
and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not o
pen a connection to SQL Server)"
At C:\SqlReports\report.ps1:21 char:10
+     $cn.Open <<<< ()
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader requires an open and available Connection. The c
onnection's current state is closed."
At C:\SqlReports\report.ps1:33 char:27
+     $rdr = $sql.ExecuteReader <<<< ()
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Exception calling "Load" with "1" argument(s): "Value cannot be null.
Parameter name: dataReader"
At C:\SqlReports\report.ps1:34 char:10
+     $dt.Load <<<< ($rdr)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Any Ideas?
MlandaTCommented:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

It's a pretty self explanatory error though. Are you able to connect to this SQL Server instance from your computer using SQL Server Management Studio or other tool? Please cross check that the SQL Server instance name you have specified is also correct.
David SankovskySenior SysAdminAuthor Commented:
I am able to connect for certain as they are on the same internal segment and as such as in DMZ towards each other.
As I stated earlier, I can't know in advance the name of all the instances on the server(s) in question, can the script somehow query the server for all available instances and then run from there?

*EDIT*
I updated the server list to also include the uncommon port and the instance:

wbx-sql-p08:49633\MSSQL

I still get the same errors.
Telneting to the address with said port works fine.
MlandaTCommented:
Use a comma to specify a port number with SQL Server:

wbx-sql-p08\MSSQL,49633
David SankovskySenior SysAdminAuthor Commented:
That did solve the first error, but now I have new errors:


PS C:\SqlReports> .\report.ps1
wbx-sql-p08\MSSQL,49633
Exception calling "Open" with "0" argument(s): "Cannot generate SSPI context."
At C:\SqlReports\report.ps1:21 char:10
+     $cn.Open <<<< ()
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Exception calling "ExecuteReader" with "0" argument(s): "ExecuteReader requires an open and available Connection. The c
onnection's current state is closed."
At C:\SqlReports\report.ps1:33 char:27
+     $rdr = $sql.ExecuteReader <<<< ()
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Exception calling "Load" with "1" argument(s): "Value cannot be null.
Parameter name: dataReader"
At C:\SqlReports\report.ps1:34 char:10
+     $dt.Load <<<< ($rdr)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException
MlandaTCommented:
Cannot generate SSPI context.

That means it needs a SQL server login. I'll send that to you just now.
David SankovskySenior SysAdminAuthor Commented:
Does that mean I have to change the connection string, even if the credentials I use to log into the server from which I'm running the script are domain credentials and they are SA on all the SQL Servers?
MlandaTCommented:
set-location C:\Temp
 
$minDbSizeInGB = 20

$isodate=Get-Date -format s
$isodate=$isodate -replace(":","")
$basepath=(Get-Location -PSProvider FileSystem).ProviderPath
 
$instancepath=$basepath + "\servers.txt"  #create a file which has the names of your servers. one per line#

$outputfilecsv="\sql_server_db_sizes_" + $isodate + ".txt" #this is the file to which th eoutput will be written

$filePath = ""

$dt = new-object "System.Data.DataTable"
foreach ($instance in get-content $instancepath)
{

    $useSqlAuth = false
    $split = $instance.split("|")
    $instance = $split[0]
    if( $split.Length -gt 2) {
        $useSqlAuth = $TRUE
        $sqlUserName = $split[1]
        $sqlPassword = $split[2]
    }
	$instance

	$cn = new-object System.Data.SqlClient.SqlConnection "server=$instance;database=msdb;Integrated Security=sspi"
    if($useSqlAuth) {
        $cn = new-object System.Data.SqlClient.SqlConnection "server=$instance;user id=$sqlUserName;password=$sqlPassword;"
    }

	$cn.Open()
	$sql = $cn.CreateCommand()
	$sql.CommandText = "
		select distinct @@servername as ServerName,
			DB_NAME(mf.database_id) as [db_name],
			ltrim(rtrim(type_desc)) type_desc,
			ltrim(rtrim(str(convert(dec(14,2),size) / 128,14,2))) as DATABASE_SIZE_MB,
			ltrim(rtrim(str(convert(dec(14,2),size) / 128,14,2)/convert(dec(8,2),1024.00))) as DATABASE_SIZE_GB
		from sys.master_files as mf
			INNER JOIN sys.databases as da ON da.database_id = mf.database_id
        where str(convert(dec(14,2),size) / 128,14,2)/convert(dec(8,2),1024.00) > " + $minDbSizeInGB + "
	"
	$rdr = $sql.ExecuteReader()
	$dt.Load($rdr)
	$cn.Close()

    $dt | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | Export-Csv $outputfilecsv -Append

}
 
Invoke-Item $outputfilecsv

Open in new window

And servers.txt looks like this. Where there is just a servername, it's windows auth, else, sql server auth. You will notice that we adjust the querystring accrodingly
dbserver
abcdefghi.database.windows.net|myuser|mypassword
localhost\sqlqexpress|anotheruser|anotherpassword
anotherDbServer

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David SankovskySenior SysAdminAuthor Commented:
in programming like always, Solving one issue raises a couple more.

Currently, My script is nearly identical to your with minute differences (the name of the original folder, the size, and correcting of a small typo from false to $FALSE)

set-location C:\SqlReports
 
$minDbSizeInGB = 5

$isodate=Get-Date -format s
$isodate=$isodate -replace(":","")
$basepath=(Get-Location -PSProvider FileSystem).ProviderPath
 
$instancepath=$basepath + "\servers.txt"  #create a file which has the names of your servers. one per line#

$outputfilecsv="\sql_server_db_sizes_" + $isodate + ".txt" #this is the file to which th eoutput will be written

$filePath = ""

$dt = new-object "System.Data.DataTable"
foreach ($instance in get-content $instancepath)
{

    $useSqlAuth = $FALSE
    $split = $instance.split("|")
    $instance = $split[0]
    if( $split.Length -gt 2) {
        $useSqlAuth = $TRUE
        $sqlUserName = $split[1]
        $sqlPassword = $split[2]
    }
	$instance

	$cn = new-object System.Data.SqlClient.SqlConnection "server=$instance;database=msdb;Integrated Security=sspi"
    if($useSqlAuth) {
        $cn = new-object System.Data.SqlClient.SqlConnection "server=$instance;user id=$sqlUserName;password=$sqlPassword;"
    }

	$cn.Open()
	$sql = $cn.CreateCommand()
	$sql.CommandText = "
		select distinct @@servername as ServerName,
			DB_NAME(mf.database_id) as [db_name],
			ltrim(rtrim(type_desc)) type_desc,
			ltrim(rtrim(str(convert(dec(14,2),size) / 128,14,2))) as DATABASE_SIZE_MB,
			ltrim(rtrim(str(convert(dec(14,2),size) / 128,14,2)/convert(dec(8,2),1024.00))) as DATABASE_SIZE_GB
		from sys.master_files as mf
			INNER JOIN sys.databases as da ON da.database_id = mf.database_id
        where str(convert(dec(14,2),size) / 128,14,2)/convert(dec(8,2),1024.00) > " + $minDbSizeInGB + "
	"
	$rdr = $sql.ExecuteReader()
	$dt.Load($rdr)
	$cn.Close()

    $dt | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | Export-Csv $outputfilecsv -Append

}
 
Invoke-Item $outputfilecsv

Open in new window


My servers file looks like this (For now, only one server is being tested)
wbx-sql-p08\MSSQL,49633

Open in new window


After running the PS1 file, I got the following output:

PS C:\SqlReports> .\report.ps1
wbx-sql-p08\MSSQL,49633
Export-Csv : A parameter cannot be found that matches parameter name 'Append'.
At C:\SqlReports\report.ps1:50 char:126
+     $dt | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | Export-Csv $outputfilecsv
 -Append <<<<
    + CategoryInfo          : InvalidArgument: (:) [Export-Csv], ParameterBindingException
    + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell.Commands.ExportCsvCommand

Invoke-Item : Cannot find path 'C:\sql_server_db_sizes_2015-09-17T105314.txt' because it does not exist.
At C:\SqlReports\report.ps1:54 char:12
+ Invoke-Item <<<<  $outputfilecsv
    + CategoryInfo          : ObjectNotFound: (C:\sql_server_d...9-17T105314.txt:String) [Invoke-Item], ItemNotFoundEx
   ception
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.InvokeItemCommand
MlandaTCommented:
Take out the -Append on line 50. That will work for one server. Let's see if that works (for now)
David SankovskySenior SysAdminAuthor Commented:
Seems to do it.

Now I have the following output

#TYPE Selected.System.Data.DataRow
"ServerName","db_name","type_desc","DATABASE_SIZE_MB","DATABASE_SIZE_GB"
"WBX-SQL-P08\CRM2013","AltoProd_MSCRM","ROWS","168786.25","164.83032226562"
"WBX-SQL-P08\CRM2013","altotest_MSCRM","LOG","12908.00","12.60546875000"
"WBX-SQL-P08\CRM2013","altotest_MSCRM","ROWS","96739.31","94.47198242187"
"WBX-SQL-P08\CRM2013","aquagallery_mscrm","ROWS","68372.19","66.76971679687"
"WBX-SQL-P08\CRM2013","BotzBike_MSCRM","ROWS","6216.00","6.07031250000"
"WBX-SQL-P08\CRM2013","Checkpon_MSCRM","LOG","12027.75","11.74584960937"
"WBX-SQL-P08\CRM2013","comsecure_MSCRM","ROWS","7505.19","7.32928710937"
"WBX-SQL-P08\CRM2013","ecofieldtrips_MSCRM","ROWS","10410.63","10.16663085937"
"WBX-SQL-P08\CRM2013","forsure_MSCRM","ROWS","5899.25","5.76098632812"
"WBX-SQL-P08\CRM2013","kabin2013_MSCRM","ROWS","18913.06","18.46978515625"
"WBX-SQL-P08\CRM2013","karkaotisrael13_MSCRM","ROWS","6597.63","6.44299804687"
"WBX-SQL-P08\CRM2013","Notal2013PreTest_MSCRM","ROWS","14858.94","14.51068359375"
"WBX-SQL-P08\CRM2013","NotalCEFTest_MSCRM","ROWS","12480.31","12.18780273437"
"WBX-SQL-P08\CRM2013","NotalVision_MSCRM","ROWS","17600.31","17.18780273437"
"WBX-SQL-P08\CRM2013","pitronot_MSCRM","ROWS","11669.69","11.39618164062"
"WBX-SQL-P08\CRM2013","sailor_MSCRM","ROWS","9791.19","9.56170898437"
"WBX-SQL-P08\CRM2013","Webox13_MSCRM","ROWS","23162.31","22.61944335937"

However, it doesn't actually create a csv file, it opens a text file and doesn't even save it, it just opens unless the default save isn't in the folder that ran the script.
I think that the export CSV has a delimiter flag that cab be used to delimit using commas, which we can use., and we can ommit the size in MB, only GB is required.

I know return to my old question, is it possible to have the script automatically detect all the instances of a given server so that the file will only contain the hostname and the Port?
David SankovskySenior SysAdminAuthor Commented:
I seem to have found something that does just that, but it's a PowerShell script in it's own right. can it somehow be used in this script to obtain all the instances based on a hostname?
MlandaTCommented:
Aha! That looks good. Unfortunately, I'm into meetings for a few hours and won't be able to help just yet
David SankovskySenior SysAdminAuthor Commented:
You are helping me out of your kindness and goodwill. Time limitations do not apply in such situations, Take all the time you need :)
David SankovskySenior SysAdminAuthor Commented:
While it's not a complete solution, It got me rolling on the right direction.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.