CREATE FUNCTION [dbo].[fn_RunDuration] (@nRunDuration INT)
RETURNS VARCHAR(128)
AS
BEGIN
DECLARE @vcMsg VARCHAR(128),
@nH INT,
@nM INT,
@nS INT,
@vcH VARCHAR(48),
@vcM VARCHAR(48),
@vcS VARCHAR(48)
SELECT @nH = LEFT(RIGHT('000000' + CAST(@nRunDuration AS VARCHAR), 6), 2),
@nM = LEFT(RIGHT('000000' + CAST(@nRunDuration AS VARCHAR), 4), 2),
@nS = RIGHT('000000' + CAST(@nRunDuration AS VARCHAR), 2),
@vcH = CASE WHEN @nH = 0 THEN ''
WHEN @nH = 1 THEN CAST(@nH AS VARCHAR) + ' hour, '
ELSE CAST(@nH AS VARCHAR) + ' hours, '
END,
@vcM = CASE WHEN @nM = 0 THEN ''
WHEN @nM = 1 THEN CAST(@nM AS VARCHAR) + ' minute, '
ELSE CAST(@nM AS VARCHAR) + ' minutes, '
END,
@vcS = CASE WHEN @nS = 1 THEN CAST(@nS AS VARCHAR) + ' second'
ELSE CAST(@nS AS VARCHAR) + ' seconds'
END,
@vcMsg = @vcH + @vcM + @vcS
RETURN @vcMsg
END
[master_admin].[dbo].[fn_INTtoDateTime]
CREATE FUNCTION [dbo].[fn_INTtoDateTime] (
@vcDate VARCHAR(8),
@vcTime VARCHAR(6)
)
RETURNS DATETIME
AS
BEGIN
SET @vcDate = RIGHT(RTRIM('00000000' + CONVERT(CHAR(8), @vcDate) + ' '), 8)
SET @vcTime = RIGHT(RTRIM('00000000' + CONVERT(CHAR(6), @vcTime) + ' '), 6)
IF @vcDate = '00000000'
SET @vcDate = LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(23), CAST(0 AS SMALLDATETIME), 121),
'-', ''), ':', ''),
SPACE(1), ''), 8)
RETURN (
CAST(SUBSTRING(@vcDate,1,4) + '-' + SUBSTRING(@vcDate,5,2) + '-' + SUBSTRING(@vcDate,7,2) + ' ' +
SUBSTRING(@vcTime,1,2) + ':' + SUBSTRING(@vcTime,3,2) + ':' + SUBSTRING(@vcTime,5,2)
AS DATETIME)
)
END
SQL Agent: What ran last night after time …
------------------------------------------------------------------------------------------------------------------------
--
-- What ran last night after time ...
--
SELECT [master_admin].[dbo].[fn_INTtoDateTime]([run_date], [run_time]) AS [RunDate],
DATENAME(WEEKDAY, CONVERT(VARCHAR(10), [run_date], 112)),
[j].[name],
[master_admin].[dbo].[fn_RunDuration]([run_duration]),
[h].[step_id],
[s].[step_name],
[command] AS [StepCommand],
[instance_id]
FROM [msdb].[dbo].[sysjobhistory] [h] WITH (NOLOCK)
JOIN [msdb].[dbo].[sysjobs] [j] WITH (NOLOCK)
ON [j].[job_id] = [h].[job_id]
JOIN [msdb]..[sysjobsteps] [s] WITH (NOLOCK)
ON [s].[job_id] = [j].[job_id]
AND [s].[step_id] = [h].[step_id]
WHERE [run_date] >= CONVERT(VARCHAR(8), DATEADD(DAY, -0, GETDATE()), 112)
AND [h].[run_time] >= 021439
ORDER BY [RunDate]
Required objects
[fn_INTtoDateTime]
[fn_RunDuration]
What are all those old tempdb temp tables?
SQL Server caches the definitions of temp tables and table variables, to reduce the load on the system catalog in tempdb on busy systems which creates a lot of temp tables. Typically they have a name that starts with # and is followed by eight hex digits. –Erland Sommarskog
SQL Server Temporary Table Caching
This tip will describe the condition to allow caching of a SQL Server temporary table and a demonstration to benchmark the performance between a cached vs. non-cached temporary table.
Source: www.mssqltips.com/sqlservertip/4406/sql-server-temporary-table-caching/
SHARE permissions vs. SECURITY tab
Here’s a great way to think about it, quoted from around the web:
Sharing only opens the door, security lets you in the door.
Standard practice is to allow everyone on sharing, and restrict access with security permissions. This keeps management simple as the most restrictive permissions apply between the two.