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:
I hope this will help you get an overview of your databases.