March 31, 2017 at 8:40 am
I have setup a job to test if session context could be used across job steps. From what I can see from the output both steps are apart of the same session but the "Backup_Start_Date" context doesn't look like it can be found. Not sure if this is a limitation or if I am missing something.
Below is the code that I am running in the job.
STEP 1:
DECLARE @BackupStartDate DATETIME2 = SYSDATETIME()
EXEC sp_set_session_context 'Backup_Start_Date', @BackupStartDate, @read_only = 1;
DECLARE @date DATETIME2
SELECT @date = CONVERT(DATETIME2,SESSION_CONTEXT(N'Backup_Start_Date'));
PRINT CONVERT(VARCHAR(50),ISNULL(@date, 'No Date')) + char(11) + char(13) + CONVERT(VARCHAR(10),@@SPID)
STEP 2:
DECLARE @date DATETIME2
SELECT @date = CONVERT(DATETIME2, CONVERT(DATE, SESSION_CONTEXT(N'Backup_Start_Date')));
PRINT ISNULL(CONVERT(VARCHAR(50),@date), 'No Date') + char(11) + char(13) + CONVERT(VARCHAR(10),@@SPID)
April 1, 2017 at 8:44 pm
Can you provide sample output that you are seeing?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 2, 2017 at 3:33 am
From a quick test, it seems as though each step logs into SQL Server separately. This means the session is different. That makes sense, as each step is independent, can be of a different type, etc.
April 3, 2017 at 5:33 am
Steve/SQLRNNR,
I am not seeing the same results that you are. I am seeing that both steps have the same session. I ran profiler while running this job and saw that all of the TSQL Job Steps ran under the same SPID.
Below is the output from the job that I setup. The first step returns a date with a @@SPID of 64. The second step returns "No Date" with a @@SPID of 64.
Date 3/31/2017 10:20:14 AM
Log Job History (TEST_SessionContext)
Step ID 1
Server
Job Name TEST_SessionContext
Step Name Set Context
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
2017-03-31 10:20:14.3674449 64 [SQLSTATE 01000] (Message 0). The step succeeded.
Date 3/31/2017 10:20:14 AM
Log Job History (TEST_SessionContext)
Step ID 2
Server
Job Name TEST_SessionContext
Step Name print result
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
No Date 64 [SQLSTATE 01000] (Message 0). The step succeeded.
April 3, 2017 at 8:56 am
I wonder if there's a difference between there being a session and there being a connection. The connection might be preserved, but a session might not.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 3, 2017 at 9:44 am
Re-using a connection is possible. I used extended events and thought I saw multiple logins, which could be in the same session. I should test using a proxy, but a touch busy now.
April 3, 2017 at 12:37 pm
I will do some more testing later today. As with Steve, I am just a touch busy to finish that off at the moment.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 4, 2017 at 1:41 pm
Thank you guys for taking the time to look at this for me. Please let me know if there is any info I can provide to make testing this scenario easier.
April 28, 2017 at 6:16 pm
Sorry for the late response but I have finally been able to test it sufficiently.
What I am seeing is that each step of a multi-step job does indeed reuse the same session id. That said, the context does not transfer from one step to the next. In addition, the context does not register in sys.dm_exec_sessions which I expect it should. This may be part of the problem with transference to the subsequent steps. This could possibly boil down to the context being purged from memory as a new step starts despite the session not being closed.
To vet this out, I logged all of those details into a log table with context key, context value, session id etc.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply