How do I get a statement to repeat till it detects a certain value?

Last Post 21 Mar 2013 07:46 AM by new2sql. 6 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
new2sql
New Member
New Member

--
19 Mar 2013 09:17 AM
I want to make my backup job Check to see if another backup is running before
it kicks off ( To prevent conflict or overlap ), and I have this little code
running before hand.

/********************************/
if exists
( select percent_complete
from master.sys.dm_exec_requests where command like 'backup%'
and percent_complete <> 0
)
begin
print 'There is a Database Backup currently running.'
end
else
Do something else.
/********************************/

Here's my question:
How do I get that statement to keep checking? When the value finally changes, then
it can proceed to the next part of the statement?

Is there way to programmatically get a statement to repeat it's self a certain number of
times? I hope thats not complicated.

Any ideas would be helpful.

Thanks in advance.
rm
New Member
New Member

--
19 Mar 2013 11:56 AM
You can backup multiple dbs at same time unless you backup all of them to single device which is no good at all.
new2sql
New Member
New Member

--
19 Mar 2013 12:06 PM
This is true; however in this case it's more specifically about having the transaction logs running say... every 5 minutes. So
the script would detect a full backup running, and will not allow it's self to run until the backup completes. So it would be repeatedly
checking the status of the backup of the database to ensure it would only start when it finishes.

The reverse is also true. The Full Database Backup has a similar check to see if there is a current backup on the database then when
the backup is detected it starts.

rm
New Member
New Member

--
19 Mar 2013 12:12 PM
How often do you do full backup? Daily? Takes over 24 hours to complete? Just don't see any reason to check that.
rm
New Member
New Member

--
19 Mar 2013 12:13 PM
You can backup log while full backup is running by the way.
gunneyk
New Member
New Member

--
20 Mar 2013 03:19 PM
Not sure why you want to do this. Like rm said you can run them concurrently now (I think since 2000). I think you are adding way too much complexity into something that doesn't need it. There are so many other things that you could devote your energy to. But in any case you can certainly do a loop in several ways. One is to keep doing it until a certain condition and then exit the loop. The other is to do it x many times and quite. Or really a combo of the two I guess. Here is an infinite loop.

WHILE 1 = 1
BEGIN
SELECT @@ServerName

IF your condition is true
BREAK

ELSE
WAITFOR DELAY '00:00:05' -- Wait 5 seconds
END

Or you can do it 100 many times

DELCARE @X INT = 1


WHILE @X <= 100
BEGIN
SELECT @@ServerName

IF your condition is true
BREAK

ELSE
WAITFOR DELAY '00:00:05' -- Wait 5 seconds


SET @X = @X + 1 ;
END
new2sql
New Member
New Member

--
21 Mar 2013 07:46 AM
Wow... You guys are really great.

The backup isn't that long, and I supposed based on what you mentioned I don't have to worry about overlap.

Many Thanks for the loop samples :)

Much appreciated.

Thanks



Acceptable Use Policy
---