MS SQL - WaitFor does not work as expected ...

[magster 0]

Hi all,

 

I'm new to MS-SQL (coming from the oracle world) so apologies for my (probably) obvious-noob question ;)

 

I've created a job that starts a snap-shot restore. This job gets triggered by a tsql script:

msdb.dbo.sp_start_job 'restore_snap';

 

This works fine, beside the sp_start_job will just fire-up the job ... and that's it ! It will not wait for its execution to finish or will output any errors that may occur during the restore ...
Not really nice, but ok ...

So I wrote another query that "monitors" the progress of the restore:
 

SELECT sj.name, sja.*

FROM msdb.dbo.sysjobactivity AS sja

INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id

WHERE sja.start_execution_date IS NOT NULL

AND sj.name = 'restore_snap'

 

Now, I wraped this code in a WHILE loop an check for the execution_date. And here is the problem. I want to check every 30 sec if the job is still running. The script should output every 30 sec: "script still running" or "finished"

So I used a "WAITFOR DELAY '00:00:30' " sleep combined with an "echo ....". BUT this doesn't work as expected. The script will NOT report the status every 30 secs but will wait until the WHILE loop has finished and then output all attempts. So if the script had run for 5 min ... it will wait for 5 min and then print 10 times "Script still running" afterwards.

This looks like a fault/bug rather than a feature to me ... but maybe I'm just using the wrong approach

 

Can you help me out ?

[NilsK 2.934]

Hi,

 

welcome to our forum. Did you notice that this is a German-language forum? We can proceed in English, but it's not the usual language here. So if you happen to speak German we might better switch.

 

Could you please explain what you are basically trying to achieve? The job and all that you describe are most likely just tools to get something done. What is this "something"? Maybe there are other ways to do this in SQL Server that have better results.

 

Bye, Nils

 

[magster 0]

Hi Nils

 

Nun es gibt einen Job in der DB der ein Snapshot-Restore anwirft. Dieser Job soll via Script ge-triggerd werden. (von ext. tool)

Dieses Script macht eigentlich nichts weiteres als: "msdb.dbo.sp_start_job 'restore_snap';"

 

Nur der msdb.dbo.sp_start_job startet den job und ist damit sofort fertig ... keine Rückmeldung wann/ob der Job ferig ist oder ob es zu Fehlern kam ...

 

Also hab ich versucht herum zu bauen indem ich seinen "progess" monitore um zu sehen wie weit er schon ist.

Je nach DB kann aber das restore etwas dauern ... Deshalb soll alle 20 sec ein output erfolgen bei welchem step er gerade steht.

Während der Job läuft ist "last_executed_step_date IS NOT NULL".

 

So sieht der Query momentan aus:

DECLARE @cnt INT = 0;
DECLARE @query NVARCHAR(MAX);

WHILE @cnt < 3
BEGIN
   SET @query = N'SELECT sja.last_executed_step_id FROM msdb.dbo.sysjobactivity AS sja INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id WHERE sja.start_execution_date IS NOT NULL AND sja.last_executed_step_date IS NOT NULL AND sj.name = ''restore_snap'' ';

   EXEC sp_executesql @query, @cnt = @cnt OUTPUT;

   PRINT 'Step: ' + (CONVERT (varchar(10),@cnt)) + ' completed';
   WAITFOR DELAY '00:00:20';

END;

Alle 20 sek soll ein "PRINT (CONVERT (varchar(10),@cnt)) + ' completed'; " erfolgen. Das passiert aber nicht !

Der Query wartet bis er fertig ist und gibt erst dann aus wie oft er den "print" gemacht hat ...

Z.B. das Script läuft 5 minuten ... es erfolgt keine "Statusmeldung" (PRINT ...) alle 20 sek, sondern nach 5 Minuten 15x "Step 1..3 completed"

 

Für mich sieht das nach einem Bug aus ... Aber vielleicht ist "WAITFOR DELAY" auch einfach nur der falsche Ansatz ... ?

Ich hab gerade noch ein einfacheres Beispiel gefunden was das Problem/Effekt verdeutlicht:

 

DECLARE @count INT;
SET @count = 1;
    
WHILE @count<= 5
BEGIN
   PRINT @count
   SET @count = @count + 1;
   WAITFOR DELAY '00:00:05';
END;

 

Es wird nicht alle 5 Sekunden ein Wert ausgegeben sondern nach 25 Sekunden alle 5 !

bearbeitet 30. Juni 2020 von magster

[NilsK 2.934]

Moin,

 

ja, die Beschreibung der Situation hab ich schon verstanden. Was mir nicht klar ist: Warum diese Wiederherstellung? Normalerweise macht man sowas ja nicht laufend. Klingt also, als wäre das Teil eines Prozesses. Was steckt dahinter?

 

Gruß, Nils

 

[magster 0]

Ach so ...

Das soll auch nicht dauernt gemacht werden. Es ist nur so das wenn wir Upgrades fahren, diese auch mnchmal schiefgehen und wir zurückrollen müssen. Daher wird vor dem Upgrade ein Snapshot erstellt und fals dieser schief läuft wieder restored. Das soll aber automatisiert geschehen ...

 

Dennoch ... Ich hab den Thread hauptsächlich wegen der WHILE Schleife mit WAITFOR Problematik geöffnet ...

[NilsK 2.934]

Moin,

 

hm, verstehe. Und in den Job kann man keine Statusausgabe integrieren? Wäre ja irgendwie direkter.

 

Das WAITFOR-Problem kann ich grad nicht nachstellen, hab nix zum Testen da. Dein Test-Batch allerdings dürfte ungeeignet sein, weil Print-Anweisungen nicht unbedingt innerhalb eines Batches ausgeführt werden, sondern (soweit ich mich erinnere) erst danach. Heißt also nicht, dass die Schleife als solche nicht funktioniert, nur die Messmethode misst nicht richtig. Du könntest Timestamps in eine Tabelle schreiben, das sollte eher funktionieren.

 

Wenn das Restore aber ohnehin von außen getriggert wird, könntest du die Statusabfrage ja auch von außen ausführen. Auch das käme mir direkter vor - sonst musst du das Ergebnis deiner Abfrage ja auch noch mal separat auslesen.

 

Gruß, Nils

 

bearbeitet 30. Juni 2020 von NilsK

[magster 0]

Hmm ... heißt das also das nicht das WaitFor das Problem ist sondern vielmehr das "Print" ?

[NilsK 2.934]

Moin,

 

das könnte zumindest sein.

 

Edit: Dies hier deutet auch darauf hin. Hab leider grad keine Zeit ...

https://stackoverflow.com/questions/306945/how-do-i-flush-the-print-buffer-in-tsql

 

Gruß, Nils

 

bearbeitet 30. Juni 2020 von NilsK

[Dukel 454]

38 minutes ago, magster said:

Das soll auch nicht dauernt gemacht werden. Es ist nur so das wenn wir Upgrades fahren, diese auch mnchmal schiefgehen und wir zurückrollen müssen.

Dafür gibts Transaktionen.

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/rollback-transaction-transact-sql?view=sql-server-ver15

 

[magster 0]

52 minutes ago, Dukel said:

Dafür gibts Transaktionen.

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/rollback-transaction-transact-sql?view=sql-server-ver15

 

Da die update Scripte zum teil dynamisch arbeiten funktioniert das mit Transaktionen "Rollback" nicht so ohne weiteres.

 

Abgesehen davon geht's hier ja hauptsächlich um das WAITFOR + PRINT Problem