December 16, 2009 at 12:34 am
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 @DBid INT
DECLARE @page_verify varchar(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
December 16, 2009 at 2:29 am
This may be a permissions issue, depending on the user logged in - see the "permissions" section in BOL ---> sys.databases.
December 16, 2009 at 8:43 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
December 17, 2009 at 6:02 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply