« Backup Status / Current process and all processes » : différence entre les versions
Aller à la navigation
Aller à la recherche
Aucun résumé des modifications |
Aucun résumé des modifications |
||
Ligne 1 : | Ligne 1 : | ||
<SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED | <syntaxhighlight lang="sql"> | ||
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED | |||
SELECT | SELECT | ||
DB_NAME(er.[database_id]) [DatabaseName] | DB_NAME(er.[database_id]) [DatabaseName] | ||
Ligne 15 : | Ligne 16 : | ||
CROSS APPLY sys.dm_exec_sql_text(er.[sql_handle]) est | CROSS APPLY sys.dm_exec_sql_text(er.[sql_handle]) est | ||
WHERE er.[command] in ('RESTORE DATABASE','BACKUP DATABASE','RESTORE HEADERON') | WHERE er.[command] in ('RESTORE DATABASE','BACKUP DATABASE','RESTORE HEADERON') | ||
<syntaxhighlight lang="sql"> | |||
SELECT FORMAT( GETDATE(), 'hh.mm tt dddd, dd MMMM, yyyy'); | |||
<syntaxhighlight lang="sql"> | |||
SELECT FORMAT( GETDATE(), 'hh.mm tt dddd, dd MMMM, yyyy'); | |||
</syntaxhighlight> | </syntaxhighlight> | ||
== all process status == | == all process status == | ||
<select | <syntaxhighlight lang="sql"> | ||
select | |||
a.session_id | a.session_id | ||
, command | , command | ||
Ligne 31 : | Ligne 34 : | ||
from sys.dm_exec_requests a | from sys.dm_exec_requests a | ||
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b</code> | CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b</code> | ||
</syntaxhighlight> | |||
== link pid and spid == | == link pid and spid == | ||
<syntaxhighlight lang="sql"> | |||
SELECT osTask.session_id, osThreads.os_thread_id, | |||
osTask.scheduler_id, osTask.task_state | osTask.scheduler_id, osTask.task_state | ||
FROM sys.dm_os_tasks AS osTask | FROM sys.dm_os_tasks AS osTask | ||
Ligne 41 : | Ligne 45 : | ||
WHERE osTask.session_id IS NOT NULL | WHERE osTask.session_id IS NOT NULL | ||
ORDER BY osTask.session_id; | ORDER BY osTask.session_id; | ||
GO</syntaxhighlight> | |||
GO</ | |||
[[Catégorie:Mssql]] | [[Catégorie:Mssql]] |
Version du 23 mars 2023 à 14:55
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
DB_NAME(er.[database_id]) [DatabaseName]
,er.[session_id] AS [SessionID]
,er.[command] AS [CommandType]
,est.[text] [StatementText]
,er.[status] AS [Status]
,CONVERT(DECIMAL(5, 2), er.[percent_complete]) AS [Complete_Percent]
,CONVERT(DECIMAL(38, 2), er.[total_elapsed_time] / 60000.00) AS [ElapsedTime_m]
,CONVERT(DECIMAL(38, 2), er.[estimated_completion_time] / 60000.00) AS [EstimatedCompletionTime_m]
,er.[last_wait_type] [LastWait]
,er.[wait_resource] [CurrentWait]
FROM sys.dm_exec_requests AS er
INNER JOIN sys.dm_exec_sessions AS es ON er.[session_id] = es.[session_id]
CROSS APPLY sys.dm_exec_sql_text(er.[sql_handle]) est
WHERE er.[command] in ('RESTORE DATABASE','BACKUP DATABASE','RESTORE HEADERON')
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
SELECT FORMAT( GETDATE(), 'hh.mm tt dddd, dd MMMM, yyyy');
all process status
select
a.session_id
, command
, a.start_time
, estimated_completion_time = DATEADD(ms, a.estimated_completion_time, GETDATE() )
from sys.dm_exec_requests a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b</code>
link pid and spid
SELECT osTask.session_id, osThreads.os_thread_id,
osTask.scheduler_id, osTask.task_state
FROM sys.dm_os_tasks AS osTask
INNER JOIN sys.dm_os_threads AS osThreads
ON osTask.worker_address = osThreads.worker_address
WHERE osTask.session_id IS NOT NULL
ORDER BY osTask.session_id;
GO