Help, job is finished, but shows as "executing"

[takaoki]

Help. Does anyone know why a job would finish but still show as "executing" in the "Job Activity Monitor"?

The last step of the job is a "restore database" script I created. I see that the database has finished restoring as of 8:30 this morning, but it's now 11:30 and the job is still running (ostensibly)...

any ideas??

 

[mrdenny]

What step does it say that it is on?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[takaoki]

The last step.

Everytime it gets to this step, the job just hangs, even though the restore is complete. It's a script that calls OSQL on another server... if that helps.

Thanks

 

[mrdenny]

Are you using the -Q or -q parameter?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[takaoki]

Hi Mr Denny,

This is the command I am running from a .cmd file, which is the last step of the job that is hanging:

osql -S MyOtherServer -d MyDatabase -E -q "Restore_DB_procedure 'MyDBToRestore', 'MyDBRestored', 'D:\MyBackupFile.BAK', 'D:\MSSQL.1\MSSQL\Data\'" -o D:\MyLogFile.log

-S MyOtherServer is ServerB, being called from a job on ServerA

Thanks!

 

[takaoki]

Ok, I see in BOL that "-q" does not exit the query, which would explain why it hangs...

But "-Q" says that it will execute the query and exit immediately... so how will the job step "know" if osql completed successfully? I would like the job step to fail if for some reason the osql query (sp) fails. Is there a way to capture this information?

Thanks!

 

[mutley1]

Try using the -b switch in the osql command line. From MS:

Specifies that osql exits and returns a DOS ERRORLEVEL value when an error occurs. The value returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity of 10 or greater; otherwise, the value returned is 0. Microsoft MS-DOS® batch files can test the value of DOS ERRORLEVEL and handle the error appropriately.

Then that step should show as a failure if it does fail.

HTH,

M.