A fonte de dados retorna o índice de pares de arquivos de ponto de verificação obsoletos para todos os CFPs por banco de dados com otimização de memória do SQL Server 2014
O intervalo de tempo recorrente em segundos no qual executa-se o fluxo de trabalho.
SyncTime
string
$Config/SyncTime$
Hora da Sincronização
A hora da sincronização especificada usando um formato de 24 horas. Pode ser omitido.
TimeoutSeconds
int
$Config/TimeoutSeconds$
Tempo Limite (segundos)
Especifica o tempo que o fluxo de trabalho pode funcionar antes de ser fechado e marcado como com falha.
ScriptDelayMsec
int
$Config/ScriptDelayMsec$
Atraso do Script (milissegundos)
Este parâmetro define o atraso entre as consultas T-SQL consecutivas executadas pelo fluxo de trabalho. Isso pode ajudar a reduzir a pegada gerada pelo fluxo de trabalho em caso de um grande número de objetos de destino. Consulte o Suporte da Microsoft antes de alterar esse parâmetro.
function ModuleAction($sqlConnection) {
$query = "SELECT ISNULL(100*SUM(CASE WHEN cf.state>4 THEN 1E0 ELSE 0E0 END)/(CASE WHEN COUNT(*)=0 THEN 1 ELSE COUNT(*) END),-1) as StaleRatio FROM sys.dm_db_xtp_checkpoint_files cf"
ForeachDatabase $sqlConnection $false $query (Get-Command 'PropertyBagMapper')
}#SQL2014Constants.ps1
$MANAGEMENT_PACK_VERSION = "6.7.2.0"
# SqlResultMapper.ps1
# Depends on SqlConnectionCommon.ps1
# Depends on Common.ps1
# Depends on SQL2014Constants.ps1
function ListAccessibleDatabases($sqlConnection,$includeSystemDbs=$true) {
$query = "SELECT d.database_id, d.name FROM sys.databases d WHERE d.source_database_id IS NULL AND HAS_DBACCESS(d.name)=1 AND is_read_only = 0 and d.state = 0 and (d.name not in ('master', 'model', 'msdb', 'tempdb') OR (@p1 = 1))";
return SqlQueryRows $sqlConnection $query $includeSystemDbs
}
#Executes query against every accessible database and applies result to $cmd
function ForeachDatabase($sqlConnection, $includeSystemDbs, $query, $cmd) {
$dbs = ListAccessibleDatabases $sqlConnection $includeSystemDbs
$query = 'USE "{0}";'+$query;
$error_msg = ""
$log_error_msg = $false;
foreach ($db in $dbs) {
try {
if ($delay -gt 0) {
Start-Sleep -m $delay
}
$rows = SqlConnQueryRows $sqlConnection $query $db.name
foreach ($row in $rows) {
&$cmd $db.name $row $args
}
} catch {
$flag = AlwaysOnReplicaAllowConnections $sqlConnection $db.database_id
if ($flag -ne 0) {
$log_error_msg = $true
$error_msg += "Error occurred during Request count data source executing.{0}Computer: {1} {0}Reason: {2} {0}Position:{3} {0}Offset:{4} {0}Database: {5}\r\n" -f [Environment]::NewLine, $env:COMPUTERNAME, $_.Exception.Message, $_.InvocationInfo.ScriptLineNumber, $_.InvocationInfo.OffsetInLine, $db.name
}
}
}
if ($log_error_msg -eq $true) {
throw $error_msg
}
}
# Creates property bag from DataRow
Function PropertyBagMapper($dbName,[System.Data.DataRow] $row,$args) {
if ($api -eq $null) {
$api = $args[0]
}
if ($api -eq $null) {
throw '$api is not set'
}
$bag = $api.CreatePropertyBag()
$bag.AddValue("Database", $dbName)
$columns = $row.Table.Columns;
$r = @{}
$msg="";
if ($DEBUG_MODE -eq 1) {
$msg = "Database: '$dbName'; "
}
for ($i=0;$i -lt $columns.Count;$i++) {
$columnName = $columns[$i].ColumnName;
$v = $row[$i];
if ($columns[$i].DataType -eq [guid]) {
$v = $v.ToString('b')
}
$bag.AddValue($columnName,$v)
if ($DEBUG_MODE -eq 1) {
$msg+= "$columnName : '$v'; "
}
}
LogEventDebug "Created bag with values $msg"
return $bag
}
function SqlQueryScalar($SqlConnection, $query) {
$sqlAdapter = New-Object 'System.Data.SqlClient.SqlDataAdapter' ($query, $SqlConnection)
$dataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($dataSet) | Out-Null
if(($dataSet.Tables.Count -eq 0) -or ($DataSet.Tables[0] -eq $null))
{
throw 'Can not query data from {0} database. Please check read permissions for this db.' -f $SqlConnection.Database
}
return ($dataSet.Tables[0]|Select -first 1)[0]
}
#''' 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([System.Data.SqlClient.SqlConnection]$SqlConnection, $DatabaseID)
{
$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')"
$dr = SqlConnQueryTables $SqlConnection $query | Select -First 1
$query = "SELECT d.name, d.database_id, drs.is_primary_replica AS db_is_primary_replica
, CASE WHEN d.replica_id IS NULL THEN 0 ELSE 1 END AS is_replica
, CASE WHEN drs.is_primary_replica = 1 THEN ar.primary_role_allow_connections ELSE ar.secondary_role_allow_connections END AS role_allow_connections
, CASE WHEN drs.is_suspended = 0 THEN -1 ELSE suspend_reason END AS db_suspended_state
FROM sys.databases as d
JOIN sys.dm_hadr_database_replica_states drs ON drs.database_id = d.database_id
JOIN sys.availability_replicas ar on d.replica_id = ar.replica_id
WHERE drs.is_local = 1 AND d.database_id = @p1"
$rdr = SqlQueryTables $SqlConnection $query $DatabaseID | Select -First 1
#-------------------------------------------------------------------------------
#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 -Property 'Name,State' -Namespace $namespace -ComputerName $server -Class "win32_service" -Filter "Name = '$service'" -ErrorAction SilentlyContinue
if ($obje -ne $null) {
return $obje.State
}
}
catch {
}
return "Unknown"
}
function GetSQLServiceName($InstanceName)
{
if ($InstanceName -eq "MSSQLSERVER")
{
return "MSSQLSERVER"
}
return 'MSSQL$' + $InstanceName
}