donderdag, september 25, 2008

Query job last run status in MS SQL & some other SQL stuff

On my current assignment i needed to check in a SQL DTS whether the last time a batch job had run, it was succesfull or not (execution status). I searched around the web, but found lots of complicated code i had trouble using. I finally ended up with simple working code:

declare @runstatus int
set @runstatus = (select last_run_outcome
openrowset ('SQLOLEDB','Server=(local);Trusted_Connection=yes','SET FMTONLY OFF EXEC msdb.dbo.sp_help_job') q
where name = '')
print @runstatus

If you execute this in SQL Analyzer, you can see the last run status is returned in variable runstatus. Off course you can query any other column as well.

And by the way: what to think or how to know what DTS job has what Plan GUID/Version GUID (for example when you run into problems with your maintenance plan where sqlmaint is called with this cryptic identifier)? It took me to long to find the simple statement to do just that: "exec msdb..sp_enum_dtspackages".

And for another problem i had to find out everything about how databases and logfiles grow and how you can manage / shrink / reclaim the space they occupy. If you're dealing with similar questions, have a look at why choose between simple or full recovery mode, what backup model to choose, how to manage database and log file size, Why you want to be restrictive with shrink of database files and Managing the Size of the Transaction Log File.

3 opmerkingen:

  1. Anoniem5:29 p.m.

    Thanks - much easier!


  2. thanks man this helped a lot.

  3. thanks a lot, good and perfect example. thanks