SQL SERVER – SQL Agent Job and Backslash – Strange Behavior

July 18, 2019

Today, I am going to discuss a very interesting scenario which I encountered while working with one of the largest European financial technology organizations Comprehensive Database Performance Health Check earlier last week. I would be open with you that I have not heard about this behavior since I started my journey with SQL Server. Today’s blog post is about SQL Agent Job and Backslash.

I was surprised to see this behavior. Here are the steps to reproduce this behavior.

Step 1

Create a database and table in it.

CREATE DATABASE MyTestDB
GO
USE MyTestDB
GO
CREATE TABLE ToBeDeleted(iID INT, vPath VARCHAR(100))
INSERT INTO ToBeDeleted VALUES(1,'\\FileServer001\MyFolder\')
INSERT INTO ToBeDeleted VALUES(2,'\\FileServer001\MyFolder2\')
-- verify the rows
Select * from ToBeDeleted

Step 2

Create a job in SQL Agent having below T-SQL script. Job name given was “MyJob”.

DELETE FROM MyTestDB..ToBeDeleted
-- Delete from table where path is \\FileServer001\MyFolder\
WHERE vPath= '\\FileServer001\MyFolder\'

Step 3

Run the job manually.

What do you expect to happen when job executes? It should delete ONE row where the path is matching?

You would be surprised to see actual results. You would see BOTH rows deleted from the table.

Here is the proof.

SQL SERVER - SQL Agent Job and Backslash - Strange Behavior Backslash

SELECT * FROM MyTestDB..ToBeDeleted
GO
EXECUTE msdb..sp_start_job 'MyJob'
GO
WAITFOR DELAY '00:00:05' -- waiting for 5 seconds to job to complete.
GO
SELECT * FROM MyTestDB..ToBeDeleted
GO

Do you know why this happened and what is the role of backslash? Comment and let me know your thoughts.

Comprehensive Database Performance Health Check is my MOST popular service. Customers opt for this service when they face performance issues with SQL Server. Here are six-part blog post series I have written based on my last 10 years of experience helping with Comprehensive Database Performance Health Check.

Here are a few additional blog posts which you may find interesting:

Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)