​The SQL Server provider for Windows PowerShell exposes the hierarchy of SQL Server objects in paths similar to file system paths. You can use the paths to locate an object, and then use methods from the SQL Server Management Object (SMO) models to perform actions on the objects.

For the TechNet reference, check here: http://msdn.microsoft.com/en-us/library/cc281947.aspx

For a video walkthrough and great summary of it, check here: http://sqlserverpedia.com/blog/sql-server-bloggers/scripting-db-objects-in-powershell-video-transcript/

In this scenario, we’re going to use it for a repetitious task in most  SharePoint installations, which is, to assign more sensible Autogrowth settings to your Content DB’s.

For the full rundown on why we need to modify SQL Server settings (and can’t just do it from SharePoint directly), check around the 20 minute mark on this video: http://channel9.msdn.com/Series/Tuning-SQL-Server-2012-for-SharePoint-2013/Tuning-SQL-Server-2012-for-SharePoint-2013-02-Best-Practices-for-SQL-Server-Database-Settings#time=20m24s

1. Open up a PowerShell instance on your SQL Server.

2. Since our task is to set the FileGrowth properties of all the content DB’s, let’s first check out the FileGrowthType Enumeration: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.filegrowthtype.aspx
Your options are:

​Member	​Description
​KB	​File growth occurs in the specified number of KB.
​Percent	​File growth occurs as an amount that is a specified percentage of the existing file size
​None	​The file will not grow automatically.

The FileGrowthType enumeration class is served by the (datafile) GrowthType property and the (logfile) GrowthType property.

Modify your server instance names and autogrowth preferences in the following script, and execute! The filter displayed on it filters to apply only to databases and logfiles with “Content” in the name – the typical naming scheme to indicate SharePoint content databases. Modify to taste:

$Server="SQLSRV2012"      
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $Server
$databases = $SMOserver.Databases;
foreach ($DB in $databases | where{$_.Name -like '*Content*'}) {
    #Set Log File growth
 foreach ($DBLF in $DB.logfiles) {
  $DBLF.set_GrowthType("KB"); 
  $DBLF.set_Growth("51200"); #50mb 
  $DBLF.Alter();
        } 
 #set File Growth 
 $DBFG = $DB.FileGroups;
    foreach ($DBF in $DBFG.Files) {
  $DBF.set_GrowthType("KB"); 
  $DBF.set_Growth("102400"); #100mb 
  $DBF.Alter();
        }
    }