Jesper M. Christensen

SharePoint and Security

Tag Archives: server databases

Checking SQL database db and log file sizes and growth with PowerShell – from your Windows 7/8


When you are working with products that are using SQL Server databases it is important that the performance is good. By default the SQL Server sets a very bad default-value for size and Growth, and should always be changed! What to put in really depends on the usage of each database.

When I configure Microsoft SharePoint farms I performance optimize the SQL databases and wants to check the size and Growth of the database and log files, as this is essential for good performance. I want to do this from a client computer using Windows Powershell, and put this script together from various internet articles. If this can be done more efficieltly then please write me 🙂

You need to have the SQL Server Management Tools/SDK installed on the client running this script. The free SQL Server 2012 Management Tools can be downloaded from http://www.microsoft.com/en-us/download/details.aspx?id=29062

##############################################################
 #
 # Powershell script by Jesper M. Christensen
 # Blog: http://JesperMChristensen.wordpress.com
 #
 # Check SQL Server for all databases matching a query
 #
 # Display database name, db+log file size and growth
 #
 # GetSQLDBInfo.ps1 Version 1.0
 #
 ##############################################################

CLS
Set-StrictMode -Version 2
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
$Conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$Conn.applicationName = "PowerShell GetSQLDBInfo (using SMO)"

#Set the parameters for the environment
$Conn.ServerInstance="SQLServer\Instance"
$Conn.LoginSecure = $false                  #Set to true connect using Windows Authentication
$Conn.Login = "sa"                          #Do not apply if you use Windows Authentication
$Conn.Password = "SAPassword"               #Do not apply if you use Windows Authentication

#Connect to the SQL Server and get the databases
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $conn
$dbs = $srv.Databases

#Process all found databases
foreach ($db in $dbs)
 {

 #Process databases if the starts with the database pattern
 if ($db.name -like "SP2013_*") {
  write-host $db.Name

  #Process all database files used by the database 
  foreach ($dbfile in $db.FileGroups.files) {
   $dbfilesize=[math]::floor($dbfile.Size/1024)           #Convert to MB
   if ($dbfile.growthtype -eq "KB") {$dbfilegrowth=[math]::floor($dbfile.growth/1024)} else {$dbfilegrowth=$dbfile.growth}                    #Convert to MB if the type is KB and not Percent
    write-host $dbfile.filename, "Size:"$dbfilesize"MB", "Growth:"$dbfilegrowth, $dbfile.growthtype
   }

  #Process all log files used by the database 
  foreach ($dblogfile in $db.logfiles) {
   $dblogfilesize = [math]::floor($dblogfile.size/1024)   #Convert to MB
   if ($dblogfile.growthtype -eq "KB") {$dblogfilegrowth=[math]::floor($dblogfile.growth/1024)} else {$dblogfilegrowth=$dblogfile.growth}     #Convert to MB if the type is KB and not Percent
    write-host $dblogfile.filename, "Size:"$dblogfilesize"MB", "Growth:"$dblogfilegrowth, $dblogfile.growthtype
   }
   write-host "-"
  }
 }
 #Disconnect from the SQL Server database
 $srv.ConnectionContext.Disconnect()

A screenshot of the output that can be analyzed and should be changed:

sqldbsize

I hope this will help you get an overview of your databases.

Advertisements