Intervalo de tiempo periódico en segundos en que se ejecuta el flujo de trabajo.
SyncTime
string
$Config/SyncTime$
Hora de sincronización
Hora de sincronización especificada en un formato de 24 horas. Se puede omitir.
TimeoutSeconds
int
$Config/TimeoutSeconds$
Tiempo de espera (segundos)
Especifica el tiempo durante el que se puede ejecutar el flujo de trabajo antes de que se cierre y se marque como error.
ScriptDelayMsec
int
$Config/ScriptDelayMsec$
Retraso de script (milisegundos)
Este parámetro define el retraso entre las consultas T-SQL consecutivas que ejecuta el flujo de trabajo. Puede ayudar a reducir la superficie que genera el flujo de trabajo en caso de gran número de objetos de destino. Consulte con el soporte técnico de Microsoft antes de cambiar este parámetro.
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
try
{
$SqlConnection.ConnectionString = $connectionString
$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
}
#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
ESN 
Show References: