November 15, 2006 at 9:47 am
we have a test cluster for testing SQL 2005. I set it up with Windows 2000 Advanced Server, SQL 2000 SP2. Then upgrade to SP4 and then to SQL 2005 and then to SP1.
Weird thing came up. I'm testing an Alter Index script i put together from a few sources of code.
/* 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' REORGANIZE';
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;
exec msdb.dbo.sp_send_dbmail
@recipients = 'dba@mycompany.com',
@subject = 'database Alter Index Script',
--@attach_query_result_as_file = 'SELECT substring(table_name,1,30) table_name, substring(index_name,1,25) index_name,
--substring(sql_statement,1,60) sql_statement FROM scs..table_index where sql_statement is not null',
@body = 'I will fix this part later'
Whenever I try to run it on one of the clustered instances i get the following error:
Msg 297, Level 16, State 12, Line 49
The user does not have permission to perform this action.
I run parts of the script. I can run the update, but when i try to run the select from the system view I get the error. We are running in mixed mode and sa is enabled. I tried via my PC, locally via terminal services and with a variety of accounts all of which have administrative access to the server. same result.
I tried on a stand alone SQL 2005 server we have and it seems to work. I'm restoring a full db to it now to make sure.
Any idea what it could be?
November 16, 2006 at 1:05 am
Did you change your compatibility level to SQL 2005 (90)? You can not query that system function if you are running in SQL 2000 compatibility mode.
To check current level: Exec sp_dbcmptlevel <Database Name>
To change to SQL 2005 level: sp_dbcmptlevel <Database Name>, 90
To change to SQL 2000 compatibility level: sp_dbcmptlevel <Database Name>, 80
November 16, 2006 at 7:17 am
just double checked, it's 90
November 16, 2006 at 10:45 pm
Hi
select * from sys.dm_db_index_physical_stats (database_id, null, null, null, 'detailed') by default will work only for certain databases..
grant control server to user_name
use this statement to make it work for all the databases....This will work but investigate more on giving this permission to a user before using it in a production environment.
-Vijay
November 17, 2006 at 7:27 am
still doesn't work
i'm staring to think it's a weird cluster issue we have seen with a few things in production where if an instance is on one of the nodes it just doesn't work with a similar error
November 30, 2006 at 9:28 am
i found the problem
the database id's are different and i just copied and pasted the script and forgot to change the db id across servers
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply