SQL Agent - Could not complete cursor operation because the set options have changed since the cursor was declared. [SQLSTATE 42000] (Error 16958).

Some how I've had this error before.  SQL Agent maintenance job with dynamic SQL in the job step fails with the above error. 

I've had this on sql 2005 and sql 2008; for the most part I thought I'd never see this error again, but alas it keeps coming back.  This time the solution was to just remove the cursor from the dynamic SQL and use a temporary table.  Solutions in the past have been SP4 for sql 2005, sp2 for sql 2008; working on getting the set options right, all these are valid solutions, but none of them worked.  So this is what I had to do.

In particular this script reindexes all user databases on a sql 2008 instance, where the compatability mode of the database is in 9.0.  We have a different step in the maintenance job for databases that are in 10.0 mode.

Fun.

StartReindex_90_compatability.txt (4.17 KB)

I'm just going to decorate this post with a much more upbeat photo of Denise Milani in a captivating, top-heavy series of photographs in a Southwest desert setting. 

Thinking of how big things are, I recently ran into an issue of an index file group growing pretty large, and I wanted to know which indexes were in it, and how big they were.  My biggest concern was non-clustered indexes that were changed to clustered indexes (causing the whole table to move into the index file group).

I have previously found two great scripts, one that shows the size of an index and another that shows what is in a file group.  I combined these two together to get a complete picture of what I needed.

Script for Indexes Size and What File Group they are in:

Select FileGroup = FILEGROUP_NAME(a.data_space_id),index_listing.* from (
Select
    s.[name] as [schema],
    Case
  When i.[name] is null
  Then o.[name]
  Else i.[name]
 end as name,
    i.type_desc,
    space_used_in_mb = (page_count * 8.0 / 1024.0),
    space_used_in_kb = (page_count * 8.0),
 i.object_id,i.index_id
From
    sys.indexes I
Inner Join
    sys.dm_db_index_physical_stats(db_id(), object_id('.'), null, null, null) P
  On I.[object_id] = P.[object_id]
  and I.[index_id] = P.[index_id]
Inner Join
 sys.objects o
  on i.[object_id] = o.[object_id]
Inner Join sys.schemas s
  on o.[schema_id] = s.[schema_id]
) index_listing
INNER JOIN sys.partitions p on index_listing.object_id = p.object_id
 AND index_listing.index_id = p.index_id
Left Join sys.allocation_units a ON a.container_id = CASE WHEN a.type in(1,3)
 THEN p.hobt_id ELSE p.partition_id END AND p.object_id > 1024
--where FILEGROUP_NAME(a.data_space_id) = 'Index_data'
-- and index_listing.type_desc = 'Clustered'
Order by space_used_in_mb Desc