Powershell and SQL for getting Database info

Part of monitoring a SharePoint farm is to make sure there is enough space available for the content databases.

If your organization is anything like mine, segregation of duties are very important. Meaning the SQL DBA's do the SQL stuff, and we the SharePoint Admin, do the SharePoint stuff. As a SharePoint Administrator, I must say, I need to know a bit more about the SQL part of the SharePoint Farm.

So, I don't have access to the SQL Server, and I do not really need it. I just need to pull some stats and that is it. I used PowerShell and SQL Queries to get the info I need.

The info I require is:
 - All Databases on the server
 - Stats specific to each database: Size of DB, used space, and free space available. On which Drive the database files reside as well as the Log files.

The method I chose works in my environment and I am sure there are tools and other/better ways to do the same thing.


1. The PowerShell Session would need to be started with an account access to the SQL Server instance.
2. I want to save the info to CSV files for analysis in Excel.

##########################################################################
# Login to farm with Service Account
##########################################################################





$credential = New-Object System.Management.Automation.PsCredential("YourServiceAccount", (ConvertTo-SecureString "yourPassword" -AsPlainText -Force))




start-process powershell.exe -Credential $credential -NoNewWindow -ArgumentList "Start-Process powershell.exe .\SQLStats.ps1 -verb runas"









sqlFunctions.ps1
function Invoke-SQL {
      param(
      [string] $dataSource = ".\SQLEXPRESS",
      [string] $database = "MasterData",
      [string] $sqlCommand = $(throw "Please specify a query."),
      [bool] $export = $false,
      [string] $filename = "data.csv"
      )


      $connectionString =     "Data Source=$dataSource; " +
                              "Integrated Security=SSPI; " +
                              "Initial Catalog=$database"

      $connection = new-object system.data.SqlClient.SQLConnection($connectionString)
      $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
      $connection.Open()

      $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
      $dataset = New-Object System.Data.DataSet
      $adapter.Fill($dataSet) | Out-Null

      $connection.Close()
      $dataSet.Tables

      if($export)
      {
            Remove-Item $filename
            foreach($table in $dataset.Tables)
            {
                  #$table | export-csv $filename -notypeinformation -append
                  $table | ConvertTo-Csv -NoTypeInformation `
                         | select -Skip 1 `
                         | Out-File $filename -Append
            }
      }

}


#I added these queries as functions. Not the right way, but anyway:

#This SQL Query runs a Stored Procedure on the SQL Server for each DB

function getDBStatsQuery()
{
      $query = "exec sp_msforeachdb
      'use [?];
      select DB_NAME() AS DbName,
      physical_name,
      CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) AS Status,
      CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')) AS Recovery,
      CONVERT(varchar(20),Type) AS Type,
      SUM(size)/128.0 AS File_Size_MB,
      SUM(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,
      SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB
      from sys.database_files group by type, physical_name' "
      $query
}




#This SQL Query runs a Stored Procedure to get the available free space for all Drives on the SQL Server

function getOSDriveQuery()
{
      $query = "EXEC master..xp_fixeddrives"
      $query
}







SQLStats.ps1

# Script to get Database and Log sizes

# Have to run script with user that has correct permissions on SQL
Write-host "Running as: " ([Security.Principal.WindowsIdentity]::GetCurrent().Name)



#Include SQL Functions
. .\sqlFunctions.ps1





### Start of Queries
$dataSource = "sqlsever/instance" #SQL Server and Instance
$database = "Master" #Database
$outDataFileName = "infoData.csv"
$outDriveFileName = "infoSQLDrives.csv"





#Run Query
Write-host "Invoking SQL Query to retrieve Database stats..."
$results = $(Invoke-SQL -DataSource $dataSource -Database $database -SQLCommand $(getDBStatsQuery) -Export $true -FileName $outDataFileName )





#Get Drive Space available on Server
Write-host "Invoking SQL Query to retrieve OS Drives free space..."
$drives = $(Invoke-Sql -DataSource $dataSource -Database $database -sqlCommand $(getOSDriveQuery) -Export $true -FileName $outDriveFileName)




Write-host "Done..."

This PowerShell Script creates two CSV Files. One for the Databases and one for the Drives on the SQL Server.


Hope it helps someone.