SQL server connection request Handling

I have one SQL server 2008 R2 Cluster instance running and there are 2 databases aligned with the instance. I require to take tail log (transaction log) backup of the databases.
While taking the Backup of Transaction log (go to Task-- >Backup database--> Options---> Go under Transaction Log section--> Select Backup the Tail of the log and leave the database in the restoring state), it says that the users are connected with the data base. In order perform the step, we need to disconnect all the user connections already established with the SQL instance and then perform the Tail log backup activity. But the problem is , the users get connected automatically with a periodicity of 10 seconds as these are Client applications that are  establishing the connections with SQL instance and I do not have control over that. Can any one suggest a possible way to achieve my objective.
I am using SQL server 2008 R2 version of SQL. The OS is Windows server 2008 R2.  Please note that the client applications can not be disconnected for a long span of time (maximum 20 mins) and needs to be reconnected automatically connected after 20  mins. So I require some sort of mechanism through which SQL server instance refuses any inbound connection requests coming to it for 20 mins.
Member_2_7964709Senior EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Yes, Ideal way would be to stop your application server so that it won't establish connection to your Database once every 10 seconds.
if that is not possible, then you can try the below code after changing the database name and backup file path
declare @db_name varchar(20) = 'Test'
declare @sql nvarchar(100);

DECLARE Kill_cursor CURSOR FOR  
SELECT 'Kill ' + cast(spid as varchar(10))
FROM sys.sysprocesses
where dbid = db_id(@db_name);  
OPEN Kill_cursor;  
FETCH NEXT FROM Kill_cursor into @sql;  
WHILE @@FETCH_STATUS = 0  
   BEGIN  
	  exec sp_executesql @sql 
	  FETCH NEXT FROM Kill_cursor into @sql;  
   END;  
CLOSE Kill_cursor;  
DEALLOCATE Kill_cursor;  
GO  
-- Take Tail Log backup
BACKUP LOG [Test] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\Test.bak'
WITH  NO_TRUNCATE , NOFORMAT, NOINIT,  NAME = N'Test-Tail Log Backup', SKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 10
GO
-- Restore database from Tail Log Backup and make it available for use
RESTORE LOG [Test] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\Test.bak' WITH  FILE = 3,  NOUNLOAD,  STATS = 10
GO

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
You don't need to kill processes one by one. Also the above process will for sure take more than 10 seconds so it won't work.
Here's an alternative script that you can use:
-- Do not connect to your database as you will disconnect everybody. Use master db instead.
USE master; 
GO
-- Disconnect all users from the database
ALTER DATABASE databaseName
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
-- Run the Backup command now that nobody is connected to the database
BACKUP LOG databaseName
TO DISK = '...'
....
GO
-- Now you can allow users to connect to the database again
ALTER DATABASE databaseName
SET MULTI_USER;
GO

Open in new window

0
Member_2_7964709Senior EngineerAuthor Commented:
Got some workaround that work for me. If I disable the TCP/IP from SQL server Configuration manager--->SQL server Network manager, then the SQL server instance is not accepting any inbound connection request.To disable the TCP/IP, we need to restart SQL server process.
After that , I have killed all the connections by some script and then do tail log backup activity. This works for me.
Thanks for support Raja Jegan R.
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Also the above process will for sure take more than 10 seconds so it won't work.

Vitor, I've faced few scenarios like this before and after you set database to Single user mode, chances of application connection getting established is high and hence I haven't been successful for my usage.
However, with the script I've given above, it will definitely work 90% in the first try(in few cases I was successful in the second execution)

>> To disable the TCP/IP, we need to restart SQL server process.

I wasn't aware that you can restart the SQL Server Services for this activity. Instead of that Stopping Application Server, taking Tail Log backup and starting it back would be easier.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Raja, issue with the KILL command is that will always try to rollback the command that's being executed by the process. Depending on what's doing on that moment, the Rollback can take milliseconds or hours. That's what I'm calling attention for.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> That's what I'm calling attention for

Yes, that's correct and it depends upon system to system..
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Any of the above comments can be used as solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.