Always ON Group Failover not happening in SQL Server 2017

by

We had one activity planned which was related to windows hostname and SQL server name change. DB Servers Setup Information: It was a 2 node windows cluster, Node1 & Node2. SQL server Always On was configured on this setup. 4 Synchronous Always On Groups (AG1, AG2,AG3,AG4) were setup and Node1 was acting as Primary. Always … Read more

LOG_REUSE_WAIT_DESC shown as Availability_Replica even though DB is removed from AG

by

Issue: One of our customers have a AG server with 2 nodes and 4 databases added to availability group. He got an alert saying that log file is full for one of the bigger database.

Read more

Cannot drop server ‘repl_distributor’ because it is used as a Distributor in replication

by

I am writing this post as I recently faced error mentioned in title while I was migrating all objects from one SQL instance to another SQL instance as part of decommission plan of source instance. On source server, there was transactional publication was configured with local distributor. If you restore a replicated database to another … Read more

Avoid deadlock scheduler situation in AlwaysOn setup

by

Scenario Kindly consider below AlwaysOn setup between three servers. Server A — Primary ReplicaServer B — Secondary synchronous commit replicaServer C — Secondary asynchronous commit replicaEach server is having 8 logical CPUs thus having 576 max worker threads.SQL instance is having 150 user databases across 5 AGs with 30 databases in each AG. With above … Read more

Lost permissions on subscriber objects after applying snapshot

by

Symptoms: You are using SQL Server replication (transactional/merge/snapshot) subscribers for transactions. You have provided users some object level permissions on specific objects (tables/stored procedures etc.) at subscriber database. After applying the snapshot users complaint about missing permissions on subscriber database. Cause: In SQL server replication, while applying snapshot default behavior is to drop existing objects … Read more

HIGH CPU usage due to RESOURCE MONITOR command

by

On one of SQL 2016 server HIGH CPU usage alert was received. It was constantly above 95% and SQL server was highest CPU consumer. As checked with sp_who2, Resource Monitor command running under system SPID was biggest CPU consumer and its CPU usage was not coming down.

Execute permission was denied on object ‘agent_datetime’

by

Execute permission was denied on object ‘agent_datetime’

Finding SQL Server Product Key

by

Recently, we had to retrieve SQL Server License Keys for existing SQL Server setups due to auditing requirements. Existing database server setups spanned from SQL 2008 to SQL 2019. PowerShell Script mentioned in below article could be used to retrieve product key for SQL server 2008 , 2008 R2 & 2012. https://mspowershell.blogspot.com/2010/11/sql-server-product-key.html We made small … Read more

SQL Server Performance Tuning : Ways to do Index Optimization

by

In our last post we discussed an approach on what we should do to find whether there is a memory issue or a way to identify if there is a memory crunch which is causing bad SQL performance. However, not always the thing we see on the surface is the root cause. Though there may … Read more