November 14, 2006 at 8:21 am
Hi,
Is there a way perform INDEXDEFRAG for all the table in the database. The catch here is that i have tables with different OWNERS in the database. Some of the table have 'dbo' as owner and some of the tables have 'xyz' as owner in the database. When i try to run the generic indexdefrag script for all tables it fails with the following error
Executed as user: DOMAIN\XXX. Could not find a table or object named 'XYZ.Tableabc'. Check sysobjects. [SQLSTATE 42S02] (Error 2501) Could not find a table or object named 'XYZ.Tableabc'. Check sysobjects. [SQLSTATE 42S02] (Error 2501). The step failed.
Any help is appreciated
Thank you
November 14, 2006 at 8:36 am
Use the object Ids.
November 14, 2006 at 8:43 am
Thank you for the reply but I didnot get what you were say..
below is the procedure that i am using to do the indexdefrag
CREATE PROCEDURE usp_sp_INDEXDEFRAG
AS
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- 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)
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''dbo.' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END
GO
November 14, 2006 at 8:47 am
I didn't read your last post, but I suspect this will be a great help for you :
USE master
GO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'ThisIsATestTable' AND XType = 'U' AND USER_NAME(uid) = 'IDEAL\Remi')
DROP TABLE [ideal\remi].ThisIsATestTable
GO
CREATE TABLE [ideal\remi].ThisIsATestTable
( A INT NOT NULL IDENTITY(1,1) CONSTRAINT PKIdealRemi PRIMARY KEY CLUSTERED
)
GO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'ThisIsATestTable' AND XType = 'U' AND USER_NAME(uid) = 'dbo')
DROP TABLE dbo.ThisIsATestTable
GO
CREATE TABLE dbo.ThisIsATestTable
( A INT NOT NULL IDENTITY(1,1) CONSTRAINT PKDBO PRIMARY KEY CLUSTERED
)
--Select name from dbo.SysIndexes where object_name (id) = 'ThisIsATestTable'
DBCC INDEXDEFRAG ('master', 'dbo.ThisIsATestTable', 'PKdbo')
--Works
DBCC INDEXDEFRAG ('master', '[ideal\remi].ThisIsATestTable', 'PKIdealRemi')
--Works
DBCC INDEXDEFRAG ('master', 'dbo.ThisIsATestTable', 'PKIdealRemi')
--Errors out, can't find the index
GO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'ThisIsATestTable' AND XType = 'U' AND USER_NAME(uid) = 'IDEAL\Remi')
DROP TABLE [ideal\remi].ThisIsATestTable
GO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'ThisIsATestTable' AND XType = 'U' AND USER_NAME(uid) = 'dbo')
DROP TABLE dbo.ThisIsATestTable
GO
November 14, 2006 at 11:44 am
When trying to use the object id, i found a strange problem
the below query gives me a NULL result.
It only works for 'dbo' owner, it does not work for other owners
USE master
SELECT OBJECT_ID('database_name..[xyz.table1]')
Any suggestion appreciated
November 14, 2006 at 11:49 am
Most likely because you are logged in as DBO.
That's why I was using fully qualified names in my exemple. It fails only when no object exists and it should work under any logins.
November 15, 2006 at 6:56 am
If you don't care about the finer details with regard to fill factors and stuff, why not try something like this:
USE MyDBNameHere
exec sp_MSforeachtable 'DBCC INDEXDEFRAG (''MyDBNameHere'',''?'')'
November 15, 2006 at 7:12 am
Good idea... just have a test run on a test server to make sure this doesn't slow the production to a crawl... or run at night when noone uses the system if possible.
November 15, 2006 at 7:51 am
Thank you both Leifah and Ninja for the suggestion. This does not work for indexdefrag. It work fine for DBCC DBREINDEX.
use test
go
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')" --- works
use test
go
EXEC sp_MSforeachtable @command1="print '?' DBCC INDEXDEFRAG ('?')" --does not work
When i try to run the command , i get the following error
Server: Msg 2583, Level 16, State 3, Line 1
An incorrect number of parameters was given to the DBCC statement.
Any thoughts???
November 15, 2006 at 7:57 am
Ya that can't work... you must specify the index name or id for that command!
Looks like you'll have to fix you script.
November 15, 2006 at 8:03 am
Two single quotes on each side of both dbname and question mark worked fine here. It was a veeery quick and dirty solution, I haven't tested any other syntax variations.
Is it possible there is something with the number of quotes in your command string?
November 15, 2006 at 8:13 am
That doesn't work for me :
USE Documentation
exec sp_MSforeachtable 'DBCC INDEXDEFRAG (''Documentation'',''?'')'
INDEXDEFRAG requires 3 parameters... (index id or name is the missing parameter here).
And since there is not for each indexes, he'll have to figure a way to fix his script!
November 15, 2006 at 8:17 am
Below is the script that worked for me in the test environment..
Please comment if i have made any mistake..
CREATE PROCEDURE inde
AS
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 30.0
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- 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)
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
GO
November 15, 2006 at 8:24 am
You forgot to drop the table #fraglist at the end of the script. Also you don't insert anything in it so no work will be done... ever!
November 15, 2006 at 4:14 pm
Well, now I've tried my quick and dirty syntax at home, on a SQL Server 2000.
As it turns out, my suggestion unfortunately only works on 2005, not 2000. http://www.norbtechnologies.com/support/articles/article01.asp
I found another script, maybe that will work for you?
http://weblogs.sqlteam.com/tarad/archive/2004/07/30/1834.aspx
Leif
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply