Identificar Jobs em Execução.

Identificar Jobs em Execução.
Database_name : Database que está configurado o Job
JOB_NAME : Nome do job em execução
SESSION_ID : Id da sessão de processamento, util quando utilizado a SP_WHOISACTIVE, que retorna no Program_name somente o id do job exemplo (SQLAgent - TSQL JobStep (Job 0x026126442B14604095F04ABC60C87310 : Step 2))
start_execution_date: Inicio da Execução
time_elapsed: Tempo de Execução
step_name: Step que está em execução no momento

 ;WITH JOBDETAILS
 AS (
  SELECT DISTINCT JOB_ID = LEFT(INTR1, CHARINDEX(':', INTR1) - 1)
   ,STEP = SUBSTRING(INTR1, CHARINDEX(':', INTR1) + 1, CHARINDEX(')', INTR1) - CHARINDEX(':', INTR1) - 1)
   ,[SESSION_ID] = SPID
  FROM MASTER.DBO.SYSPROCESSES X WITH(NOLOCK)
  CROSS APPLY (
   SELECT REPLACE(X.PROGRAM_NAME, 'SQLAGENT - TSQL JOBSTEP (JOB ', '')
   ) CS(INTR1)
  WHERE SPID > 50
   AND X.PROGRAM_NAME LIKE 'SQLAGENT - TSQL JOBSTEP (JOB %'
  )
 SELECT 
  Database_name,
  NAME AS JOB_NAME,
  JD.[SESSION_ID],
  A.start_execution_date,
  CONVERT(VARCHAR, CONVERT(VARCHAR, DATEADD(ms, ( DATEDIFF(SECOND, A.start_execution_date, GETDATE()) % 86400 ) * 1000, 0), 114)) AS time_elapsed,
  d.step_name
 FROM MSDB.DBO.SYSJOBS J WITH(NOLOCK)
 INNER JOIN JOBDETAILS JD WITH(NOLOCK) ON JD.JOB_ID = CONVERT(VARCHAR(MAX), CONVERT(BINARY (16), J.JOB_ID), 1)
 INNER JOIN MSDB.DBO.SYSJOBACTIVITY A ON A.job_id = J.job_id
 INNER JOIN MSDB.DBO.SYSJOBSTEPS                   D   WITH(NOLOCK)    ON A.JOB_ID = D.JOB_ID AND ISNULL(A.LAST_EXECUTED_STEP_ID, 0) + 1 = D.STEP_ID
 --WHERE NAME LIKE '%Nome Job%';
 --WHERE JD.[SESSION_ID] = 459

SQL Server Get free space in all files in an Instance

Pensa numa consulta completa para quem precisa saber o quanto um Database está reservando de espaço em seu arquivo, assim como o tamanho dos arquivos mdf e ldf.

Com algumas melhorias aplicadas na rotina da fonte: https://www.sqlservergeeks.com/sql-server-get-free-space-in-all-files-in-an-instance/

DECLARE @DBName NVARCHAR(100) = NULL, 
        @Drive NVARCHAR(2) = NULL 
 
DECLARE @cmd NVARCHAR(4000)
IF (SELECT OBJECT_ID('tempdb.dbo.#DBName')) IS NOT NULL
DROP TABLE #DBName
CREATE TABLE #DBName (Name NVARCHAR(100))
 
IF @DBName IS NOT NULL
INSERT INTO #DBName SELECT @DBName
ELSE
INSERT INTO #DBName SELECT Name FROM sys.databases WHERE state_desc = 'ONLINE'
 
IF (SELECT OBJECT_ID('tempdb.dbo.##FileStats')) IS NOT NULL
DROP TABLE ##FileStats
CREATE TABLE ##FileStats (ServerName NVARCHAR(100), DBName NVARCHAR(100), FileType NVARCHAR(100), 
FileName NVARCHAR(100), CurrentSizeMB FLOAT, FreeSpaceMB FLOAT, PercentMBFree FLOAT, FileLocation NVARCHAR(1000))
 
WHILE (SELECT TOP 1 * FROM #DBName) IS NOT NULL
BEGIN
 
    SELECT @DBName = MIN(Name) FROM #DBName
 
    SET @cmd = 'USE [' + @DBName + ']
    INSERT INTO ##FileStats
    SELECT @@ServerName AS ServerName, DB_NAME() AS DbName, 
    CASE WHEN type = 0 THEN ''DATA'' ELSE ''LOG'' END AS FileType,
    name AS FileName, 
     size/128.0 AS CurrentSizeMB,
 --ROUND(CAST(((size*8) / 1024.0 / 1024.0) AS FLOAT),2) AS CurrentSizeMB,  
    (size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0) AS FreeSpaceMB,
    100*(1 - ((CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0)/(size/128.0))) AS PercentMBFree,
    physical_name AS FileLocation
    FROM sys.database_files
 '
     
    IF @Drive IS NOT NULL
    SET @cmd = @cmd + ' WHERE physical_name LIKE ''' + @Drive + ':\%'''
 
    EXEC sp_executesql @cmd
     
    DELETE FROM #DBName WHERE Name = @DBName
     
END
 
SELECT 
 ServerName,
 DBName,
 ROUND(CAST((CurrentSizeMB  / 1024.0) AS FLOAT),2) AS "FileSize_GB",
 ROUND(CAST((FreeSpaceMB*100/CurrentSizeMB) AS FLOAT),2) AS "PercentFree", 
 ROUND(CAST((FreeSpaceMB  / 1024.0) AS FLOAT),2) AS "FreeSpace_GB",
 FileName,
 FileLocation
FROM ##FileStats
--WHERE FileLocation LIKE 'D:%'
ORDER BY CurrentSizeMB DESC
DROP TABLE #DBName
DROP TABLE ##FileStats