question

rohits avatar image
rohits asked

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
sqlserver 2008r2sessionskill
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
Just for my clarification, so you failed over and now am attempting to configure log shipping back to what is now the secondary server?
0 Likes 0 ·

3 Answers

·
rohits avatar image
rohits answered
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
4 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
Wait. You are attempting to log ship the master database? Why are you trying to log ship Master? You can't put the Master into NORECOVERY as it's needed, to well, run the instance.
0 Likes 0 ·
rohits avatar image rohits commented ·
Yes, I am configuring the logshipping failover and Failback. My problem is I am not able to take master database in restoring state. Can you help??
0 Likes 0 ·
JohnM avatar image JohnM commented ·
Yeah, that's not going to work. Log shipping is essentially a one way street UNTIL you fail over. Once you fail over, then you'll need to re-configure log shipping to ship back to the other direction. Unless I completely misunderstand. Why would you try to log ship Master?
0 Likes 0 ·
JohnM avatar image JohnM commented ·
Also, the error makes sense in that it has to take an exclusive lock on the database in order to get it into "Recovery" mode so if it's online and servicing connections, I doubt that you'd ever get an exclusive lock on Master. Plus I wouldn't try to log ship that.
0 Likes 0 ·
rohits avatar image
rohits answered
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.
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
And the requirement specifically states to log ship Master? Or two other named user databases?
0 Likes 0 ·
rohits avatar image
rohits answered
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
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.