• AlwaysON

    AlwaysON database NOT SYNCHRONIZED and RECOVERY PENDING

    AlwaysON database NOT SYNCHRONIZED and RECOVERY PENDING   I have recently come across an issue that, one of my alwaysON secondary replica databases are went into “NOT SYNCHRONIZED and RECOVERY PENDING” state. It is a geo cluster alwaysON with 4+2 nodes configured in both synchronous and asynchronous mode.  The issue is when the database removed from the primary replica, with the secondary disconnection the higher database IDs on the secondary went into “NOT SYNCHRONIZED and RECOVERY PENDING” state, but the lower database IDs are good and synchronous state only. I just checked with one of the Microsoft MSFT and he said it considers as a bug. “Since, it occurs a…

  • AlwaysON,  DR/HA

    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…

  • DBA,  Performance

    Performance Tuning Series – 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…

  • DBA,  Performance

    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. Run the stored procedure without any parameter parameters You can run the procedure without any parameter. It’s a default mode. Exec [sp_WhoIsActive]   Lightweight parameters @get_plans = 1 – It will show the execution plans of the running query. @get_task_info = 2 – It will show the wait type of the query. Ex: Parallel CXPACKET waits. With the additional info like, 3 times…

  • Indexes

    How important is clustered index for a table

      I am not sure, how to name this topic. May be, start by following questions.   How important and effective is clustered index for a table? How to solve the heap fragmentation? Why my query is running slow most of the time for this particular table? Are the primary key and clustered index are same?   There are developers still they understand that, we must need a primary key to create a clustered index. No, that’s not. Both are different used for different purpose. The primary key is to enforce the business logic. I.e. enforce the uniqueness. By default it will create a clustered index, if we are not…

  • DBA

    Lost Administrator SA password SQL server 2012

    There are many blog posts talking about, how to recover “SysAdmin” password in case if it’s lost OR the server is new to you and no idea that has own and access to it. In my case the second option J In my opinion, it’s very easy to work on GUI instead of command line CMD 🙂 I used a SSMS.   Steps: 1. Stop the SQL server agent 2. Take the SQL server in single user mode, earlier version, it’s a bit hard All programs –> SQL server 2012 –> Configuration Tools –> SQL server configuration manager Stop the agent and take the SQL server into single user mode.…