$RemoteComputers = @(“vm1”, “vm2”)
foreach ($Computer in $RemoteComputers) {
# Fetch the last boot up time for each computer
$LastBootUpTime = Get-CimInstance -ClassName Win32_OperatingSystem -ComputerName $Computer | Select-Object @{Name=”ComputerName”;Expression={$Computer}},LastBootUpTime
# Output the result for each computer
$LastBootUpTime
}
To retrieve the last run time and duration for SQL Server backup jobs, you can use the following T-SQL query. This query provides information on the most recent backup runs:
SELECT
@@servername as ServerName,
j.name AS [Job Name],
–h.run_status,
durationHHMMSS = STUFF(STUFF(REPLACE(STR(h.run_duration, 7, 0), ‘ ‘, ‘0’), 4, 0, ‘:’), 7, 0, ‘:’),
[start_date] = CONVERT(DATETIME, RTRIM(run_date) + ‘ ‘ + STUFF(STUFF(REPLACE(STR(RTRIM(h.run_time), 6, 0), ‘ ‘, ‘0’), 3, 0, ‘:’), 6, 0, ‘:’))
FROM msdb.dbo.sysjobs AS j
INNER JOIN (
SELECT job_id, instance_id = MAX(instance_id)
FROM msdb.dbo.sysjobhistory
GROUP BY job_id
) AS l ON j.job_id = l.job_id
INNER JOIN msdb.dbo.sysjobhistory AS h ON h.job_id = l.job_id AND h.instance_id = l.instance_id
where j.name like ‘DatabaseBackup%’
ORDER BY CONVERT(INT, h.run_duration) DESC, [start_date] DESC;
To modify the FailureConditionlevel value, use the Failover Cluster Manager and follow these steps:
The failure condition level of the AG changes the failure conditions for the health check. For any failure level, if the AG element is reported unhealthy by sp_server_diagnostics then the health check will fail. Each level inherits all the failure conditions from the levels below it.Expand table
| Level | Condition under which the instance is considered dead |
|---|---|
| 1: OnServerDown | Health check takes no action if any resources fail besides the AG. If AG data is not received within 5 intervals, or 5/3 * HealthCheckTimeout |
| 2: OnServerUnresponsive | If no data is received from sp_server_diagnostics for the HealthCheckTimeout |
| 3: OnCriticalServerError | (Default) If the system component reports an error |
| 4: OnModerateServerError | If the resource component reports an error |
| 5: OnAnyQualifiedFailureConitions | If the query processing component reports an error |
Whether the diagnostic data and health information returned by sp_server_diagnostics warrants an automatic failover depends on the failure-condition level of the availability group. The failure-condition level specifies what failure conditions trigger an automatic failover. There are five failure-condition levels, which range from the least restrictive (level one) to the most restrictive (level five). A given level encompasses the less restrictive levels. Thus, the strictest level, five, includes the four less restrictive conditions, and so forth.
Important
Damaged databases and suspect databases are not detected by any failure-condition level. Therefore, a database that is damaged or suspect (whether due to a hardware failure, data corruption, or other issue) never triggers an automatic failover.
The following table describes the failure-condition that corresponds to each level.Expand table
| Level | Failure-condition | Transact-SQL Value | PowerShell Value |
|---|---|---|---|
| One | On server down. Specifies that an automatic failover is initiated when one the following occurs: The SQL Server service is down. The lease of the availability group for connecting to the WSFC cluster expires because no ACK is received from the server instance. For more information, see How It Works: SQL Server Always On Lease Timeout. This is the least restrictive level. | 1 | OnServerDown |
| Two | On server unresponsive. Specifies that an automatic failover is initiated when one of the following occurs: The instance of SQL Server does not connect to cluster, and the user-specified health check timeout threshold of the availability group is exceeded. The availability replica is in failed state. | 2 | OnServerUnresponsive |
| Three | On critical server error. Specifies that an automatic failover is initiated on critical SQL Server internal errors, such as orphaned spinlocks, serious write-access violations, or too many memory dumps generated in a short period of time. This is the default level. | 3 | OnCriticalServerError |
| Four | On moderate server error. Specifies that an automatic failover is initiated on moderate SQL Server internal errors, such as a persistent out-of-memory condition in the SQL Server internal resource pool. | 4 | OnModerateServerError |
| Five | On any qualified failure conditions. Specifies that an automatic failover is initiated on any qualified failure conditions, including: Detection of Scheduler deadlock. Detection of an unsolvable deadlock. This is the most restrictive level. | 5 | OnAnyQualifiedFailureConditions |
References:
Lease Timeouts and Health Checks in SQL Server Always On Availability Groups (sqlshack.com)
DECLARE @filename NVARCHAR(260);
— Get the default trace file path
SELECT @filename = CAST(value AS NVARCHAR(260))
FROM sys.fn_trace_getinfo(NULL)
WHERE property = 2;
— Query the default trace for file growth events
SELECT
DB_Name(databaseid)
FROM sys.fn_trace_gettable(@filename, default) AS t
INNER JOIN sys.trace_events AS te ON t.EventClass = te.trace_event_id
WHERE t.EventClass = 92
ORDER BY StartTime DESC;
To create the folder’s structure I am using the Xcopy CMD utility with the switches /t and /e
c:\>xcopy “source folder” “destination filder” /t /t
If you are seeing errors to create database on secondary replica using auto seeding means it is missing permissions.
run the below TSQL on secondary replica to grant permissions to AG group to create DB.
ALTER AVAILABILITY GROUP [<AGName>] GRANT CREATE ANY DATABASE.
https://learn.microsoft.com/en-in/azure/virtual-machines/disks-performance
https://learn.microsoft.com/en-us/azure/virtual-machines/ev4-esv4-series
https://learn.microsoft.com/en-us/azure/virtual-machines/constrained-vcpu —- to compare with old and new vm names
https://learn.microsoft.com/en-us/azure/virtual-machines/disks-types
SELECT
db_name(db.source_database_id) source_database,
db.name AS snapshot_db_name,
db.create_date,
db.is_read_only,
mf.physical_name
FROM sys.databases db
INNER JOIN sys.master_files mf
ON db.database_id = mf.database_id
WHERE db.source_database_id is not null
AND mf.is_sparse =1
ORDER BY create_date;
following is the script to view all the sessions that are waiting for page-related wait types and get information about the objects that the pages belong to.
use master
go
select
er.session_id, er.wait_type, er.wait_resource,
object_name(page_info.[object_id],page_info.database_id) as [object_name],
er.blocking_session_id, er.command,
SUBSTRING(st.text,(er.statement_start_offset/2)+1,
((case er.statement_end_offset
when -1 then DATALENGTH(St.text)
else er.statement_end_offset
END – er.statement_start_offset)/2)+1) as statement_text,
page_info.database_id,page_info.[file_id],page_info.page_id,page_info.[object_id],
page_info.index_id,page_info.page_type_desc
from sys.dm_exec_requests as er
cross apply sys.dm_exec_sql_Text(er.sql_handle) as st
cross apply sys.fn_pagerescracker (er.page_resource) as r
cross apply sys.dm_db_page_info (r.[db_id], r.[file_id], r.page_id,’DETAILED’) as page_info
where er.wait_type like ‘%page%’
go
I am testing backups on the SQL Sever 2017 version
2. but, you can take copy_only backups
3. I am able to take transactional log backups on the secondary node and it is truncating the log as well.
selectstarfromyogi 