The recurring interval of time in seconds in which to run the workflow.
SyncTime
string
$Config/SyncTime$
Synchronization Time
The synchronization time specified by using a 24-hour format. May be omitted.
TimeoutSeconds
int
$Config/TimeoutSeconds$
Timeout (seconds)
Specifies the time the workflow is allowed to run before being closed and marked as failed.
ScriptDelayMsec
int
$Config/ScriptDelayMsec$
Script Delay (milliseconds)
This parameter sets the delay between consecutive T-SQL queries executed by the workflow. This may help to reduce the footprint generated by the workflow in case of large number of target objects. Please advise with Microsoft Support before changing this parameter.
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
try
{
#try to create connection with server if there is no success
#when create new build connection string
try {
$SqlConnection.ConnectionString = $builder.ConnectionString
$SqlConnection.Open()
}
catch {
}
#no luck
if($SqlConnection.State -ne [System.Data.ConnectionState]::Open ) {
$SqlConnection.ConnectionString = BuildConnectionString $computerName $sqlInstanceName $databaseName $null 0 $DEBUG_SA $DEBUG_PWD
$SqlConnection.Open()
}
return $SqlConnection
}
catch {
if ($SqlConnection -ne $null)
{
if($SqlConnection.State -ne [System.Data.ConnectionState]::Closed ) {
$SqlConnection.Close()
}
$SqlConnection.Dispose()
}
}
return $null
}
# Parameters should be provided after $SqlConnection and $query
# Parameters should be named in the query text as $p1...$pN
function SqlQueryTables($SqlConnection, $query) {
#if an error takes place during execution when the function throws exception
try {
$safeName = $dbName -replace """", """"""
$SqlCmd.CommandText = $query -f $safeName
$SqlCmd.Connection = $SqlConnection
$SqlAdapter.SelectCommand = $SqlCmd
$SqlAdapter.Fill($DataSet)|out-null
$res = $DataSet.Tables
$SqlAdapter.Dispose()
$SqlCmd.Dispose()
}
catch {
if( $SqlAdapter -ne $null)
{
$SqlAdapter.Dispose()
}
if( $SqlCmd -ne $null)
{
$SqlCmd.Dispose()
}
throw $_.Exception
}
return $res
}
#''' Returns -1: If DB is not in AlwaysOn
#''' Returns 0: If DB is in AlwaysOn and replica allow connections is NO
#''' Returns 1: If DB is in AlwaysOn and replica allow connections is YES
function AlwaysOnReplicaAllowConnections($SqlConnection, $DatabaseID)
{
$isAlwaysOn = -1
$query = " SELECT columns.id, CASE WHEN OBJECT_ID('sys.availability_replicas') IS NOT NULL THEN 1 ELSE 0 END AS HasAlwaysOn " +
" FROM master.sys.syscolumns columns where name = 'replica_id' and id = OBJECT_ID('sys.databases')"
$res = SqlConnQueryTables $SqlConnection $query
$res | foreach {
$hasAlwaysOn = $_.HasAlwaysOn
if ($hasAlwaysOn -eq 1) {
$query = " SELECT d.name, d.database_id,
CASE WHEN d.replica_id IS NULL THEN 0 ELSE 1 END AS is_replica,
ar.secondary_role_allow_connections
FROM master.sys.databases d
JOIN master.sys.availability_replicas ar on d.replica_id = ar.replica_id
JOIN master.sys.servers s ON s.name = ar.replica_server_name AND s.server_id = 0 /*local server*/
WHERE d.database_id = @p1 "
$resAv = SqlQueryTables $SqlConnection $query $DatabaseID
$resAv | foreach {
$is_replica = $_.is_replica
if( $is_replica -eq 1) {
$isAlwaysOn = 1
$secondary_role_allow_connections = $_.secondary_role_allow_connections
if ($secondary_role_allow_connections -le 1) {
$isAlwaysOn = 0
}
return $isAlwaysOn
}
}
}
}
return $isAlwaysOn
}
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
try
{
#try to create connection with server if there is no success
#when create new build connection string
try {
$SqlConnection.ConnectionString = $builder.ConnectionString
$SqlConnection.Open()
}
catch {
}
#no luck
if($SqlConnection.State -ne [System.Data.ConnectionState]::Open ) {
$SqlConnection.ConnectionString = BuildConnectionString $computerName $sqlInstanceName $databaseName $null 0 $DEBUG_SA $DEBUG_PWD
$SqlConnection.Open()
}
return $SqlConnection
}
catch {
if ($SqlConnection -ne $null)
{
if($SqlConnection.State -ne [System.Data.ConnectionState]::Closed ) {
$SqlConnection.Close()
}
$SqlConnection.Dispose()
}
}
return $null
}
#The function returns service or "Unknown" state
#Input:
# server - compute name
# service - system service name
# InstanceName - sql server instance name
#Output:
# service state or "Unknown" state
function GetServiceState($server, $service, $InstanceName)
{
try {
if ($service -eq "MSSQL") {
$service = "MSSQL`${0}" -f $InstanceName
}
$namespace = "root/cimv2"
$obje = Get-WmiObject -Namespace $namespace -ComputerName $server -Class "win32_service" -ErrorAction SilentlyContinue | where {$_.name -like $service }
if ($obje -ne $null) {
return $obje.State
}
}
catch {
}
return "Unknown"
}
function Main
{
param(
$computerName,
$sqlInstanceName,
$connectionString,
$tcpPort,
$delay,
$serviceName
)
#error messege has to be logged duaring execution
$log_error_msg = $false
$error_msg = ""
if($DEBUG_MODE -eq 1)
{
$DEBUG_MSG = "ComputerName: $computerName InstanceName: $sqlInstanceName ConnectionString: $connectionString TcpPort: $tcpPort" + [Environment]::NewLine
}
#
# Prepare MOM API and property bag object
#
$api = New-Object -comObject "MOM.ScriptAPI"
$SqlConnection = $null
#if service is not in running state when exit without any error
$state = GetServiceState $computerName $serviceName $sqlInstanceName
if(($state -ne "Running") -and ($state -ne "Unknown"))
{
return
}
try {
#check if connection string valid
$SqlConnection = GetValidatedConnection $computerName $InstanceName 'master' $connectionString $tcpPort $DEBUG_SA $DEBUG_PWD
#if connection is null when exit the script without error
if($SqlConnection -eq $null )
{
PrintDebug $api "The server was not found or was not accessible."
return
}
$queryDB = "USE ""{0}"";
SELECT name as dbName, database_id as dbID FROM sys.databases
WHERE source_database_id IS NULL AND state = 0 AND is_read_only = 0 AND name not in ('master', 'model', 'msdb', 'tempdb');"
$queryMemory = "USE ""{0}"";
select
SUM((convert(float, memory_used_by_table_kb))/1024) as 'Tables_Used_Memory_MB',
SUM((convert(float, (memory_allocated_for_table_kb - memory_used_by_table_kb)))/1024) as 'Tables_Unused_Memory_MB',
SUM((convert(float, memory_used_by_indexes_kb))/1024) as 'Indexes_Used_Memory_MB',
SUM((convert(float, (memory_allocated_for_indexes_kb - memory_used_by_indexes_kb)))/1024) as 'Indexes_Unused_Memory_MB'
from sys.dm_db_xtp_table_memory_stats where object_id > 0;"
Home
Show References: