November 27, 2006 at 8:44 am
I have a script that runs alter index on all indexes in a db over a certain frag level. it's a modified version of what is in BOL.
Once a week or so I get an error that
Msg 1914, Level 16, State 3, Line 1
Index cannot be created on object 'dbo.stored_proc_name' because the object is not a user table or view.
anyone have an idea what is causing this?
November 27, 2006 at 8:52 am
you might have to post your script, but i'm guessing that you have not filtered out stored procs or you have a "dodgy" join in your script.....
MVDBA
November 27, 2006 at 8:55 am
/* Declare Variables */
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Clear temp tables of old data
--Truncate TABLE table_index
delete physical_stats where
datepart(d, date) = datepart(d, getdate()-30) and
datepart(yy, date) = datepart(yy, getdate()) and
datepart(m, date) = datepart(m, getdate())
--Create table to hold table names, indexes and sql statements
/*
INSERT table_index (table_name, index_name)
SELECT c.name + '.' + a.name AS table_name, b.name AS index_name
--FROM sysobjects a
--INNER JOIN sysindexes b
ON a.id = b.id
AND b.indid 0 -- table itself
AND b.indid 255 -- text column
AND a.name 'dtproperties'
AND a.type = 'u' and a.name not like 'ms%' and b.name not like '_wa%'
ON c.uid = a.uid
ORDER BY 1
IF @@ERROR 0
BEGIN
RAISERROR('error occured while populating a temp table', 16, 1)
RETURN
END */
-- declare variables:
/*
DECLARE @table_name VARCHAR(80)
declare @index_name VARCHAR(80)
declare @sql VARCHAR(4000)*/
-- Run system view to gather fragmentation data and insert into table
INSERT into physical_stats ( database_id,
object_id,
index_id,
partition_number,
index_type_desc,
alloc_unit_type_desc,
index_depth,
index_level,
avg_fragmentation_in_percent,
fragment_count,
avg_fragment_size_in_pages,
page_count,
avg_page_space_used_in_percent,
record_count,
ghost_record_count,
version_ghost_record_count,
min_record_size_in_bytes,
max_record_size_in_bytes,
avg_record_size_in_bytes,
forwarded_record_count
)
select * from sys.dm_db_index_physical_stats (13, null, null, null, 'detailed')
update physical_stats
set date = getdate() where date is null
/* Prepare list of tables to run maintenance on */
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM physical_stats where
datepart(d, date) = datepart(d, getdate()) and
datepart(yy, date) = datepart(yy, getdate()) and
datepart(m, date) = datepart(m, getdate()) and
avg_fragmentation_in_percent > 10.0
AND index_id > 0
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- 10 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag = 10.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD with (online = on)' ;
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply