Checking transaction status in VBScript?

I am updating about 10 databases from production to development server using the same VBScript with database name as parameter.

It has actual RESTORE statement followed by BACKUP with TRUNCATE, then DBCC SHRINKDATABASE.

Sometimes(not everynight) one of databases (different everytime or same) gives me headache. I am getting error 3023 for BACKUP/ 927 for SHRINK, meaning RESTORE didn't complete.

I am not using Async mode for Execute, but I tried to use Async and cycle till State is not Executing -- it never ends...

Is there the way to find if transaction completed on server or it is still running?

before every restore statement try to issue wait command like executing sleep command for a min or so

Thanks for advice!

I was thinking about it, but...
for one it may not be sufficient -- some of databases take up to 15 min to restore
and some take mere seconds, so it would be waste of time.

Still, I won't know if transaction completed or not.

This morning I had very bad surprise -- a half of my databases didn't complete process, so it looks like at least I can reproduce problem.

Thanks again!

why cant you explicitly begin a transaction and commit and after all the commits do a checkpoint on all the databases

Thanks for reply! I found solution and my script is running smoothly for last 3 nights! How?..

Let's me say "EURIKA!" and RTFM!

There is a function DATABASEPROPERTYEX(). You can check a lot of properties and one that is interesting to me is STATUS.

I just cycle till database status is ONLINE, as opposed to OFFLINE, SUSPECT (corrupted), RESTORING, or RECOVERYING.

I found that it takes from 1 to 5 sec between my RESTORE command is comleted till my database is back ONLINE depending on size of database.

All this considering it is SYNC, not ASYNC mode! How do you like it?!.

nice:p