February 24, 2009 at 6:25 am
Hi again,
thank you all for your advise.
Now I have the script up and running.
Since the script isn´t that good to be placed in the SSC script section, I will post it here.
The script is based on examples from msdn page and the forum http://www.sqlservercentral.com
use dba
go
-- Verify existence of DBA_dbReindex procedure. If it does exist, drop
it, so that it can be re-created.
if exists (select * from sysobjects where id =
object_id(N'[dbo].[iit_sp_IndexDefragRebuild]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
begin
print 'dropping iit_IndexDefragRebuild.'
drop procedure [dbo].[iit_sp_IndexDefragRebuild]
end
else
print 'iit_sp_IndexDefragRebuild does not currently exist.'
print 'creating iit_sp_IndexDefragRebuild procedure.'
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE iit_sp_IndexDefragRebuild
@dbName varchar(128)
AS
SET NOCOUNT ON
PRINT 'Version: 2009/02/24 12:40:00'
DECLARE @tablename VARCHAR (128)
DECLARE @indexname VARCHAR (255)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @minfrag DECIMAL
DECLARE @maxfrag DECIMAL
DECLARE @TrivPages INT
DECLARE @command NVARCHAR(2000)
DECLARE @command2 NVARCHAR(2000)
--DECLARE @dbname nvarchar (128)
-- Decide on the maximum fragmentation to allow
SELECT @minfrag = 5.0
SELECT @maxfrag = 30.0
SELECT @TrivPages = 500
--SELECT @dbname = 'ipac026'
-- Cleanup possible existing temp tables
IF EXISTS (SELECT * FROM tempdb..sysobjects where name like
'##fraglist%' and type = 'U')
DROP TABLE dbo.##fraglist
IF EXISTS (SELECT * FROM tempdb..sysobjects where name like '#IndDef%'
and type = 'U')
DROP TABLE #IndDefrag
IF EXISTS (SELECT * FROM tempdb..sysobjects where name like
'##Info_Schem%' and type = 'U')
DROP TABLE ##info_schema
-- Create the table
CREATE TABLE ##fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
-- Create the table
CREATE TABLE #IndDefrag (
ObjectName nCHAR (255),
ObjectId INT,
IndexId INT,
CountPages INT,
LogicalFrag DECIMAL,
IndexName CHAR (255)
)
Create table ##info_schema (
table_name nCHAR (255)
)
-- Build the command to execute a DBCC SHOWCONTIG on the specified
database.
select @command2 =
'USE ' + @dbName + '
SELECT Table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE'''
INSERT INTO ##info_schema
EXEC (@command2)
set @command2 = null
-- select * from ##info_schema
DECLARE tables CURSOR FOR
SELECT TABLE_NAME from ##info_schema
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
select @command =
'USE ' + @dbName + '
DBCC SHOWCONTIG (''' + @tablename + ''') WITH FAST,
TABLERESULTS, ALL_INDEXES, NO_INFOMSGS'
-- print @command
INSERT INTO ##fraglist
EXEC (@command)
FETCH NEXT
FROM tables
into @tablename
END
-- Close and deallocate the cursor tables
close tables
deallocate tables
set @command = null
--select * from ##fraglist where LogicalFrag >= @minfrag
--INSERT INTO #IndDefrag (ObjectName,
ObjectId,IndexId,LogicalFrag,IndexName)
set @command = 'USE ' + @dbName
set @command = @command + ' SELECT ObjectName, ObjectId, IndexId,
CountPages,LogicalFrag, IndexName '
set @command = @command + ' FROM ##fraglist WHERE INDEXPROPERTY
(ObjectId, IndexName,''IndexDepth'') > 0 '
--print @command
insert into #IndDefrag
exec (@command)
--select * from #IndDefrag
-- Declare cursor for list of indexes to be defragged
DECLARE Cur_Indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag, IndexName
FROM #IndDefrag
WHERE LogicalFrag >= @minfrag
AND CountPages >= @TrivPages
OPEN Cur_indexes
-- loop through the indexes
FETCH NEXT
FROM Cur_indexes
INTO @tablename, @objectid, @indexid, @frag, @indexname
WHILE @@FETCH_STATUS = 0
BEGIN
IF @frag >= @maxfrag
BEGIN
PRINT 'Executing DBCC DBREINDEX ON ' +
@dbname + '(''' + RTRIM(@tablename) + ''','''+ RTRIM(@indexname) + ''')
WITH NO_INFOMSGS - fragmentation currently '+
RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'USE ' + @dbname + '
DBCC DBREINDEX (''' + RTRIM(@tablename) + ''',''' + RTRIM(@indexname) +
''') WITH NO_INFOMSGS '
END
ELSE
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (''' +
@dbname + ''', ''' + RTRIM(@tablename) + ''', ' + RTRIM(@indexid) + ')
WITH NO_INFOMSGS - fragmentation currently '+
RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (''' +
@dbname + ''', ''' + RTRIM(@tablename) + ''', ' + RTRIM(@indexid) + ')
WITH NO_INFOMSGS '
END
PRINT @execstr
-- EXEC (@execstr)
FETCH NEXT
FROM Cur_indexes
INTO @tablename, @objectid, @indexid,
@frag, @indexname
end
-- Close and deallocate the cursor Cur_indexes
CLOSE Cur_indexes
DEALLOCATE Cur_indexes
-- Drop temporary tables
IF EXISTS (SELECT * FROM tempdb..sysobjects where name like
'##fraglist%' and type = 'U')
DROP TABLE dbo.##fraglist
IF EXISTS (SELECT * FROM tempdb..sysobjects where name like '#IndDef%'
and type = 'U')
DROP TABLE #IndDefrag
IF EXISTS (SELECT * FROM tempdb..sysobjects where name like '##Info%'
and type = 'U')
DROP TABLE ##info_schema
Greetings
Dirk
--
May you never suffer the sentiment of spending a day without any purpose.
@DirkHondong on Twitter
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply