Sethu's blog

dev@sqlservr.microsoft

SQL Powershell - Unrestrict Growth size on all Database Files that has AutoGrowth set as None

SQL Powershell - Unrestrict Growth size on all Database Files that has AutoGrowth set as None

  • Comments 1

###########################################################################################
# Scenario: Unrestrict Growth size on all Database Files that has AutoGrowth set as None
# The following SQL 2008 Powershell sample code enumerates all  user database data files and sets Autogrowth by 10%
#
#  How to use this powershell script:
#    - Launch SQL Server Management  Studio ( Start -> All Programs -> Microsoft SQL Server 2008 -> SQL Server Management Studio)
#    - Connect to SQL Server instance, Right click on the server node in Object Explorer and click on "Start Power Shell" menu
#    - Copy the following powershell script and paste it in "SQL Server PowerShell command window
###########################################################################################

# Gets the list of files, Growth types for all databases
function GetFileInfoList
()
{
        $fileInfoList = @
()
       
#get list of databases on this sql instance
        $userDatabaseList = dir Databases

       
#enumerate list of databases
        foreach($db in $userDatabaseList
)
       
{
                $fileGroupPSPath = "Databases\" + $db.Name +
"\FileGroups"
                pushd
$fileGroupPSPath
               
               
# Get list of FileGroups for this database
                $fileGroupCollection = (get-item .).Collection
               
                foreach($fileGroup in $fileGroupCollection)     
               
{
                        $filesPSPath = ".\" + $fileGroup.Name +
"\Files"
                        pushd $filesPSPath
                       
                       
# get list of files
                        $fileList = (get-item .).Collection
                       
                        foreach($file in $fileList )   
                       
{
                                $fileInfoList +=
$file
                       
}
                       
popd
               
}
               
popd
       
}
        $fileInfoList
;
}


# Set GrowthType
function SetFileGrowthType($fileList, $GrowthType = [Microsoft.SqlServer.Management.Smo.FileGrowthType]::Percent , $Growth=10
)
{
        $fileListWithSpecificGrowthType = $fileList   | where-object { $_.GrowthType -eq [Microsoft.SqlServer.Management.Smo.FileGrowthType]::None
}

       
# enumerate files with no growth type
        foreach($fileWithNoGrowthType in $fileListWithSpecificGrowthType  )     
       
{
                if( $fileWithNoGrowthType -ne $null
)
               
{
                        $fileWithNoGrowthType.GrowthType =
$GrowthType
                        $fileWithNoGrowthType.Growth =
$Growth
               
                        write-host "Updating file:" ,  $fileWithNoGrowthType.Name
                        write-host "GrowthType:" ,  $GrowthType
                        write-host "Growth:" ,  $Growth
                       
                        $fileWithNoGrowthType.Alter
()
               
}
       
}
}

$fileList = GetFileInfoList

$fileList  | select-object Name, GrowthType,Growth

SetFileGrowthType($fileList )


 

#This posting is provided "AS IS" with no warranties, and confers no rights.
#Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm

Comments
  • It only sets the log of a db to 10 percent.....the data file remains the same.

Page 1 of 1 (1 items)
Leave a Comment
  • Please add 2 and 4 and type the answer here:
  • Post