CODEWITHSUNDEEP
sql-servert-sqlsql-server-agentdbcc
chezy525
chezy525

Reputation: 4174

SQL Agent step with cursor not excuting for all databases

I have a SQL Agent job with a step that uses a cursor to run a DBCC CHECKDB for all databases. However, it randomly stops before it runs for all databases. The job succeeds, and I see no errors in the "Log to table" view for the step - other than it stops before the last database.

The job step command (T-SQL from [master]):

declare c_db cursor for 
select [name]
from sys.databases
order by database_id

declare @dbName nvarchar(100)

open c_db
fetch next from c_db into @dbName
while @@FETCH_STATUS = 0
 BEGIN
    print 'checking db:'+@dbName+' ['+convert(varchar,getutcdate(),120)+']'
    DBCC CHECKDB (@dbName)
    print '  done:'+@dbName+' ['+convert(varchar,getutcdate(),120)+']'

    fetch next from c_db into @dbName
 END

close c_db
deallocate c_db

Again, this sometimes works as expected. But when it fails to run for all databases, the step completes successfully, and the job continues to the next step. The log file (viewed thru SSMS Job step properties, Advanced page, "Log to table", "View") doesn't show any errors, and the last line is just the " done:{dbname} [{timestamp}]" from the print statement for the last database it ran for. And the timestamp is close to the timestamp for that particular database from previous runs.

I admit, I'm at a loss on this one. I don't even know where to start looking for an issue/conflict/etc. So, thanks in advance for your help!

Upvotes: 0

Views: 28

Answers (0)

Related Questions