Cursor problem

Question

Cursor problem

Robbert Hof

SSCrazy

Points: 2778
More actions
December 16, 2009 at 12:34 am

#94225

Hi,
I have a strange problem with a cursor. The weird part is that it is not consistent in processing the rows over time.
For a while it returns all the rows, then suddenly it returns a few rows. After making some dummy modifications it is functioning well again. This is the main part of the Stored Procedure:
DECLARE @databasename VARCHAR(128)
DECLARE @DBidINT
DECLARE @page_verifyvarchar(24)
DECLARE dbname CURSOR FOR SELECT name,database_id,page_verify_option_desc FROM master.sys.databases
OPEN dbname
FETCH NEXT FROM dbname
INTO @databasename, @DBid, @Page_verify
WHILE @@FETCH_STATUS = 0
BEGIN
... do some processing
FETCH NEXT FROM dbname
INTO @databasename,@dbid,@Page_verify
END
CLOSE dbname
DEALLOCATE dbname
The dummy modifications are: adding some lines between the BEGIN.. END or adding a "order by name" at the end of the declaration of the cursor.
I am using SQL Server 2005 64 bit Enterprise Edition (SP3, 9.00.4230.00)
Has anyone experienced the same problem, or knows what is going wrong?
thanks,
Robbert

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

dmoldovan SSCertifiable Points: 6334 More actions · Answer 1

This may be a permissions issue, depending on the user logged in - see the "permissions" section in BOL ---> sys.databases.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

Two things come to mind, but I'm not sure either is applicable. It could be contention, other processes interfering with yours, although I would think you'd either see errors or simply blocking. It could be some issues around the execution plan of the query. I only say this because making a dummy modification would result in a recompile. Next time try simply forcing a recompile on the procedure.

Again, I'm not sure either is applicable. Maybe a little more detail on what's going wrong is needed.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Robbert Hof SSCrazy Points: 2778 More actions · Answer 3

Thanks Grant,

This SP is executed every morning (to gather information about backups etc.)

The execution plan is a suspect, the cursor prcocesses the same number of databases once it goes the wrong way.

It is either all the databases or consistent a subset of the databases.

But why does it suddenly stop processing all the databases. It happens on all our servers.

Robbert