Kill command in SQL server

When we are connection to the SQL server instance or opening the new query window, SQL server assigns a unique identification number to each connection. It is the Server Process ID and commonly known as SPID. 

 With the help of @@SPID we will get the current SPID. See below.  

 SPID 1 to 50 is reserved for SQL Server internal processes, and the user process starts from SPID 51.

We can get the all active SPID using SP_WHO or SP_WHO 2 command.

 

 Kill Command

This command is use to Ends a user process that is based on the session ID. 

Syntax

Kill <SPID>

This kill the particular session.

Example  

Here both Session ID 56 and 66 are connected. Now we are killing the session id 66 for session ID 56 window. Running the kill command.

SPID : 66 killed successfully.

If we run again we will get the below message

  

Below is the script to kill the all SPID for a database

DECLARE @str_sql NVARCHAR(1000),         @db_name VARCHAR(100),         @number_of_session_killed INT  SET @db_name = 'AdventureWorks2019'  PRINT 'START: Killing active connections to the "'       + @db_name + '" database'  -- Count Connections SELECT @number_of_session_killed = Count(*) FROM   master.dbo.sysprocesses WHERE  Db_name(dbid) = @db_name        AND dbid <> 0        AND spid <> @@spid -- Create the sql to kill the active database connections SET @str_sql = ''  SELECT @str_sql = @str_sql + 'kill ' + CONVERT(CHAR(10), spid) + ' ' FROM   master.dbo.sysprocesses WHERE  Db_name(dbid) = @db_name        AND dbid <> 0        AND spid <> @@spid EXEC (@str_sql) PRINT @str_sql PRINT 'END: Killed "'       + Cast(@number_of_session_killed AS VARCHAR(4))       + '" active connections to the "' + @db_name       + '" database'  go

Running this script    

We can't kill our own process also shouldn't kill the following processes:

Ø  AWAITING COMMAND

Ø  CHECKPOINT SLEEP

Ø  LAZY WRITER

Ø  LOCK MONITOR

Ø  SIGNAL HANDLER

KILL WITH STATUSONLY

This option tells SQL Server to terminate the specified process and provide information about the termination status without actually terminating the process. It's a way to check the status of the process before deciding whether to proceed with termination.

Syntax

KILL SPID WITH STATUSONLY; 

Running the command;

 

This gives the information only. This is not terminating the SPID 58. Still it is in active state. Let’s see.

 

Still Session ID 58 is active.

Dirty read in SQL

A "dirty read" in SQL refers to a situation where one transaction reads data that has been modified by another transaction but has not yet been committed. In other words, it allows a transaction to read uncommitted changes made by another concurrent transaction. This concept is associated with the lowest level of isolation in the SQL transaction isolation levels, known as "Read Uncommitted."

 Let’s see this example.

Here in the transaction we are updating, inserting and deleting some records but not yet committed this transaction.  

When we are updating this table in transaction this table is locked and when we are trying to fetch data from the table we will not get the data because it is waiting for the commit the transaction.  

This query is still executing. Because SQL is by default reading the committed data.

Let’s use no lock. We will get the uncommitted data. It means we will get the old data.   

Here we got the data. Even though our transaction is not yet commented.  This data is known as Dirty Read or Dirty data.

Let’s  commit the transaction.   

 Transaction committed successfully. Now we can see the updated data.  

Nolock in sql server

NOLOCK is a query hint that can be used in a SELECT statement to specify that the query should read data without acquiring shared locks on the underlying tables. This hint is also known as "read uncommitted" or "dirty read". When we use NOLOCK, it allows our query to potentially read data that is in the process of being modified by other transactions, which can result in non-repeatable reads and even the possibility of reading data that is in an inconsistent state.

Syntax

SELECT department_id,        department_name,        location_id FROM   departments WITH (nolock)

Dirty Reads: Using NOLOCK can lead to dirty reads, which means we might read data that has been modified by another transaction but hasn't been committed yet. This can result in inconsistent or incorrect data being returned.

Non-Repeatable Reads: NOLOCK can also lead to non-repeatable reads, where the same query executed multiple times within the same transaction may return different results if other transactions are modifying the data concurrently.

Uncommitted Data: we may read data that is in an uncommitted state, which can be problematic if the other transaction is later rolled back, leaving our data in an inconsistent state.

Reduced Lock Contention: On the positive side, using NOLOCK can reduce lock contention and improve query performance in situations where we are confident that dirty reads and potential inconsistencies are acceptable. It can be useful in scenarios where you are running reports or queries that don't need strict transactional consistency.

Other Isolation Levels: If we require more control over isolation levels, consider using other isolation levels such as READ COMMITTED or REPEATABLE READ, which offer a higher degree of data consistency while still allowing concurrent access.

Let’s see the example of no lock

In one session we are reading the data with no lock. In the other session we are updating the data in the table in transaction but transaction is not yet committed. In the other window we will get not modified data.

If we not using the no lock out query is waiting to complete the transaction   

Here still we have not yet committed  

This query is still executing. Because SQL is by default reading the committed data.

Let’s use no lock. We will get the uncommitted data. 

Here we got the data. Even though our transaction is not yet commented.  Now we are committing the transaction. 

 Transaction committed successfully. Now we can see the updated data.

Deploying SQL Server Database objects using Yuniql CLI

We can deploy our SQL server database objects using the Yuniql CLI.  Yuniql support SQL Server , Azure SQL Database.

Below are the folder structure.  

We must have these folders otherwise it will throw an error.

Directory / File	Usage Description	Execution
_init	Initialization scripts. Executed the first time Yuniql run is issued	Executed once
_pre	Pre migration scripts. Executed every time before any version.	Every migration run
v0.00	Baseline scripts. Executed when Yuniql run.	Executed once
_draft	Scripts in progress. Scripts that you are currently working and have not moved to specific version directory yet. Executed every time after the latest version.	Every migration run
_post	Post migration scripts. Executed every time and always the last batch to run.	Every migration run
v0.01	This directory has script of current version, which we are going to deploy.	Executed on demand when run command issued
_erase	Database cleanup scripts. Executed once only when Yuniql erase is issued.	Executed on demand when erase command issued
Dockerfile	A template docker files to run your migration. Uses docker base images with Yuniql installed.	Executed on docker build
README.md	A template README file.
.gitignore	A template git ignore file to skip yuniql.exe from being committed.

 Let’s see here we have some script we need to deploy this script using Yuniql CLI

We have a database “Test_DB” where we need to deploy the build. 

In this example we are going to create two tables and insert some records in this table.

We have kept our script in the v0.01 directory.  

_sequence.ini file :

This file has the sequence of the file execution. Based on the  sequence list our script will be running.

  

In this example we are running our script on below sequence

·         DDL_employees.sql

·         DDL_departmnet.sql

If we will not provide the _sequence.ini file the it will run based on the alphabetically like below

·         DDL_department.sql

·         DDL_employees.sql

After DDL script we want to run our DML script. I means insert script. We have kept our DML script into the _post directory.  

 We don’t have any pre build deployment script such as connection close , due to that we have kept our _pre directory as empty. 

In _erase directory we have rollback script.  

We have table drop script into this directory.

Let’s run the Yuniql CLI.

Open the CMD and pointing where we have kept the Yuniql exe.  

Below is the command to deploy the script in SQL Server

Yuniql run -p "C:\Yuniql\DBOBJECTS" -c "Server=DESKTOP-DEF14CQ\SQLSERVER2019;Database=Test_DB;User Id=SA;Password=*****;TrustServerCertificate=true" -a "false"

                               
Yuniql run CLI Arguments

-a

--auto-create-db

Runs migration using connection string from environment variable YUNIQL_CONNECTION_STRING

Auto-create target database if not exists

-c "<value>"

--connection-string "<value>"

Runs migration using the specified connection string

-p c:\temp\demo

--path c:\temp\demo

Runs migration from target directory

-t v1.05

--target-version v1.05

Runs migration only up to the version v1.05 skipping v1.06 or later

-k "<key>=<value>,<key>=<value>"

--token "<key>=<value>,<key>=<value>"

Replace each token in each script file. This is very helpful when you have environment specific sql-statements such as cross-server queries where database names are suffixed by the environment.

--delimiter ";"

Runs migration using ; as CSV file delimiter

-d

--debug

Runs migration with DEBUG tracing enabled

--platform "sql server"

Runs migration in sql server database.

 

Running this commend  

Scripts will run as listed in the content of _sequence.ini file. Any scripts not listed in the manifest will be skipped and will not be committed in the version where it is placed. Skipped scripts can only be executed by moving them to the next version.

Build deploy successfully deploy.

See the objects in the Database.

  

Employees and Departments table created successfully. _yuniql_scema_version table I created by default when we are running Yuniql first time. Yuniql use this table for version control. 

Currently we have deployed the v0.01 version. If we are trying to run this version again yuniql will not allow running the same version again but _pre, _draft and _post script will run again. 

Rollback Run 

In case we want to rollback we need to run the erase command. I will run the erase directory scripts.

Yuniql erase -p "C:\Yuniql\DBOBJECTS" -c "Server=DESKTOP-DEF14CQ\SQLSERVER2019;Database=Test_DB;User Id=SA;Password=*******;TrustServerCertificate=true" --force --debug

 

Objects have been drop. See in the database. 

Tables have been dropped.

See the yuniql_schema_version table.  

Drawback of the erase is , it will not remove the current version from the yuniql_schema_version table. We need to provide delete script in the _erase directory to delete the current version in case of rollback.

 

Now the _erase directory having below files.  

Now running the build and after that I will run the rollback.  

See the yuniql_schema_version table.  

Current version has deleted.