January 6, 2012 at 11:48 am
can someone plese help?
I am attempting to determine when a backup is complete using sp_who2. If backup is not complete, I would like it to continue to look. Any suggestions?
CREATE TABLE #who2 (
spid INT
, [status] SYSNAME
, [login] SYSNAME
, hostname SYSNAME
, blocked SYSNAME
, dbname VARCHAR(50)
, cmd VARCHAR(300)
, cpu int
, physical_io SYSNAME
, lastbatch VARCHAR(50)
, programname SYSNAME
, dbid SYSNAME
, requestid int
)
INSERT INTO #who2
EXEC sp_who2
if not exists (select * from #who2 where cmd like '%BACKUP DATABASE%')
begin
January 6, 2012 at 11:56 am
Is the backup part of a job or maintenance plan? Might be easier to notify you via email when the backup completes. Never used a 3rd party backup tool before but I believe they offer similar notification options as well. Unless you're trying to do something else when the backup completes?
January 6, 2012 at 12:01 pm
Its part of a procedure. After backup completes, I want to start deleting records from a table. But do not want to start the delete until I am sure the backup is completed.
January 6, 2012 at 12:06 pm
I would query msdb..backupset table.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 6, 2012 at 12:12 pm
Will backup_finish_date = NULL if backup is in progress? I would still need to loop in case it's still running.
January 6, 2012 at 12:29 pm
SQLSeTTeR (1/6/2012)
Will backup_finish_date = NULL if backup is in progress? I would still need to loop in case it's still running.
Yes. I think so.
I think something like this will solve it - please take it as non-tested pseudo-code 🙂
WHILE (SELECT backup_finish_date FROM msdb..backupset WHERE <whatever-condition-you-figure-out>) is NULL
BEGIN
BEGIN
WAITFOR DELAY '01:00';
END;
END;
... idea is to wait one minute before checking status again.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 6, 2012 at 12:30 pm
Thanks man!
January 6, 2012 at 12:41 pm
I see what you're looking for now. Sounds like Paul's solution will work.
January 6, 2012 at 12:59 pm
SQLSeTTeR (1/6/2012)
Thanks man!
Wait until it works 😀 Glad to help.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply