There are many times, we face the LSN mismatch issue in alwaysON and other HA technologies. It is a bit hard to find the missing transaction log backup to apply. Since, there are hundreds of thousands log generated, depends on the transaction log frequency and it can be run in any secondary alwaysON database server. Think about “the VLDB” and “the backup is in different data center” and database are out of sync in DR site because of LSN mismatch. For VLDB 8 TB database, we cannot take a full or differential backup to fix this. Since, it will take more and more time. Backup is in different data center…
-
-
AlwaysON database NOT SYNCHRONIZED and RECOVERY PENDING
AlwaysON database NOT SYNCHRONIZED and RECOVERY PENDING Check the AG database by expanding group and resume it. A quick fix: Suspend and resume it. select name,role_desc,operational_state_desc,recovery_health_desc, synchronization_health_desc,getdate() from sys.dm_hadr_availability_replica_cluster_states a join sys.dm_hadr_availability_replica_states b on a.group_id =b.group_id join sys.availability_groups_cluster c on b.group_id =c.group_id where b.synchronization_health_desc<>'HEALTHY' -- Get the replica ID and pass it select 'alter database ['+database_name+'] set HADR suspend' from sys.dm_hadr_database_replica_cluster_states where replica_id='' select 'alter database ['+database_name+'] set HADR resume' from sys.dm_hadr_database_replica_cluster_states where replica_id='' Bug: I have had one more issue Always ON database went into In Recovery mode, it was 10 GB database and I have read the log no improvement and rollback percentage etc. I had left…
-
AlwaysON move database without breaking HADR
Move database without breaking alwaysON This post is going to show the database movement from one drive to another drive, without breaking the database from alwaysON configuration. An application has created the many databases to both primary and secondary replica servers to the default location of C drive. There are methods like detach/attach, backup/restore & alter database. For alwaysON HADR servers, the best method is Alter database. Since it is in the mirror/sync mode. Detach/attach will not work and backup/ restore, we need to break databases from the HADR. Note: It’s a two node synchronous replica, if you have more replica, you should plan each well. If you have…
-
Performance Tuning step by steps Series – SQL Main Part
Performance tuning is a tricky part. We need to analysis all points of view. Sometimes it is an easy fix and can be solved in a minute and sometimes it take a day or more to find and fix. I received many emails for performance tuning help. It has a variety of questions, that how can we tune a query and how to find out what is going on in the server, since my application team is does not give a slow running code. The query only slowdown in some time period. My server is banging all time above 95% of resources. Nowadays, every business has a tool that…
-
Whoisactive capturing a SQL server queries Performance Tuning
Whoisactive stored procedure is a powerful script to capture the SQL server queries with tons of information. It’s written by Adam Machanic. It makes DBAs job easier. It’s a great script with lots of parameter and I am going to show, which are all more important. Note: It been moved to – http://whoisactive.com/ Run the stored procedure without any parameter parameters You can run the procedure without any parameter. It’s a default mode. Exec [sp_WhoIsActive] I used two table and method to load a data into a table – lightweight (minimal parameter) and heavyweight. Steps to load: Download the SP from website Create a SP in any of the DB…