Who is the owner/creator of the #Temp tables!?

I found this great post by Aaron Bertrand with the title ‘Dude, who owns that #temp table?‘

He explains how you could run a trace on SQL Server 2012 and figure out who’s actually creating those #Temp tables! 🙂

1) Create an Extended Event (by using this SQL script):
NOTE: Make sure to verify that ‘c:\temp‘ does exist on your server


CREATE EVENT SESSION [TempTableCreation] ON SERVER
ADD EVENT sqlserver.object_created
(
ACTION
(
-- you may not need all of these columns
sqlserver.session_nt_username,
sqlserver.server_principal_name,
sqlserver.session_id,
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.sql_text
)
WHERE
(
sqlserver.like_i_sql_unicode_string([object_name], N'#%')
AND ddl_phase = 1 -- just capture COMMIT, not BEGIN
)
)
ADD TARGET package0.asynchronous_file_target
(
SET FILENAME = 'c:\temp\TempTableCreation.xel',
-- you may want to set different limits depending on
-- temp table creation rate and available disk space
MAX_FILE_SIZE = 32768,
MAX_ROLLOVER_FILES = 10
)
WITH
(
-- if temp table creation rate is high, consider
-- ALLOW_SINGLE/MULTIPLE_EVENT_LOSS instead
EVENT_RETENTION_MODE = NO_EVENT_LOSS
);
GO
ALTER EVENT SESSION [TempTableCreation] ON SERVER STATE = START;


2) Verify that the trace is running:
Head over to SSMS and verify that the Extended Event was created and is running at this moment:

Also open the ‘Watch Live Data‘-window:

3) Analyzing the data:

Run this query to analyse which #Temp tables currently exist and who made them:


DECLARE @delta INT = DATEDIFF(MINUTE, SYSUTCDATETIME(), SYSDATETIME());

;WITH xe AS
(
SELECT
[obj_name] = xe.d.value(N'(event/data[@name="object_name"]/value)[1]',N'sysname'),
[object_id] = xe.d.value(N'(event/data[@name="object_id"]/value)[1]',N'int'),
[timestamp] = DATEADD(MINUTE, @delta, xe.d.value(N'(event/@timestamp)[1]',N'datetime2')),
SPID = xe.d.value(N'(event/action[@name="session_id"]/value)[1]',N'int'),
NTUserName = xe.d.value(N'(event/action[@name="session_nt_username"]/value)[1]',N'sysname'),
SQLLogin = xe.d.value(N'(event/action[@name="server_principal_name"]/value)[1]',N'sysname'),
HostName = xe.d.value(N'(event/action[@name="client_hostname"]/value)[1]',N'sysname'),
AppName = xe.d.value(N'(event/action[@name="client_app_name"]/value)[1]',N'nvarchar(max)'),
SQLBatch = xe.d.value(N'(event/action[@name="sql_text"]/value)[1]',N'nvarchar(max)')
FROM
sys.fn_xe_file_target_read_file(N'C:\temp\TempTableCreation*.xel',NULL,NULL,NULL) AS ft
CROSS APPLY (SELECT CONVERT(XML, ft.event_data)) AS xe(d)
)
SELECT
DefinedName = xe.obj_name,
GeneratedName = o.name,
o.[object_id],
xe.[timestamp],
o.create_date,
xe.SPID,
xe.NTUserName,
xe.SQLLogin,
xe.HostName,
ApplicationName = xe.AppName,
TextData = xe.SQLBatch,
row_count = x.rc,
reserved_page_count = x.rpc
FROM xe
INNER JOIN tempdb.sys.objects AS o
ON o.[object_id] = xe.[object_id]
AND o.create_date >= DATEADD(SECOND, -2, xe.[timestamp])
AND o.create_date <= DATEADD(SECOND, 2, xe.[timestamp])
INNER JOIN
(
SELECT
[object_id],
rc = SUM(CASE WHEN index_id IN (0,1) THEN row_count END),
rpc = SUM(reserved_page_count)
FROM tempdb.sys.dm_db_partition_stats
GROUP BY [object_id]
) AS x
ON o.[object_id] = x.[object_id]
ORDER BY xe.[timestamp]


See all #Temp tables which existed?
After the user dropped the #Temp table, it will no longer be listed here.
But if you do want to see every #Temp tables which was created in a certain period, replace the INNER-joins with LEFT-joins and voila!:


DECLARE @delta INT = DATEDIFF(MINUTE, SYSUTCDATETIME(), SYSDATETIME());

;WITH xe AS
(
SELECT
[obj_name] = xe.d.value(N'(event/data[@name="object_name"]/value)[1]',N'sysname'),
[object_id] = xe.d.value(N'(event/data[@name="object_id"]/value)[1]',N'int'),
[timestamp] = DATEADD(MINUTE, @delta, xe.d.value(N'(event/@timestamp)[1]',N'datetime2')),
SPID = xe.d.value(N'(event/action[@name="session_id"]/value)[1]',N'int'),
NTUserName = xe.d.value(N'(event/action[@name="session_nt_username"]/value)[1]',N'sysname'),
SQLLogin = xe.d.value(N'(event/action[@name="server_principal_name"]/value)[1]',N'sysname'),
HostName = xe.d.value(N'(event/action[@name="client_hostname"]/value)[1]',N'sysname'),
AppName = xe.d.value(N'(event/action[@name="client_app_name"]/value)[1]',N'nvarchar(max)'),
SQLBatch = xe.d.value(N'(event/action[@name="sql_text"]/value)[1]',N'nvarchar(max)')
FROM
sys.fn_xe_file_target_read_file(N'C:\temp\TempTableCreation*.xel',NULL,NULL,NULL) AS ft
CROSS APPLY (SELECT CONVERT(XML, ft.event_data)) AS xe(d)
)
SELECT
DefinedName = xe.obj_name,
GeneratedName = o.name,
o.[object_id],
xe.[timestamp],
o.create_date,
xe.SPID,
xe.NTUserName,
xe.SQLLogin,
xe.HostName,
ApplicationName = xe.AppName,
TextData = xe.SQLBatch,
row_count = x.rc,
reserved_page_count = x.rpc
FROM xe
LEFT JOIN tempdb.sys.objects AS o
ON o.[object_id] = xe.[object_id]
AND o.create_date >= DATEADD(SECOND, -2, xe.[timestamp])
AND o.create_date <= DATEADD(SECOND, 2, xe.[timestamp])
LEFT JOIN
(
SELECT
[object_id],
rc = SUM(CASE WHEN index_id IN (0,1) THEN row_count END),
rpc = SUM(reserved_page_count)
FROM tempdb.sys.dm_db_partition_stats
GROUP BY [object_id]
) AS x
ON o.[object_id] = x.[object_id]
ORDER BY xe.[timestamp]


After a few seconds the ‘Watch Live Data‘-window will start showing results (or after dropping the table, can’t remember…):

Job Activity Monitor showing that a job/step is still running even though the job has completed (without running all steps)

This is one of those freaky scenarios where you just pull your hair out…this morning it happened again (second time).
My ETL did ran, but the Job Activity Monitor is showing that it’s still running somehow:

Turns out that it just completed Step 1 and after that stopped…
The thing is that when you dive deep into the logging, you notice that ‘Step 0 (Job Outcome)‘ is missing and for some reason SQL Agent decided it could stop after Step 1….WTF
(and yes, it suppossed to go to the next step)

The Query:


SELECT
j.name AS JobName
,j.job_id AS JobId
,CAST(DATEADD(S,(run_time/10000)*60*60 /* hours */
+((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
+ (run_time - (run_time/100) * 100) /* secs */
,CONVERT(DATETIME,RTRIM(run_date),113))
AS DATETIME) AS ExecutionTime
,h.step_id AS StepId
,h.step_name AS StepName
,h.run_status AS StatusId
,StatusDesc = CASE
WHEN h.run_status=0 THEN 'Failed'
WHEN h.run_status=1 THEN 'Succeeded'
WHEN h.run_status=2 THEN 'Retry'
WHEN h.run_status=3 THEN 'Cancelled'
ELSE 'Unknown'
END
,s.on_success_action
,ActionDesc = CASE
WHEN s.on_success_action = 1 THEN 'QuitWithSuccess'
WHEN s.on_success_action = 3 THEN 'GotoNextStep'
ELSE 'http://technet.microsoft.com/en-us/library/ms142620(v=sql.105).aspx'
END
,h.*
FROM dbo.sysjobs j
INNER JOIN dbo.sysjobhistory h ON j.job_id = h.job_id
LEFT JOIN dbo.sysjobsteps s ON j.job_id = s.job_id AND h.step_id = s.step_id
WHERE DATEADD(S,
(run_time/10000)*60*60 /* hours */
+((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
+ (run_time - (run_time/100) * 100) /* secs */,
CONVERT(DATETIME,RTRIM(run_date),113)) >= DATEADD(d,-2,GetDate())
AND j.name IN ('DWH_TEST (daily): *Step 2')
ORDER BY 1,ExecutionTime DESC


And this time I’ve decided to at least build some kind of mechanism to detect of prevent it.
After a couple of hours of brainstorming and testing things, I found that the shitty thing is is that there’s no ‘BatchId’ of some sort that combines the job_id with step_id and run_date/run_time in dbo.sysjobhistory.

So I figured that during the previous maintenance/ETL window, a SQL Agent Job should have a Step 0 when it has run (skipping Step 0 would indicate that freaky scenario may have happened).
Here’s what I’ve came up with:

LastStepExecutionTime should have taken place in the same (last) maintenance/ETL window as Step0LastExecutionTime.

The Query:

WITH SourceData
AS
(
SELECT
j.name
,h0.Step0LastExecutionTime
,h.LastStepExecutionTime
FROM dbo.sysjobs j
INNER JOIN
(
SELECT
h.job_id
,MAX(
CAST(DATEADD(S,(run_time/10000)*60*60 /* hours */
+((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
+ (run_time - (run_time/100) * 100) /* secs */
,CONVERT(DATETIME,RTRIM(run_date),113))
AS DATETIME)
) AS LastStepExecutionTime
FROM dbo.sysjobhistory h
WHERE run_status = 1 --Succeeded
GROUP BY
h.job_id
) AS h ON j.job_id = h.job_id
LEFT JOIN
(
SELECT
h.job_id
,MAX(
CAST(DATEADD(S,(run_time/10000)*60*60 /* hours */
+((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
+ (run_time - (run_time/100) * 100) /* secs */
,CONVERT(DATETIME,RTRIM(run_date),113))
AS DATETIME)
) AS Step0LastExecutionTime
FROM dbo.sysjobhistory h
WHERE h.step_id = 0 --0 = (Job outcome)
GROUP BY
h.job_id
) AS h0 ON j.job_id = h0.job_id
--Last/final step should have been executed during the last maintenance window.
WHERE h.LastStepExecutionTime BETWEEN DATEADD(HOUR,1,CAST(CAST(GETDATE() AS DATE) AS DATETIME)) AND DATEADD(HOUR,6,CAST(CAST(GETDATE() AS DATE) AS DATETIME))
--While step 0 (Job outcome) should also have been executed/registered during the last maintenance window.
AND NOT h0.Step0LastExecutionTime BETWEEN DATEADD(HOUR,1,CAST(CAST(GETDATE() AS DATE) AS DATETIME)) AND DATEADD(HOUR,6,CAST(CAST(GETDATE() AS DATE) AS DATETIME))

)

SELECT
s.*
FROM SourceData s --Retrieve more info
ORDER BY 1,2,3


Next:
Build something that will alert you, like sending a database mail or integrate this into one of your maintenance reports.

SSRS: Sum() LookupSet Function

Here’s a handy piece of coding which might help you:
http://salvoz.com/blog/2013/05/27/sum-result-of-ssrs-lookupset-function/

You may use the SumLookup function to sum the results from LookupSet:
=Code.SumLookup(LookupSet(Fields!Parent_Number.Value,Fields!Parent_Number.Value,
Fields!StoreCount.Value, "StoreCount"))

Or use a combined ‘key’ to perform the lookup:
=Code.SumLookup(LookupSet(Fields!GroupName.Value & Fields!WeekNumber.Value,Fields!GroupName.Value & Fields!WeekNumber.Value,Fields!NrOfItems.Value,"DataSet1"))

Paste the following code in ‘Custom code’ field:

Function SumLookup(ByVal items As Object()) As Decimal
If items Is Nothing Then
Return Nothing
End If
Dim suma As Decimal = New Decimal()
Dim ct as Integer = New Integer()
suma = 0
ct = 0
For Each item As Object In items
suma += Convert.ToDecimal(item)
ct += 1
Next
If (ct = 0) Then return 0 else return suma
End Function

Why not to use a MONEY data type to represent a currency or other financial value

Remember my last post ‘Why not to use a float to represent a currency or other financial value‘?

Here’s another one in regards to the usage of the MONEY data type:


--Decimal/Numeric versus Money:

DECLARE
@mon1 MONEY,@mon2 MONEY,@mon3 MONEY,@mon4 MONEY,
@num1 DECIMAL(19,4),@num2 DECIMAL(19,4),@num3 DECIMAL(19,4),@num4 DECIMAL(19,4),@num5 DECIMAL(19,4)

SELECT
@mon1 = 100, @mon2 = 339, @mon3 = 10000,
@num1 = 100, @num2 = 339, @num3 = 10000

SET @mon4 = @mon1/@mon2*@mon3
SET @num4 = @num1/@num2*@num3

SET @num5 = @num1/@num2

SET @num5 = @num5*@num3

SELECT @mon4 AS moneyresult,
@num4 AS numericresult,
@num5 AS numericresult2


The result:

Download the complete example:
Decimal-Numeric versus Float and Money