Results 1 to 6 of 6

Thread: Checking transaction status in VBScript?

  1. #1
    Join Date
    Jun 2003
    Posts
    3

    Question 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?

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    before every restore statement try to issue wait command like executing sleep command for a min or so

  3. #3
    Join Date
    Jun 2003
    Posts
    3
    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!

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    why cant you explicitly begin a transaction and commit and after all the commits do a checkpoint on all the databases

  5. #5
    Join Date
    Jun 2003
    Posts
    3
    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?!.

  6. #6
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •