Killing User Connection(session) connected to the Database

By -

DECLARE @SPID varchar(8000)
DECLARE @Conntions_Killed smallint
DECLARE @DBName varchar(100)

SET @SPID = ''
SET @Conntions_Killed = 0;
SET @DBName = 'test' -- Pass the Database Name.

SELECT @SPID=coalesce(@spid,',' )+'KILL '+convert(varchar, spid)+ '; '
FROM MASTER..SYSPROCESSES WHERE dbid=db_id(@DBName);

Print @SPID;

IF LEN(@SPID) > 0
BEGIN
EXEC(@SPID);

SELECT @Conntions_Killed = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

END

Comments

Post a Comment

Popular posts from this blog

Azure Data Fundamentals | DP-900 | what you need to know

By -
Image
I just completed the certification- Microsoft Certified: Azure Data Fundamentals Note: If you are looking for tutorials, then the official videos at Microsoft DP-900 site are more than enough. You don't need to buy any other tutorials from anywhere else. Here's all you need to know to clear this: Skills measured Describe core data concepts (25-30%) Identify considerations for relational data on Azure (20-25%) Describe considerations for working with non-relational data on Azure (15-20%) Describe an analytics workload on Azure (25-30%) The exam is pretty basic, so you need to be clear about all fundamentals of each term. Have a look at the study guide for detailed topic list and some sample questions: Download the DP-900 study guide to help you prepare for the exam View free sample questions to help prepare for this exam    My Notes- Core Data workloads type of data data processing solutions batch vs stream Data Da...
Read more

Leetcode - Blind75 - P1 - Arrays

By -
Image
"Arrays are like the socks in your drawer; there’s always one missing when you need it the most!" We covered the arrays(lists) in python earlier in this article . Building upon that, let's take a look at the list of Blind75 questions that are based on arrays and related concepts. This blog is part of my leetcode journey blog and you can access the complete list here . Mind Mapping the Algorithmic Challenge - Blind75 Why Every Developer Should Use LeetCode Leetcode - Blind75 - Easy Arrays are a fundamental concept in programming, serving as a cornerstone for storing and manipulating collections of data. They are defined as a linear data structure that holds elements of the same data type in contiguous memory locations. The simplicity of arrays lies in their ability to access elements directly through indices, which are typically zero-based, meaning the first element is at index 0. Here's a quick overview of Lists/Arrays in python: In the context of coding in...
Read more

View or Configure the backup compression default Server Configuration Option

By -
use sp_configure to configure the server instance to create compressed backups by default. USE AdventureWorks2012; GO EXEC sp_configure 'backup compression default', 1 ; RECONFIGURE WITH OVERRIDE ; GO By this, all further backups that you perform will be done with compression by default. Then there is a very good backup scheme that I've developed. Here it is- DECLARE @dbName varchar(100); DECLARE @backupPath varchar(100); DECLARE @backupQuery varchar(500); set @dbName = N'database_name' EXECUTE master.dbo.xp_create_subdir N'\\your\path\to\the \folder\database_name' set @backupPath = '\\your\path\to\the \folder\database_name\' set @backupQuery =  'backup database ' + @dbName + ' to disk = ''' + @backupPath + @dbName + '_[' + REPLACE( convert(varchar, getdate(), 109), ':', '-') + '].bak''' print @backupQuery EXEC (@backupQuery)
Read more