• AlwaysON,  DBA

    AlwaysON SQL server database blocking SSISDB Active Operations

    AlwaysON SQL server database blocking I had two blocking case on one of my alwaysON databases. Backup is third party tool and it got blocked by each other sessions. SSIS package deployed in SSIS folder and it run long and had blocking as well.   The first one, tried to kill the session ID, it went in to rollback state and it took more than half day, no response. Fix: Just suspend the database in the primary AG this will remove the killed SPID. The second one, we can look into the Active Operations of packages. Integration service catalog – right click SSISDB – Active Operations  

  • DBA

    No sysadmin access Microsoft SQL server error 18461

    Login failed for user reason single user mode only one administrator can connect at this time Microsoft SQL server error 18461   You might get a situation that no sysadmin access to SQL server and server installed by someone. Breaking SQL server and adding SA sysadmin of yourself by SQLCMD. You should be OS administrator.   First find out ‘Binn’ folder where it is installed C or D drive. Better search in my computer. Open a CMD with administrator Type: cd /d E:\Program Files\Microsoft SQL Server\130\Tools\Binn (OR) 1.Stop the SQL server service 2.Start the service in Single user mode by adding ‘;-mSQLCMD’ to the parameters from SQL server configuration manager…

  • DBA

    The Cluster service failed to bring clustered service or application completely online or offline. One or more resources may be in a failed state.

    Recently, one of alwaysON cluster had issue, cause of file share witness box down and we brought up the box online.   Errors: The Cluster service failed to bring clustered service or application completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered service or application. Cluster resource ‘File Share Witness’ of type ‘File Share Witness’ in clustered role ‘Cluster Group’ failed Based on the failure policies for the resource and role, the cluster service may try to bring the resource online on this node or move the group to another node of the cluster and then restart…

  • DBA

    How to use server side trace to capture running query T SQL

    Capturing queries by using server side trace In general, DBA will have a report most of time the server and application web is running slow. You have to find yourself what query is running slow and benchmark and tune it. Profiler trace is good to capture the running code. But it is a GUI and we have to start, capture and stop it. This will also make some performance slowness to the server and you it is hard to read and store data. The server side trace is another option and it is a lightweight and T-SQL code, we can create a stored procedure and schedule it on SQL server…

  • AlwaysON,  DBA

    Multi subnet sql alwayon cluster not connecting to sql database when application or alwayon failover

    Multi subnet SQL alwayon cluster not connecting to SQL database when application server restarts or alwayon failover. In multi subnet we will have more than two ips, the active IP will be online and other ips will be offline. https://techcommunity.microsoft.com/t5/SQL-Server-Support/Connection-Timeouts-in-Multi-subnet-Availability-Group/ba-p/318334 This Microsoft link will have more information. I just used this to give a workaround for one of our application. The application has MultiSubnetFailover=True. Open powershell as administrator. Run the following and pass the resource name. Get the resource name, it could be AG group name_Listener name – Get-ClusterResource Check the ‘RegisterAllProvidersIP’ value, if it is 1 change to 0 Get-ClusterResource “Cluster Resource Name” | Get-ClusterParameter   Change the ‘RegisterAllProvidersIP’…