Simplifying Life of SQL Server DBA

Troubleshooting Arithmetic overflow errors in queries using Extended Events in SQL Server 2008

Problem:

Management view sys.dm_exec_requests has a column total_elapsed_time to track the total time elapsed in milliseconds since the request arrived. Following T-SQL query converted this value from milli-seconds to micro-seconds.

-- Simplified version of a complex T-SQL batch
SELECT  session_id, command,
r.total_elapsed_time * 1000 AS total_elapsed_time_in_ms -- Convert ms to us.
FROM sys.dm_exec_requests AS r

Sometimes the above query throws Arithmetic overflow error during while converting large integer value in total_elapsed_time to micro-seconds. This issue happens only when the sessions ran long enough on SQL server to hit this issue, thus making it little harder to predictably reproduce this issue in test environments.

Following T-SQL exception was captured in error log of application that ran this query on a recurring basis.  

Msg 8115, Level 16, State 2, Line 2

Arithmetic overflow error converting expression to data type int.

Troubleshooting:

Extended Events in SQL Server 2008 is a powerful, light weight monitoring and trouble-shooting feature that can help developer/dba to narrow down the root cause of issues like the one above. You can read more about Extended events at http://technet.microsoft.com/en-us/library/bb630282.aspx

Here are the steps I did to capture the exact T-SQL batch that caused this issue. My goal was to have a light weight event gathering mechanism to capture T-SQL batch that caused Error 8115.

Event session definition below has

·        Action -  ACTION (sqlserver.tsql_stack, sqlserver.sql_text) to capture sql_text along with event data

·        Predicate - “WHERE (error = 8115 )” to capture events that are generated only when error 8115 is hit.  

·        File Target - Captured events are logged to asynchronous file target for every 10 seconds to keep this event capture session as light weight as possible.

-- Drop event session if it was defined earlier
IF EXISTS(SELECT * from sys.server_event_sessions where name='ArithmeticError')

      DROP EVENT SESSION ArithmeticError ON SERVER

GO

-- Create event session to capture Error:8115 along with T-SQL Statement that
-- caused this error
-- Note: This script assumes that C:\temp is available and SQL Service account
-- has privileges to read, write to this folder
CREATE EVENT SESSION ArithmeticError ON SERVER
ADD EVENT sqlos.exception_ring_buffer_recorded (
ACTION (sqlserver.tsql_stack, sqlserver.sql_text)
                  WHERE (error = 8115 ))
ADD TARGET package0.asynchronous_file_target( -- file target
SET filename='c:\temp\ArithmeticErrors.xet',
metadatafile='c:\temp\ArithmeticErrors.xem')
WITH (MAX_MEMORY = 4096KB,
EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 10 SECONDS,
MAX_EVENT_SIZE = 0KB,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = OFF,
STARTUP_STATE = OFF
)

GO
-- Start event session
ALTER EVENT SESSION ArithmeticError ON SERVER
state=start

GO

After creating Extended Event session, I ran a long running T-SQL batch whose elapsed time would eventually result in large integer value. I re-started the application that caused this arithmetic overflow error. I let the long running query and the application run for a while. I issued the following T-SQL statement to gather the list of captured events in extended event file target.

-- Query Gathered events from file target
SELECT CAST(event_data as XML) eventdata
      FROM sys.fn_xe_file_target_read_file('c:\temp\*.xet',
       'c:\temp\*.xem',
       NULL,
       NULL)
GO

Using the output of above query, I was able to identify the T-SQL batch that caused this arithmetic overflow issue. Captured event data looks like the following.

<event name="exception_ring_buffer_recorded" package="sqlos" id="21" version="1" timestamp="2009-09-09T20:10:02.503Z">

…

  <data name="error">

    <value>8115</value>

    <text />

  </data>

  <data name="severity">

    <value>16</value>

    <text />

  </data>

  <data name="state">

    <value>2</value>

    <text />

  </data>

 …

  <action name="sql_text" package="sqlserver">

    <value>

SELECT  session_id, command,

r.total_elapsed_time * 1000 AS total_elapsed_time_in_ms -- Convert ms to us.

FROM sys.dm_exec_requests AS r </value>

    <text />

  </action>

</event>

sql_text captured along with event data was useful to isolate the exact query that caused this issue.

After identifying the rootcause of this problem, we no longer need the event seesion to be active, You can drop the event session using following DDL

 -- Drop event session if it was defined earlier
IF EXISTS(SELECT * from sys.server_event_sessions where name='ArithmeticError')

      DROP EVENT SESSION ArithmeticError ON SERVER

GO

References:

1)   SQL 2008 books online

2)   http://technet.microsoft.com/en-us/library/bb630282.aspx

Execute a query on servers in Central Management Server Group

Enable / Disable protocols in SQL Server using PowerShell

Configure Database Mirroring using SQL 2008 Power Shell

###########################################################################################
# Scenario: Configure Database mirroring
#
#  How to use this powershell script:
#    - Launch SQL Server PowerShell ( Start -> Run -> sqlps.exe)
#    - Copy the following powershell script and save to file (Ex c:\DBMirrroringSetup.ps1)
#    - in powershell window, type in script file path ( Ex: c:\DBMirrroringSetup.ps1) to run the script
###########################################################################################

# Backup Database
function BackupDatabase
{
        Param([string]$servername, [string]$dbName, [string]$backupfiledir, [Microsoft.SqlServer.Management.Smo.BackupActionType]$actionType)

       $server = GetServer($servername)

        # construct a unique backup file name
        $backupfilepath = $backupfiledir + "\" + $dbName +  "-" + $actionType.ToString() + "-" +[DateTime]::Now.ToString('s').Replace(":","-") + ".bak"
       
        $backup = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
        $backup.Action = $actionType
        $backup.Database = $dbName
        $backup.Devices.AddDevice($backupfilepath, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)

        $backup.SqlBackup($server)

        write-host "Backup completed successfully"
        write-host "Server:",$server.Name
        write-host "Database:$dbName"
        write-host "Backup File:$backupfilepath"
       
        $backupfilepath;
}

# Restore Database
function RestoreDatabase
{
        Param([string]$servername, [string]$dbName, [string]$backupDataFile)

        $server = GetServer($servername)
        $targetDBFilePath = $server.MasterDBPath + "\" + $dbName +  "-Data-" + [DateTime]::Now.ToString('s').Replace(":","-") + ".mdf"
        $targetLogFilePath = $server.MasterDBLogPath + "\" + $dbName +  "-Log-" + [DateTime]::Now.ToString('s').Replace(":","-") + ".ldf"

        $restore = new-object ('Microsoft.SqlServer.Management.Smo.Restore')
        $restore.Database = $dbName
        $restore.Devices.AddDevice($backupDataFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)

        $relocateDataFile = new-object ('Microsoft.SqlServer.Management.Smo.RelocateFile')($dbName, $targetDBFilePath)

        $logFileName = $dbName + "_Log"
        $relocateLogFile  = new-object ('Microsoft.SqlServer.Management.Smo.RelocateFile')($logFileName, $targetLogFilePath)

        $restore.RelocateFiles.Add($relocateDataFile)
        $restore.RelocateFiles.Add($relocateLogFile)

        $restore.ReplaceDatabase = $True
        $restore.NoRecovery = $True
        $restore.SqlRestore($server)
       
        write-host "Restore completed successfully"
        write-host "Server:", $server.Name
        write-host "Database:$dbName"
}

# Create Endpoints for Database mirroring and grant permissions
function CreateDBMirroringEndPoint
{
        Param([string]$servername, [Microsoft.SqlServer.Management.Smo.ServerMirroringRole]$mirroringRole)
        $server = GetServer($servername)
        $tcpPort = GetNextAvailableTCPPort $servername

        $endPointName = "Database_Mirroring_" + [DateTime]::Now.ToString('s').Replace(":","-")
        $endpoint  = new-object ('Microsoft.SqlServer.Management.Smo.EndPoint')($server, $endPointName)
        $endpoint.ProtocolType = [Microsoft.SqlServer.Management.Smo.ProtocolType]::Tcp
        $endpoint.EndpointType = [Microsoft.SqlServer.Management.Smo.EndpointType]::DatabaseMirroring
        $endpoint.Protocol.Tcp.ListenerPort = $tcpPort 
        $endpoint.Payload.DatabaseMirroring.ServerMirroringRole = $mirroringRole
        $endpoint.Create()
        $endpoint.Start()
       
        # TCP:Server:port
        $fullyQualifiedName = "TCP://" + $server.NetName + ":" + $tcpPort      
        $fullyQualifiedName;
}

# Get DB Mirroring Endpoint ( if configured already)
# Note: we can have only one DB mirroring end point per sql instance
function GetFullyQualifiedMirroringEndpoint
{
        Param([string]$serverInstance, [Microsoft.SqlServer.Management.Smo.ServerMirroringRole]$mirroringRole)
        $fullyQualifiedMirroringEndPointName = ""
       
        $EndPointList = GetEndPointList $serverInstance

        $server = GetServer $serverInstance
       
        if($EndPointList -eq $null)
        {
                $fullyQualifiedMirroringEndPointName = CreateDBMirroringEndPoint $serverInstance $mirroringRole
        }
        else
        {
                foreach($endPoint in $EndPointList)
                {
                        $fullyQualifiedMirroringEndPointName = "TCP://" + $server.NetName + ":" + $endPoint.Properties["ListenerPort"].Value
                        break
                }
        }
       
        write-host "Server Name:$serverInstance"
        write-host "Mirroring Role:$mirroringRole"
        write-host "EndPointName:$fullyQualifiedMirroringEndPointName"
        $fullyQualifiedMirroringEndPointName;
}

# Get EndPoint List
function GetEndPointList
{
        Param([string]$servername)
        $server = GetServer($servername)

        $PSPath = $server.PSParentPath + "\EndPoints"

        $EndPointList = @()

       
        $AllEndPoints = dir $PSPath
        foreach($endpoint in $AllEndPoints)
        {
                $EndPointList += $endpoint.Protocol.Tcp
        }

        $EndPointList;
}


# Get Next available port
function GetNextAvailableTCPPort
{
        Param([string]$serverInstance)
       
        $measure = GetEndPointList $serverInstance | measure-object ListenerPort -max

        if($measure.Maximum -eq $null)
        {
                $maxPort = 5000
        }
        else
        {
                $maxPort = $measure.Maximum
        }

        #choose a random port that is greater than the current max port
        $maxPort + (new-object random).Next(1,500)
}


# Get Server object
function GetServer
{
        Param([string]$serverInstance)

       $array = $serverInstance.Split("\")

       if([String]::IsNullOrEmpty($serverInstance))
       {
                write-error "Server instance  name is not valid"
                return
       }

       if($array.Length -eq 1)
       {
                $machineName = $array[0]
                $instanceName = "DEFAULT"
       }
       else
       {
                $machineName = $array[0]
                $instanceName = $array[1]
       }

       $PSPath = "\SQL\" + $machineName + "\" + $instanceName

       $server = get-item $PSPath
      
       CheckForErrors
       $server;
}

# Set Recovery Model for given database
function SetRecoveryModel
{
        Param($serverInstance, $dbName, [Microsoft.SqlServer.Management.Smo.RecoveryModel]$recoveryModel)
       
        write-host "Setting", $recoveryModel, "Recovery model for database:", $dbName
        $server = GetServer($serverInstance)

        $PSPath = $server.PSParentPath + "\Databases\"  + $dbName

        $db = get-item $PSPath
        $db.RecoveryModel = $recoveryModel
        $db.Alter()
       
        CheckForErrors
}


# Set Partner, Witness
function SetMirroringPartner
{
        Param($serverInstance, $dbName, $fqName, [bool]$isPartner)
        $server = GetServer($serverInstance)

        $PSPath = $server.PSParentPath + "\Databases\"  + $dbName
       
        $db = get-item $PSPath
       
        if($isPartner -eq $True)
        {
                $db.MirroringPartner = $fqName
        }
        else
        {
                $db.MirroringWitness = $fqName
        }
       
        $db.Alter()
}

# Reports Errors
function CheckForErrors
{
        $errorsReported = $False
        if($Error.Count -ne 0)
        {
                write-host "******************************"
                write-host "Errors:", $Error.Count
                write-host "******************************"
                foreach($err in $Error)
                {
                        $errorsReported  = $True
                        if( $err.Exception.InnerException -ne $null)
                        {
                                write-host $err.Exception.InnerException.ToString()
                        }
                        else
                        {
                                write-host $err.Exception.ToString()
                        }
                               
                        write-host "----------------------------------------------"
                }
               
                throw
        }
       
}

# Perform initial validation checks to make sure that all input parameters are valid
function PerformValidation
{
        Param($primary , $mirror, $witness, $shareName, $dbName)
        #Clear any errors
        $Error.Clear()
       
        write-host "Performing Validation checks..."
       
        $primaryServer = GetServer $primary
       
        $PSPath = $primaryServer.PSParentPath + "\Databases\"  + $dbName
       
        $primaryDatabase = get-item $PSPath

        write-host "Checking if Database:$dbName on Primary:$primary is not mirrored..."
        if($primaryDatabase.MirroringStatus -ne [Microsoft.SqlServer.Management.Smo.MirroringStatus]::None)
        {
                $errorMessage = "Cannot setup mirroring on database due to its current MirroringState:" + $primaryDatabase.MirroringStatus
                throw $errorMessage
        }
       
        if($primaryDatabase.Status -ne [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal)
        {
                $errorMessage = "Cannot setup mirroring on database due to its current Status:" + $primaryDatabase.Status
                throw $errorMessage
        }
       
        write-host "Checking if Database:$dbName does not exist on  Mirror:$mirror..."
        $mirrorServer = GetServer $mirror
        $PSPath = $mirrorServer.PSParentPath + "\Databases\"
       
        $mirrorDatabase= get-childitem $PSPath | where {$_.Name -eq $dbName} 
       
        if($mirrorDatabase -ne $null)
        {
                $dbMeasures = $mirrorDatabase | measure-object
                if($dbMeasures.Count -ne 0)
                {
                        $errorMessage = "Database:" + $dbName + " already exists on mirror server:" + $mirror
                        throw $errorMessage
                }
        }
       
        write-host "Checking if Witness Server exists..."
        $witnessServer = GetServer $witness
       
        write-host "Checking if File Share:$ShareName exists..."
        if([System.IO.Directory]::Exists($shareName) -ne $True)
        {
                $errorMessage = "Share:" + $shareName + " does not exists"
                throw $errorMessage
        }
       
        CheckForErrors
}

# Configure Database mirroring; if input params are not passed into this function,
# those values are read from user entered text from console
function ConfigureDatabaseMirroring
{
        Param([string]$primary = $(Read-Host "Primary SQL Instance(like server\instance)") ,
                [string]$mirror = $(Read-Host "Mirror SQL Instance(like server\instance)") ,
                [string]$witness = $(Read-Host "Witness SQL Instance(like server\instance)") ,
                [string]$shareName = $(Read-Host "Share Path(unc path like \\server\share)") ,
                [string]$dbName = $(Read-Host "Database Name")
                )
       
        write-host
        write-host "============================================================="
        write-host " 1: Performing Initial checks; validating input parameters"
        write-host "============================================================="
        PerformValidation $primary $mirror $witness  $shareName $dbName
       
        write-host
        write-host "============================================================="
        write-host " 2: Set Recovery Model as FULL on primary database"
        write-host "============================================================="
        $fullRecoveryModelType = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Full
        SetRecoveryModel $primary $dbName $fullRecoveryModelType

        write-host
        write-host "============================================================="
        write-host " 3: Perform Full Database backup from Primary instance"
        write-host "============================================================="
        $backupActionType = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
        $primaryBackupDataFile = BackupDatabase $primary $dbName $shareName $backupActionType

        write-host
        write-host "============================================================="
        write-host " 4: Restore Database backup on Mirror"
        write-host "============================================================="
        RestoreDatabase $mirror $dbName $primaryBackupDataFile

        write-host
        write-host "============================================================="
        write-host " 5: Create endpoints for database mirroring"
        write-host "============================================================="

        $mirroringRole = [Microsoft.SqlServer.Management.Smo.ServerMirroringRole]::Partner
        $primaryFQName = GetFullyQualifiedMirroringEndpoint $primary $mirroringRole

        $mirrorFQName = GetFullyQualifiedMirroringEndpoint $mirror $mirroringRole

        $mirroringRole = [Microsoft.SqlServer.Management.Smo.ServerMirroringRole]::Witness
        $witnessFQName = GetFullyQualifiedMirroringEndpoint $witness $mirroringRole

        write-host
        write-host "============================================================="
        write-host "  6: Set Primary, Mirror, Witness states in database"
        write-host "============================================================="
        write-host "Connecting to Mirror and set Primary as partner ..."
        SetMirroringPartner $mirror $dbName $primaryFQName $True

        write-host "Connecting to Primary, set partner as mirror and set witness ..."
        SetMirroringPartner $primary $dbName $mirrorFQName   $True
        SetMirroringPartner $primary $dbName $witnessFQName   $False
       
        write-host
        write-host "============================================================="
        write-host "  Database:$dbName mirrored successfully."
        write-host "============================================================="
}

################################################
# Configure Database mirroring
################################################
ConfigureDatabaseMirroring

# 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

SQL 2008 powershell script to print Resource Governor properties

###########################################################################################
# SQL 2008 powershell script to print Resource Governor state, properties, Resource pools and corresponding workload groups
#
#  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 save to file (Ex c:\resourceGovernor.ps1)
#    - in powershell window, type in script file path ( Ex: c:\resourceGovernor.ps1) to run the script
###########################################################################################

####################################
# Prints Resource Governor Configuration to console
####################################
function PrintResourceGovernorProperties
{
        param($server)
        $resourceGovernor = $server.ResourceGovernor
        write-host "=========================================="
        write-host "Resource Governor Configuration for server:", $server.Name
        write-host "=========================================="
       
        $resourceGovernor | select-object  Enabled, ReconfigurePending, ClassifierFunction | format-list
       
        PrintClassifierFunction $server
        EnumerateResourcePools $resourceGovernor
}

####################################
# Prints Classifier function details to console
####################################
function PrintClassifierFunction
{
        param($server)
        $resourceGovernor = $server.ResourceGovernor
       
        # Print classifier function contents only if classifier function was set for Resource Governor
        if($resourceGovernor.ClassifierFunction -ne $null)
        {
                $classifierFunctionName = $resourceGovernor.ClassifierFunction.Replace("[","").Replace("]","")
                $classifierFunctionPSPath = $server.PSParentPath + "\Databases\master\UserDefinedFunctions\" +$classifierFunctionName
               
                write-host "--------------------------"
                write-host "Classifier function details"
                write-host "-------------------------, "
                $classifierFunction = get-item $classifierFunctionPSPath
                $classifierFunction | select-object Name, Owner, IsSchemaBound, TextHeader, TextBody |format-list
        }
}

###########################################
# Enumerates Resource Pools and prints resource pool properties
###########################################
function EnumerateResourcePools
{
        param($resourceGovernor)
       
        write-host "==========================="
        write-host "Resource Pools: (", $resourceGovernor.ResourcePools.Count, "pools)"
        write-host "==========================="
       
        $resourceGovernor.ResourcePools | `
                select-object Name, MinimumCpuPercentage, MaximumCpuPercentage, MinimumMemoryPercentage, MaximumMemoryPercentage |  `
                format-table
       
        foreach($resourcePool in $resourceGovernor.ResourcePools)
        {
                EnumerateWorkloadGroups $resourcePool
        }
}

###########################################
# Enumerates workloadgroups and prints their properties
###########################################
function EnumerateWorkloadGroups
{
        param($resourcePool)
       
        write-host "==========================="
        write-host "Resource Pool:", $resourcePool.Name
        write-host "Workload Groups: (", $resourcePool.WorkloadGroups.Count, "groups)"
        write-host "==========================="
       
        $resourcePool.WorkloadGroups| `
                select-object Name, Importance, GroupMaximumRequests, RequestMaximumCpuTimeInSeconds, `
                RequestMaximumMemoryGrantPercentage, RequestMemoryGrantTimeoutInSeconds, MaximumDegreeOfParallelism |  `
                format-table
}

##Main
$server = (get-item .)
PrintResourceGovernorProperties $server

# 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

SQL 2008 Powershell Script for creating a Policy and saving to file

###########################################################
# SQL 2008 Powershell Script for Policy Based Management
# Scenario: Create a  server policy to check if XPCmdShell is disabled and save it to file
#
# To run this Powershell script
# 1) Install SQL Server 2008
# 2) Run SQL PowerShell ( Start -> Run -> sqlps.exe)
# 3) Copy the following script and paste it in SQL powershell window  to run this script
###########################################################

######################################################
# Creates disconnected policy store
######################################################
function CreateOfflinePolicyStore
{
        $policyStore = new-object ('Microsoft.SqlServer.Management.Dmf.PolicyStore')
        ([Microsoft.SqlServer.Management.Sdk.Sfc.SfcConnectionContext]$policyStore).Mode = [Microsoft.SqlServer.Management.Sdk.Sfc.SfcConnectionContextMode]::Offline
 
        $policyStore;
}

######################################################
# Creates  Expression Node for (XpCommandShellEnabled=False)
######################################################
function GetExpressionNode
{
        $nodeOperator = [Microsoft.SqlServer.Management.Dmf.OperatorType]::EQ
        $nodeAttribute = new-object ('Microsoft.SqlServer.Management.Dmf.ExpressionNodeAttribute')("XPCmdShellEnabled")
        $nodeFunction = [Microsoft.SqlServer.Management.Dmf.ExpressionNode]::ConstructNode($False)
        $expressionNode = new-object ('Microsoft.SqlServer.Management.Dmf.ExpressionNodeOperator')($nodeOperator, $nodeAttribute, $nodeFunction)

        $expressionNode;
}

######################################################
# Creates condition based on ISurfaceAreaFacet Facet
######################################################
function CreateCondition
{
        Param($policyStore)
        
        $conditionName = "Surface_Area_Config_XPCmdShell_Condition"
        $condition = new-object ('Microsoft.SqlServer.Management.Dmf.Condition')($policyStore, $conditionName)
        $condition.Facet =  "ISurfaceAreaFacet"
        $condition.ExpressionNode = GetExpressionNode
        $policyStore.Conditions.Add($condition)
        
        $condition;
}

######################################################
#Gets corresponding facet name for the facet that is referred in the condition
######################################################
function GetFacetName
{
        Param($condition)
        
        $facetInfo = [Microsoft.SqlServer.Management.Dmf.PolicyStore]::Facets[$condition.Facet];
        
        $facetInfo.FacetType.Name;
}

######################################################
# Creates objectset based on the facet that is referred by the condition
######################################################
function CreateObjectSet
{
        Param($policyStore, $policyName, $condition)
        
        $objectSetName = $policyName + "_ObjectSet"
        $objectSet = new-object ('Microsoft.SqlServer.Management.Dmf.ObjectSet')($policyStore, $objectSetName)
        $objectSet.Facet = GetFacetName $condition
        $policyStore.ObjectSets.Add($objectSet)
        
        $objectSet;
}

######################################################
# Creates policy object and sets the condition and object set references
######################################################
function CreatePolicy
{
        Param($policyStore,  $condition, $objectSet)
        
        $policy = new-object ('Microsoft.SqlServer.Management.Dmf.Policy')($policyStore, $policyName)
        $policy.Condition = $condition.Name
        $policy.ObjectSet = $objectSet.Name
        $policyStore.Policies.Add($policy)

        $policy;
}

######################################################
# Saves given policy object to a file.
# To view this policy file, you can launch SQL Server 2008 Management Studio,
#       and click on menu File -> Open -> Policy, Browse to %TEMP% to locate this xml file
######################################################
function ExportPolicyToFile
{
        Param($policy,  $policyFilePath)
        
        $xmlWriter = [System.Xml.XmlWriter]::Create($policyFilePath)
        $policy.Serialize($xmlWriter)
        $xmlWriter.Close()
        write-host "Policy Saved to file:" + $policyFilePath
}



################
## Main code
################

$policyName =  "Surface_Area_Config_XPCmdShell_Policy"

# Create Offline Policy store
$policyStore = CreateOfflinePolicyStore

# Create Condition
$condition = CreateCondition $policyStore

# Create ObjectSet
$objectSet = CreateObjectSet $policyStore $policyName $condition

# Create Policy
$policy = CreatePolicy $policyStore $condition $objectSet

# Export Policy Store to a temp file
# This file can be viewed using SQL Server 2008 Management Studio. (Launch ssms, click on menu File -> Open -> Policy)
$policyFilePath  = $Env:Temp + "\" + $policy.Name + ".xml"
ExportPolicyToFile $policy $policyFilePath

#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

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

###########################################################################################
# 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 )