Otterpohl

DECLARE @options INT
SELECT @options = @@OPTIONS

PRINT @options
IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK'
IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS'
IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT'
IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS'
IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING'
IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS'

Otterpohl

DECLARE @temp_spblitzwho AS TABLE
(
    run_date DATETIME2 NULL,
    elapsed_time NVARCHAR(30) NULL,
    session_id INT NULL,
    database_name NVARCHAR(100) NULL,
    query_text NVARCHAR(MAX) NULL,
    query_plan XML NULL,
    live_query_plan XML NULL,
    query_cost FLOAT NULL,

Otterpohl

DECLARE @temp_sp_who2 AS TABLE
    (
      SPID INT,
      Status VARCHAR(1000) NULL,
      Login SYSNAME NULL,
      HostName SYSNAME NULL,
      BlkBy SYSNAME NULL,
      DBName SYSNAME NULL,
      Command VARCHAR(1000) NULL,
      CPUTime INT NULL,

Otterpohl

SELECT TOP 10
       d.name,
       st.text,
       qs.creation_time,
       qs.execution_count,
       qs.total_worker_time,
       qs.total_elapsed_time,
       qs.total_logical_reads,
       qs.total_physical_reads,
       qp.query_plan,

Otterpohl

/*
Last updated October 1, 2021
https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
*/

WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],

Otterpohl

select CASE (ss.freq_subday_type)
            WHEN 1 then   left(stuff((stuff((replicate('0', 6 - len(ss.active_start_time)))+ convert(varchar(6),ss.active_start_time),3,0,':')),6,0,':'),8)
            WHEN 2 then 'Every ' + convert(varchar(10),ss.freq_subday_interval) + ' seconds'
            WHEN 4 then 'Every ' + convert(varchar(10),ss.freq_subday_interval) + ' minutes'
            WHEN 8 then 'Every ' + convert(varchar(10),ss.freq_subday_interval) + ' hours'
            ELSE '??'
        END AS [Time],
        s.Name
from msdb.dbo.sysjobs AS s
left join msdb.dbo.sysjobschedules AS sj on S.job_id = SJ.job_id  

Otterpohl

SELECT j.name,
       j.description,
       a.start_execution_date
FROM msdb.dbo.sysjobs AS j
    INNER JOIN msdb.dbo.sysjobactivity AS a
        ON j.job_id = a.job_id
WHERE a.start_execution_date > DATEADD(dd, -14, GETDATE())
      AND j.enabled = 1
      AND a.start_execution_date IN
          (

Otterpohl

SELECT t.name,
       MAX(us.last_user_seek) AS last_user_seek,
       MAX(us.last_user_scan) AS last_user_scan,
       MAX(us.last_user_lookup) AS last_user_lookup,
       MAX(us.last_user_update) AS last_user_update
FROM sys.dm_db_index_usage_stats AS us
    JOIN sys.tables AS t
        ON t.object_id = us.object_id
WHERE us.database_id = DB_ID()
GROUP BY t.name;

Otterpohl

WITH LastRestores AS
(
	SELECT [rs].[destination_database_name]
		,[rs].[restore_date]
		,[bs].[backup_start_date]
		,[bs].[backup_finish_date]
		,[bs].[database_name] AS [source_database_name]
		,[bmf].[physical_device_name] AS [backup_file_used_for_restore]
		,RowNum = ROW_NUMBER() OVER (PARTITION BY [rs].[destination_database_name] ORDER BY rs.[restore_date] DESC)
	FROM msdb..restorehistory rs

Otterpohl

DECLARE @TraceFileName NVARCHAR(400);

SELECT TOP 1
       @TraceFileName = path
FROM sys.traces
WHERE is_default = 1;

SELECT *
FROM sys.fn_trace_gettable(@TraceFileName, DEFAULT) AS t
WHERE (