November 21, 2006 at 7:30 am
Is there a way to specifiy all in the Alter Index rebuld statement. I need to run this at night and cycle all the databases in an instance and run the command on all the indexes. I'd like to use a job that will populate a tmp table and cycle the database names in a
Alter Index rebuild all @databasename statement
any help would be appreciated
Thanks
-WM
November 21, 2006 at 7:36 am
CREATE PROCEDURE dbo.REBUILD_INDEX
AS
DECLARE @tablename VARCHAR(255)
DECLARE @tableowner VARCHAR(255)
DECLARE @tablename_header VARCHAR(600)
DECLARE @sql VARCHAR(600)
DECLARE tnames_cursor CURSOR FOR
select 'tablename'=so.name,
'tableowner'=su.name
from dbo.sysobjects so
inner join dbo.sysusers su on so.uid = su.uid
where so.type = 'U'
open tnames_cursor
fetch next from tnames_cursor into @tablename, @tableowner
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
begin
select @tablename_header = '***** Updating [' + rtrim(upper(@tablename)) + '] (' + convert(varchar, getdate(), 20) + ') *****'
print @tablename_header
select @sql = 'DBCC DBREINDEX ([' + @tableowner + '.' + @tablename + '],'''',0 )'
exec ( @sql )
end
fetch next from tnames_cursor into @tablename, @tableowner
end
print ''
print ''
print '***** DBReindex have been updated for all tables (' + convert(varchar,getdate(),20) + ') *****'
close tnames_cursor
deallocate tnames_cursor
GO
November 21, 2006 at 8:20 am
Thanks
Isn't the DBCC Dbreindex an offline operation? I actually have this setup now. I wanted to use the 2005 Alter Index rebuild with the online option as not to interfere with the other nightly operations.
Thanks again
-WM
November 21, 2006 at 8:24 am
That is out of my competence area. I'll leave this question opened to all other DBAs with relevent experience and information.
Good luck with this problem!
November 21, 2006 at 9:54 am
May be you can try this for OnLine..
USE [DBName]
GO
SELECT OBJECT_NAME(X.[Id]) AS TableName,
X.[Name] AS IndexName,
'DBCC INDEXDEFRAG('+DB_NAME()+','+RTRIM(X.[Id])+','+RTRIM(X.IndId)+')' AS OnLineMaintenance
FROM SysIndexes AS X
LEFT JOIN sysobjects SO ON X.[id] = SO.[id]
WHERE xtype = 'U'
AND X.INDID > 0
AND X.INDID < 255
AND (X.STATUS & 64)=0 -- Exclude Statistics
ORDER BY SO.[Name],X.IndId
November 22, 2006 at 8:17 am
this is my script that i combined from scripts here and BOL. this is for all the tables in a database. you can create a separate db to store the fragmentation data, just add a column for the db id then create a cursor within a cursor. the outside cursor is for the databases and the inside cursor is for the tables in the database
i don't see the point since i think it's better to run the jobs on different databases at the same time. if you run them one after the other like you want to then it can take a long time and affect performance during work hours.
I liked Lord of the Rings as well, but I don't think one script to rule them all is the right solution
/* 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 (8, 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;
exec msdb.dbo.sp_send_dbmail
@recipients = 'dba@xxx.com',
@subject = 'Billing 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'
November 22, 2006 at 9:05 am
Thanks for the help
-WM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply