Backup status

[Former Member]

Hi ,

Can you please let me know how to check the running backups in sql server 2008.

Regards,

Kirthi S

[rupali_karbhari3 · Accepted solution]

Hi,

If database backup is scheduled via SQL job , you can check the status of backup job via SQL Server Managment Studio.

Activity monitor will show you status of running jobs (running,cancelled ,halt etc)

You can also set alert parameter while sheduling SQL backup jobs to send mail if job fails.

Regards

[Former Member]

In addition to above points

you can also check with the T-sql script, This will provide you more details.

SELECT r.session_id,

s.login_name,

s.host_name,

r.command,

CONVERT(NUMERIC(6,2), r.percent_complete) AS [Percent Complete],

CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [ETA Completion Time],

CONVERT(NUMERIC(10,2), r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],

CONVERT(NUMERIC(10,2), r.estimated_completion_time/1000.0/60.0) AS [ETA Min],

CONVERT(NUMERIC(10,2), r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],

CONVERT(VARCHAR(1000),( SELECT SUBSTRING(text, r.statement_start_offset/2,

CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)

FROM sys.dm_exec_sql_text(sql_handle)))

FROM sys.dm_exec_requests r

INNER JOIN sys.dm_exec_sessions s

ON r.session_id = s.session_id

WHERE 1=1

--AND command IN ('drop trigger','create trigger')

AND r.total_elapsed_time != 0

AND r.total_elapsed_time < 100000000

ORDER BY 5 DESC

Thanks

Mushtaq