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.
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
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\'
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.
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://blog.sqlauthority.com)