February 3, 2012 at 8:05 am
We've a got a bit of a head scratcher with one of our SQL Agent jobs I'd like to get some feedback on.
It is a one step job, scheduled for Sundays at 1am. The guts of the job is a t-sql step that does this:
DECLARE @cmd1 VARCHAR(2000)
set @cmd1='
use [?]
IF ''[?]'' not in (''[master]'',''[model]'',''[msdb]'',''[distribution]'',''[tempdb]'')
AND (SELECT DATABASEPROPERTY(''?'', ''IsReadOnly''))=0 AND (SELECT DATABASEPROPERTY(''?'',''IsOffline''))=0
BEGIN
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
print ''[?]''
/*
Do a bunch of stuff
*/
END'
exec sp_MSforeachdb @command1=@cmd1
Now, running this code manually, or firing off the job manually works as expected. Everytime we tried it.
For the most part the scheduled job runs as expected as well. With the log file containing the db name and all of the output we've included in the script.
Except, sporadically, the job will finish successfully without doing any of the work in the "Do a bunch of stuff" section. The log file contains no details only a single line that Step 1 of the job has started.
To me is seems as if the evaluation of ''IsReadOnly'' or ''IsOffline'' (not really requied due the the use statement) is not coming back properly for every db on the server. Another possibility is sp_MSforeachdb not picking up any db names.
Again when it works, it runs for over an hour, when it doesnt work it completes successfully in 1 second.
Any ideas on this one? I'm completely puzzled by the behaviour.
February 3, 2012 at 8:17 am
Wow, a bit more googling and I stumbled across this: http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx
I've NEVER seen this behaviour before afaik. Guess, I'll be rolling my own version of sp_MSforeachdb.
Seem like such a fundamental thing. How could they get this part wrong.
February 3, 2012 at 10:02 am
Well it is an undocumented & unsupported proc 😀
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply