Ravi Agarwal Recently one of our customer called in for help as his database got stuck in SINGLE_USER mode after he tried to copy database using Copy Database wizard. Using detach and attach method in database copy wizard does not allow any user connections to the source database thus puts the database in single_user mode in initial … Read more
Recently I encountered one issue where OLA Hallengren transaction log backup job was not deleting old backup files for one particular user database. While for other databases it was deleting the files as per retention period mentioned against “CleanupTime” parameter. “CleanupTime” parameter was mentioned as 48 hours but there were log backup files older than … Read more
Recently while working on one alert, when I logged onto the server and connected SQL instance, noticed that on one of secondary replica in AlwaysOn availability group was having databases in “Synchronized/In Recovery” mode. This is not the usual status for secondary replica databases. Replica was configured in synchronous commit mode and databases were not … Read more
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
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
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
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’
Issue: In an always on setup, CDC jobs should be created on all the replicas of always on availability group. Sometime you may find that CDC capture and cleanup job is running successful on some of replicas but failing on other replicas with below error: Error message:Executed as user: Domain\user. Invalid object name ‘msdb.dbo.cdc_jobs’. [SQLSTATE … Read more
Scenario: Consider we have a primary data center where we want to configure HA solution using Always On availability group and want to configure log shipping to DR site as a DR solution. In this post we will discuss on how to configure log shipping for a database involved in AG and some limitations and … Read more