Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Archive for June, 2015

AlwaysOn AG group status check

Posted by Simon Cho on 06/15/2015

SELECT replica_server_name
, d.role_desc
, d.connected_state_desc
–, endpoint_url
–, availability_mode_desc
–, failover_mode_desc
–, session_timeout
–, backup_priority
–, secondary_role_allow_connections_desc AS Secondary_Readable
, Pri_Check.pri_Status
–, Pri_Check.Sec_Status
–, Pri_Check.Sync_Status
, d.operational_state_desc
, d.recovery_health_desc
, d.synchronization_health_desc AS Sync_Status
, d.last_connect_error_description AS ErrorMsg
, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), d.last_connect_error_timestamp) AS ErrorDateTime
FROM sys.availability_replicas r
OUTER APPLY (
SELECT 1 AS IsPrimary
, c.ip_address AS listnerIP
, c.state_desc AS listnerStatus
, a.primary_recovery_health_desc AS pri_Status
, a.secondary_recovery_health_desc AS Sec_Status
, a.synchronization_health_desc AS Sync_Status
FROM sys.dm_hadr_availability_group_states a
JOIN sys.availability_group_listeners B
ON A.group_id = B.group_id
JOIN sys.availability_group_listener_ip_addresses c
ON b.listener_id = c.listener_id
WHERE primary_replica = r.replica_server_name
) Pri_Check
JOIN sys.dm_hadr_availability_replica_states d
ON r.replica_id = d.replica_id
/*
WHERE d.role NOT IN (1,2) — 1:Primary, 2:Secondary, 0:Resolving
OR d.operational_state <> 2 –0 = Pending failover, 1 = Pending, 2 = Online, 3 = Offline, 4 = Failed, 5 = Failed, no quorum
OR d.recovery_health <> 1 –0:In progress. At least one joined database has a database state other than ONLINE ( database_state is not 0).1- Online. All the joined databases have a database state of ONLINE ( database_state is 0).
OR d.synchronization_health <>2 — 0 = Not healthy. At least one joined database is in the NOT SYNCHRONIZING state.
— 1 = Partially healthy. Some replicas are not in the target synchronization state: synchronous-commit replicas should be synchronized, and asynchronous-commit replicas should be synchronizing.
— 2= Healthy. All replicas are in the target synchronization state: synchronous-commit replicas are synchronized, and asynchronous-commit replicas are synchronizing.
OR d.connected_state <> 1 –0 Disconnected. The response of an availability replica to the DISCONNECTED state depends on its role, as follows:
— On the primary replica, if a secondary replica is disconnected, its secondary databases are marked as NOT SYNCHRONIZED on the primary replica, which waits for the secondary to reconnect.
— On a secondary replica, upon detecting that it is disconnected, the secondary replica attempts to reconnect to the primary replica.
— 1 Connected
OR d.last_connect_error_number IS NOT NULL
*/

Posted in Common | Leave a Comment »

SQL 2014 service pack for SP1 or CU7?

Posted by Simon Cho on 06/15/2015

https://support.microsoft.com/en-us/kb/2936603

SQL service pack SP1 is announced.

https://support.microsoft.com/en-us/kb/2936603

And CU7 and SP1 both of the latest build for now.

 

The problem is, SP1 has higher version. But, it looks not containing CU6 and CU7.

CU6 and CU7 include very critical KB as well.

You may not want to apply SP1. Please check CU6 and CU7 first.

 

 

ServicePack VSTS bug number KB article number Description Fix area
CU6 4067265 3016165 FIX: Arithmetic overflow error occurs when you add manually initialized subscriptions for publication in SQL Server SQL service
CU6 4067300 3021757 FIX: Duplicate sequence value is generated when you run sp_sequence_get_range in parallel with NEXT VALUE FOR function SQL service
CU6 3986465 3024815 Large query compilation waits on RESOURCE_SEMAPHORE_QUERY_COMPILE in SQL Server 2014 SQL performance
CU6 3915402 3025845 FIX: The transaction isolation level is reset incorrectly when the SQL Server connection is released in SQL Server 2014 SQL service
CU6 4067260 3026082 FIX: SOS_CACHESTORE spinlock contention on system table rowset cache causes high CPU usage in SQL Server 2012 or 2014 SQL service
CU6 4067263 3026083 FIX: SOS_CACHESTORE spinlock contention on ad hoc SQL Server plan cache causes high CPU usage in SQL Server 2012 or 2014 SQL service
CU6 3856439 3029762 FIX: Access violation occurs when you delete rows from a table that has clustered columnstore index in SQL Server 2014 SQL service
CU6 4067312 3029825 FIX: DBCC CHECKDB and DBCC CHECKTABLE take longer to run when SQL CLR UDTs are involved in SQL Server 2012 or SQL Server 2014 SQL performance
CU6 3885366 3030041 FIX: Error occurs when you connect to the database engine after you install CU4 for SQL Server 2014 Setup & Install
CU6 3749961 3030619 FIX: Incorrect data returned when you use DATE data type as a qualifier in a query in SQL Server 2014 SQL service
CU6 4072235 3034615 FIX: Memory leak occurs when you run DBCC CHECKDB against a database in SQL Server 2014 SQL service
CU6 4045776 3034679 FIX: AlwaysOn availability groups are reported as NOT SYNCHRONIZING High Availability
CU7 4210782 3042544 FIX: A query that requires nested loops join takes longer to complete in SQL Server 2014 SQL performance
CU7 4326599 3044958 FIX: Rollback recovery on a snapshot fails when you run DBCC CHECKDB and then SQL Server shuts down unexpectedly SQL service
CU7 4326597 3032476 FIX: Memory leak in USERSTORE_SCHEMAMGR and CPU spikes occur when you use temp table in SQL Server 2012 or 2014 SQL service
CU7 4336264 3037624 FIX: Complex parallel query does not respond in SQL Server 2012 or SQL Server 2014 SQL performance
CU7 4265652 3042370 An AlwaysOn secondary replica crashes or raises error 3961 when the AlwaysOn database has CLR UDT in SQL Server 2014 SQL service
CU7 4326600 3042135 FIX: Access violation and “No exceptions should be raised by this code” error occur when you use SQL Server 2012 or SQL Server 2014 SQL service
CU7 4056944 3048752 FIX: A SELECT query run as a parallel batch-mode scan may cause a deadlock situation in SQL Server 2014 SQL service
CU7 4302739 3052404 FIX: You cannot use the Transport Layer Security protocol version 1.2 to connect to a server that is running SQL Server 2014 SQL connectivity
CU7 4329649 3048856 FIX: Error 3624 occurs when you execute a query that contains multiple bulk insert statements in SQL Server 2014 SQL service
CU6 4067280 3011465 FIX: Sequence object generates duplicate sequence values when SQL Server 2012 or SQL Server 2014 is under memory pressure SQL service

 

Posted in Common | Leave a Comment »