How to permanently kill multiple sessions to single database
Hello, I am stuck with one of our application which keep requesting sql server database every 10 sec and form 300-400 connections(300-400 are the no of clients). I was trying to configure the Log shipping and Fail-over scenario. I configured the log shipping successfully, but during configuration of reverse log shipping , I was unable to take the tail log backup of one database due to database is in use. I checked the activity monitor and 400 connections were made to the database. I killed all the connections to the database through script:- ***USE [master]; GO DECLARE @Kill VARCHAR(8000) = ''; SELECT @Kill = @Kill + 'kill ' + CONVERT(VARCHAR(5), session_id) + ';' FROM sys.dm_exec_sessions WHERE database_id = DB_ID(' '); EXEC sys.sp_executesql @Kill;*** But after 10 sec all my connections are again established. So I am unable to take the tail log backup of database and configure reverse log shipping. Can someone please help me on this?? Or can someone provide the solution to permanently disconnect the users from the particular database. Thanks in advance. Rohit
Hello JohnM, Thanks for your reply. Yes I successfully configured logshipping from master to standby database. But during configuration of reverse log shipping I got the error of database is in use, in Master database. I was taking tail log backup of Master database and leave the database in restoring state. Now when I killed all connected sessions to the database, they again connected back in 10 seconds. Hope now I am clear. Rohit
I have requirement from the application OEM to configure log-shipping and reverse log-shipping. I have 2 databases in the same SQL server instance, I am able to configure the reverse log shipping in one database, while in another I am facing this issue.
Sorry, in my comment master means primary database. I didn't get your point when you asked "Wait. You are attempting to log ship the master database?". Sorry for creating confusion. Now my issue is resolved. I have disabled the TCP/IP protocol from SQL Server configuration manager, and now no connection requests to my database. I am able to take the tail log backup of database and configure the logshipping failover and failback option. Thanks for your timely response. And sorry for inconvenience caused by my answers. Rohit