• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 76
  • Last Modified:

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.
0
Member_2_7964709
Asked:
Member_2_7964709
  • 3
  • 3
2 Solutions
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
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_7964709Author 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
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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 & ArchitectCommented:
>> 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now