No comments
Category: SQL SERVER
SQL Server – Add New Replica in AlwaysOn Availability Group
Expand AlwaysOn High Availability > Expand Availability Groups >Expand Availability Groups Name. Right click Availability Replica.
Click Next.
Click Add Replica…
Click Connect All…
Input the instance name of your new replica. Click Connect.
Change ‘Readable Secondary’ to Yes. But if you don’t want your secondary replica to be readable, then just accept the default ‘No’.
Click Endpoints Tab just to verify that the new endpoint has been created.
Go to Backup Preference tab. I just indicated ‘20’ under Backup Priority for the new Replica. We already have an existing secondary Replica already taking the DB backups.
Choose Join Only because I already took a full backup of the primary database and restored it to the new replica WITH NORECOVERY option.
Validation
Verify Database Synchronization.
SQL Server – The Transaction Log for Database is Full Due to Replication (Error 9002 Severity 17 State 6)
We have a 3 node AAG (below is our setup). Our transaction log file has a growth limit of 60GB. I found out that this error occurred because the CDC failed. I could not start CDC and the transaction log is not truncating.
I checked the log_reuse_wait_desc and it indicated REPLICATION which means that there are committed transactions that haven’t yet been scanned by the transaction replication Log Reader Agent job for the purpose of sending them to the replication distributor or harvesting them for Change Data Capture (which uses the same Agent job as transaction replication). The job could have been disabled, could be broken, or could have had its SQL Agent schedule changed.
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = 'DB_Name'
I also ran the command DBCC OPENTRAN and saw Replicated Transaction Information.
To resolve the issue, I did the following steps.
Create a new transaction log file (it is a good thing that our currently t-log file has a growth limit else it could have occupied all the disk space.)
I started CDC and it ran successfully.
But the t-log was still not truncating. I checked the log_reuse_wait_desc again and it indicated “AVAILABILITY REPLICA” which means that the database mirroring partnership has some latency in it and there are log records on the mirroring principal that haven’t yet been sent to the mirroring mirror (called the send queue). This can happen if the mirror is configured for asynchronous operation, where transactions can commit on the principal before their log records have been sent to the mirror. It can also happen in synchronous mode, if the mirror becomes disconnected or the mirroring session is suspended. The amount of log in the send queue can be equated to the expected amount of data (or work) loss in the event of a crash of the principal.
I waited for few minutes and I checked the log_reuse_wait_desc and now I see “NOTHING” which means that SQL Server thinks there is no problem with log truncation.
SQL Server – AlwaysON Availability Group Replica is Not Synchronizing (Error: 976, Severity: 14, State: 1.)
You can lose communication between the primary and secondary replicas for some reason.
There is a policy on SQL Server to check the data synchronization state of the database replica. The policy is in an unhealthy state when the data synchronization state is NOT SYNCHRONIZING or the state is not SYNCHRONIZED for the synchronous-commit database replica.
Based on SQL Server logs, I observed the following messages:
The target database, ‘YourDatabase’, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online. (Microsoft SQL Server, Error: 976)
According to Technet, this issue can be caused by the following:
The availability replica might be disconnected.
The data movement might be suspended.
The database might not be accessible.
There might be a temporary delay issue due to network latency or the load on the primary or secondary replica.
Further information: https://technet.microsoft.com/en-us/library/hh245199.aspx
Resolution:
You can fix it, using the following T-SQL command to force the resume synchronization:
ALTER DATABASE YourDatabase SET HADR RESUME;
Or
Using SQL Server Management Studio
In Object Explorer, connect to the server instance that hosts the availability replica on which you want to resume a database, and expand the server tree.
Expand the AlwaysOn High Availability node and the Availability Groups node.
Expand the availability group.
Expand the Availability Databases node, right-click the database, and click Resume Data Movement.
In the Resume Data Movement dialog box, click OK.
SQL Server- To See Number of Connections from Each Server Connecting to the Database
SELECT DB_NAME(dbid) AS DBName,
COUNT(dbid) AS NumberOfConnections,
loginame AS LoginName,
nt_domain AS NT_Domain,
nt_username AS NT_UserName,
hostname AS HostName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid,
hostname,
loginame,
nt_domain,
nt_username
ORDER BY NumberOfConnections DESC;
SQL Server – DMV to View Missing Indexes
--DMV to view missing indexes
SELECT
*
FROM
sys.dm_db_missing_index_group_stats AS igs
JOIN
sys.dm_db_missing_index_groups AS ig ON igs.group_handle = ig.index_group_handle
JOIN
sys.dm_db_missing_index_details AS id ON ig.index_handle = id.index_handle
SQL Server – Get List of Index Information
-- Ensure a USE statement has been executed first.
SELECT [DatabaseName]
,[ObjectId]
,[ObjectName]
,[IndexId]
,[IndexDescription]
,CONVERT(DECIMAL(16, 1), (SUM([avg_record_size_in_bytes] * [record_count]) / (1024.0 * 1024))) AS [IndexSize(MB)]
,[lastupdated] AS [StatisticLastUpdated]
,[AvgFragmentationInPercent]
FROM (
SELECT DISTINCT DB_Name(Database_id) AS 'DatabaseName'
,OBJECT_ID AS ObjectId
,Object_Name(Object_id) AS ObjectName
,Index_ID AS IndexId
,Index_Type_Desc AS IndexDescription
,avg_record_size_in_bytes
,record_count
,STATS_DATE(object_id, index_id) AS 'lastupdated'
,CONVERT([varchar](512), round(Avg_Fragmentation_In_Percent, 3)) AS 'AvgFragmentationInPercent'
FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'detailed')
WHERE OBJECT_ID IS NOT NULL
AND Avg_Fragmentation_In_Percent <> 0
) T
GROUP BY DatabaseName
,ObjectId
,ObjectName
,IndexId
,IndexDescription
,lastupdated
,AvgFragmentationInPercent
ORDER BY AvgFragmentationInPercent DESC
SQL Server – Get Index Sizes
SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY [Indexsize(KB)] desc
SQL Server – View Fragmentation Level
SELECT
OBJECT_NAME(ix.OBJECT_ID) AS TableName,
ix.name AS IndexName,
ixs.index_type_desc AS IndexType,
ixs.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ixs
INNER JOIN
sys.indexes ix ON ix.object_id = ixs.object_id AND ixs.index_id = ixs.index_id
WHERE
ixs.avg_fragmentation_in_percent > 30
ORDER BY
ixs.avg_fragmentation_in_percent DESC
SQL Server – Identifying Locks
--Stored procedure to display current locks/process
exec sp_lock
--Stored procedure to display current activity/process
exec sp_who2
--DMV to view locking information
SELECT * FROM sys.dm_tran_locks
GO
--DMV to view blocked transactions
SELECT * FROM sys.dm_exec_requests WHERE status = 'suspended'
GO
--Trace flag to log deadlocks
DBCC TRACEON (1222, -1)
GO