July 5, 2022 at 9:22 am
hi all
We've ventured into the world of AAG's as we have now moved across to SQL2019. We have a Primary node and two secondary nodes.
We have a training database that is autoamtically created from the main production database. This was initially added to the AAG manually and syncs fine.
What we are now trying to do is to automate the refreshing of that db, through a SQL Agent job. It all works apart from dropping the database on the secondary nodes using...
EXEC [NameofLinkedServer].tempdb.dbo.sp_executesql N'DROP DATABASE Test;';
which works fine when I run it from a query window, but not when run as the service account that runs SQL Server Agent (which is a sysadmin). The Job History shows the following error...
Message
Executed as user: DOMAIN\ServiceAccount. Cannot drop database "Test" because it is currently in use. [SQLSTATE 42000] (Error 3702). The step failed.
We've run a who is script and it shows as no connections but does show the db as Restpring as it is now out of the AAG from an earlier step...
I've tried pushing it into single user mode, but that fails as well...
I would appreciate any thoughts....
July 5, 2022 at 6:50 pm
I am assuming you already have the code available to determine which node is primary and which nodes are secondary. To drop the database on a secondary from a process running on the primary I would use something like:
If @primaryNode = 'Node1'
Begin
Execute ('Drop Database Test') At Node2;
Execute ('Drop Database Test') At Node3;
End
If @primaryNode = 'Node2'
Begin
Execute ('Drop Database Test') At Node1;
Execute ('Drop Database Test') At Node3;
End
If @primaryNode = 'Node3'
Begin
Execute ('Drop Database Test') At Node2;
Execute ('Drop Database Test') At Node1;
End
The linked server permissions will need to be setup correctly for that process. The login executing that code would need to be added to the linked server and mapped to a remote login that has the necessary permissions to drop the database.
With that said - I do this process from an SSIS package running on a separate node. It is much easier to manage that way and I don't have to worry about which node the code is running on since it runs from a separate instance not participating in the AG.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 6, 2022 at 9:38 am
Thanks for that! Annoyingly I managed to get it working last night after checking something I had been assured was the case! When I went to check the security on the account on the secondary node, I discovered it actually wasn't on there.... ....must learn to never assume!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply