DBCC INDEXDEFRAG

Hi Guru,
One of my vendor database has about 500 tables where owner not belong
to dbo. I'm having a hard time to do online defrag from M-F because my
code is not working. Please look at my code and modify it when owner
not belong to DBO.
USE ObjectManager
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[uspOM_OnlineFragmentation]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[uspOM_OnlineFragmentation]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC dbo.uspOM_OnlineFragmentation
@.dbname sysname
AS
SET NOCOUNT ON
CREATE TABLE #UserTables(
[id] INT,
[name] VARCHAR(60)
)
--Create temporary table to hold DBCC SHOWCONTIG output
CREATE TABLE #FragmentationResult(
ObjectName VARCHAR(255), ObjectId INT, IndexName VARCHAR(255),
IndexId INT, [Level] INT, Pages INT, [Rows] INT,
MinimumRecordSize INT, MaximumRecordSize INT,
AverageRecordSize FLOAT, ForwardedRecords INT, Extents INT,
ExtentSwitches INT, AverageFreeBytes FLOAT,
AveragePageDensity FLOAT, ScanDensity FLOAT, BestCount INT,
ActualCount INT, LogicalFragmentation FLOAT,
ExtentFragmentation FLOAT
)
--Create temporary table to hold tables/indexes that require
-- defragmentation
CREATE TABLE #Defragmentation(
[id] INT IDENTITY,
ObjectName VARCHAR(255),
IndexName VARCHAR(255),
ScanDensity FLOAT
)
--Identify all user tables in the current database to analyze
-- fragmentation
INSERT INTO #UserTables([id], [name])
EXEC ('SELECT [id], [name] FROM ' + @.dbname + '.dbo.sysobjects WHERE
xtype = ''U'' and [name] <> ''dtproperties''')
--SELECT [id], [name] INTO #UserTables
-- FROM sysobjects
-- WHERE type = 'U'
-- ORDER BY [id]
--Determine fragmentation of every user table/index
DECLARE @.id INT, @.name VARCHAR(255), @.TableCnt INT
SET @.id = 0
SELECT @.TableCnt = COUNT(*) FROM #UserTables
WHILE @.TableCnt > 0
BEGIN
SELECT TOP 1 @.id=[id], @.name=[name]
FROM #UserTables
WHERE [id] > @.id
INSERT INTO #FragmentationResult
EXEC('USE ' + @.dbname + ' DBCC SHOWCONTIG ([' + @.name + ']) WITH
ALL_INDEXES, TABLERESULTS')
SET @.TableCnt = @.TableCnt - 1
END
--Determine user tables/indexes that require defragmentation
INSERT INTO #Defragmentation
SELECT ObjectName, IndexName, ScanDensity
FROM #FragmentationResult
WHERE IndexID NOT IN(0,255)
AND ScanDensity < 40 --Scan Density is low
AND LogicalFragmentation > 10 --Logical Scan Fragmentation is high
AND PAGES > 8 --Not a very small table
DROP TABLE #FragmentationResult
--Defragment tables/indexes with high fragmentation
DECLARE @.oname VARCHAR(255), @.iname VARCHAR(255), @.sdensity FLOAT
SET @.id = 0
SELECT @.TableCnt = COUNT(*) FROM #Defragmentation
WHILE @.TableCnt > 0
BEGIN
SELECT TOP 1 @.id=[id]
, @.oname = ObjectName
, @.iname = IndexName
, @.sdensity = ScanDensity
FROM #Defragmentation
WHERE [id] > @.id
PRINT '** De-fragmentation #' + CAST(@.id AS VARCHAR(15))+ ' **'
PRINT 'DBCC INDEXDEFRAG on [' + @.oname + '].[' + @.iname
+ '] with ScanDensity = ' + CAST(@.sdensity AS VARCHAR(15)) + '%'
--DBCC DBREINDEX(@.oname, @.iname)
DBCC INDEXDEFRAG(@.dbname,@.oname,@.iname)
SET @.TableCnt = @.TableCnt - 1
END
--Release resources
DROP TABLE #UserTables
DROP TABLE #Defragmentation
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Thanks,
Silaphet,
Hi Silaphet
It's not the code that needs to change, but the roles assigned to whomever
is running this code.
DBO is not a role. It is a user name. You can assign the users who need to
run this to the db_owner or db_ddladmin role. Table owners can only defrag
or showcontig on their own tables.
FROM BOL:
Permissions
DBCC INDEXDEFRAG permissions default to members of the sysadmin fixed server
role, the db_owner and db_ddladmin fixed database role, and the table owner,
and are not transferable.
DBCC SHOWCONTIG permissions default to members of the sysadmin fixed server
role, the db_owner and db_ddladmin fixed database roles, and the table
owner, and are not transferable.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<smounkhaty@.bremer.com> wrote in message
news:1133496455.993570.168870@.g44g2000cwa.googlegr oups.com...
> Hi Guru,
> One of my vendor database has about 500 tables where owner not belong
> to dbo. I'm having a hard time to do online defrag from M-F because my
> code is not working. Please look at my code and modify it when owner
> not belong to DBO.
> USE ObjectManager
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[uspOM_OnlineFragmentation]') and OBJECTPROPERTY(id,
> N'IsProcedure') = 1)
> drop procedure [dbo].[uspOM_OnlineFragmentation]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE PROC dbo.uspOM_OnlineFragmentation
> @.dbname sysname
> AS
> SET NOCOUNT ON
> CREATE TABLE #UserTables(
> [id] INT,
> [name] VARCHAR(60)
> )
> --Create temporary table to hold DBCC SHOWCONTIG output
> CREATE TABLE #FragmentationResult(
> ObjectName VARCHAR(255), ObjectId INT, IndexName VARCHAR(255),
> IndexId INT, [Level] INT, Pages INT, [Rows] INT,
> MinimumRecordSize INT, MaximumRecordSize INT,
> AverageRecordSize FLOAT, ForwardedRecords INT, Extents INT,
> ExtentSwitches INT, AverageFreeBytes FLOAT,
> AveragePageDensity FLOAT, ScanDensity FLOAT, BestCount INT,
> ActualCount INT, LogicalFragmentation FLOAT,
> ExtentFragmentation FLOAT
> )
> --Create temporary table to hold tables/indexes that require
> -- defragmentation
> CREATE TABLE #Defragmentation(
> [id] INT IDENTITY,
> ObjectName VARCHAR(255),
> IndexName VARCHAR(255),
> ScanDensity FLOAT
> )
> --Identify all user tables in the current database to analyze
> -- fragmentation
> INSERT INTO #UserTables([id], [name])
> EXEC ('SELECT [id], [name] FROM ' + @.dbname + '.dbo.sysobjects WHERE
> xtype = ''U'' and [name] <> ''dtproperties''')
> --SELECT [id], [name] INTO #UserTables
> -- FROM sysobjects
> -- WHERE type = 'U'
> -- ORDER BY [id]
> --Determine fragmentation of every user table/index
> DECLARE @.id INT, @.name VARCHAR(255), @.TableCnt INT
> SET @.id = 0
> SELECT @.TableCnt = COUNT(*) FROM #UserTables
> WHILE @.TableCnt > 0
> BEGIN
> SELECT TOP 1 @.id=[id], @.name=[name]
> FROM #UserTables
> WHERE [id] > @.id
> INSERT INTO #FragmentationResult
> EXEC('USE ' + @.dbname + ' DBCC SHOWCONTIG ([' + @.name + ']) WITH
> ALL_INDEXES, TABLERESULTS')
> SET @.TableCnt = @.TableCnt - 1
> END
> --Determine user tables/indexes that require defragmentation
> INSERT INTO #Defragmentation
> SELECT ObjectName, IndexName, ScanDensity
> FROM #FragmentationResult
> WHERE IndexID NOT IN(0,255)
> AND ScanDensity < 40 --Scan Density is low
> AND LogicalFragmentation > 10 --Logical Scan Fragmentation is high
> AND PAGES > 8 --Not a very small table
> DROP TABLE #FragmentationResult
> --Defragment tables/indexes with high fragmentation
> DECLARE @.oname VARCHAR(255), @.iname VARCHAR(255), @.sdensity FLOAT
> SET @.id = 0
> SELECT @.TableCnt = COUNT(*) FROM #Defragmentation
> WHILE @.TableCnt > 0
> BEGIN
> SELECT TOP 1 @.id=[id]
> , @.oname = ObjectName
> , @.iname = IndexName
> , @.sdensity = ScanDensity
> FROM #Defragmentation
> WHERE [id] > @.id
> PRINT '** De-fragmentation #' + CAST(@.id AS VARCHAR(15))+ ' **'
> PRINT 'DBCC INDEXDEFRAG on [' + @.oname + '].[' + @.iname
> + '] with ScanDensity = ' + CAST(@.sdensity AS VARCHAR(15)) + '%'
> --DBCC DBREINDEX(@.oname, @.iname)
> DBCC INDEXDEFRAG(@.dbname,@.oname,@.iname)
> SET @.TableCnt = @.TableCnt - 1
> END
> --Release resources
> DROP TABLE #UserTables
> DROP TABLE #Defragmentation
> SET NOCOUNT OFF
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> Thanks,
> Silaphet,
>

DBCC INDEXDEFRAG

Hi Guru,
One of my vendor database has about 500 tables where owner not belong
to dbo. I'm having a hard time to do online defrag from M-F because my
code is not working. Please look at my code and modify it when owner
not belong to DBO.
USE ObjectManager
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[uspOM_OnlineFragmentation]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[uspOM_OnlineFragmentation]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC dbo.uspOM_OnlineFragmentation
@.dbname sysname
AS
SET NOCOUNT ON
CREATE TABLE #UserTables(
[id] INT,
[name] VARCHAR(60)
)
--Create temporary table to hold DBCC SHOWCONTIG output
CREATE TABLE #FragmentationResult(
ObjectName VARCHAR(255), ObjectId INT, IndexName VARCHAR(255),
IndexId INT, [Level] INT, Pages INT, [Rows] INT,
MinimumRecordSize INT, MaximumRecordSize INT,
AverageRecordSize FLOAT, ForwardedRecords INT, Extents INT,
ExtentSwitches INT, AverageFreeBytes FLOAT,
AveragePageDensity FLOAT, ScanDensity FLOAT, BestCount INT,
ActualCount INT, LogicalFragmentation FLOAT,
ExtentFragmentation FLOAT
)
--Create temporary table to hold tables/indexes that require
-- defragmentation
CREATE TABLE #Defragmentation(
[id] INT IDENTITY,
ObjectName VARCHAR(255),
IndexName VARCHAR(255),
ScanDensity FLOAT
)
--Identify all user tables in the current database to analyze
-- fragmentation
INSERT INTO #UserTables([id], [name])
EXEC ('SELECT [id], [name] FROM ' + @.dbname + '.dbo.sysobjects WHERE
xtype = ''U'' and [name] <> ''dtproperties''')
--SELECT [id], [name] INTO #UserTables
-- FROM sysobjects
-- WHERE type = 'U'
-- ORDER BY [id]
--Determine fragmentation of every user table/index
DECLARE @.id INT, @.name VARCHAR(255), @.TableCnt INT
SET @.id = 0
SELECT @.TableCnt = COUNT(*) FROM #UserTables
WHILE @.TableCnt > 0
BEGIN
SELECT TOP 1 @.id=[id], @.name=[name]
FROM #UserTables
WHERE [id] > @.id
INSERT INTO #FragmentationResult
EXEC('USE ' + @.dbname + ' DBCC SHOWCONTIG ([' + @.name + ']) WITH
ALL_INDEXES, TABLERESULTS')
SET @.TableCnt = @.TableCnt - 1
END
--Determine user tables/indexes that require defragmentation
INSERT INTO #Defragmentation
SELECT ObjectName, IndexName, ScanDensity
FROM #FragmentationResult
WHERE IndexID NOT IN(0,255)
AND ScanDensity < 40 --Scan Density is low
AND LogicalFragmentation > 10 --Logical Scan Fragmentation is high
AND PAGES > 8 --Not a very small table
DROP TABLE #FragmentationResult
--Defragment tables/indexes with high fragmentation
DECLARE @.oname VARCHAR(255), @.iname VARCHAR(255), @.sdensity FLOAT
SET @.id = 0
SELECT @.TableCnt = COUNT(*) FROM #Defragmentation
WHILE @.TableCnt > 0
BEGIN
SELECT TOP 1 @.id=[id]
, @.oname = ObjectName
, @.iname = IndexName
, @.sdensity = ScanDensity
FROM #Defragmentation
WHERE [id] > @.id
PRINT '** De-fragmentation #' + CAST(@.id AS VARCHAR(15))+ ' **'
PRINT 'DBCC INDEXDEFRAG on [' + @.oname + '].[' + @.iname
+ '] with ScanDensity = ' + CAST(@.sdensity AS VARCHAR(15)) + '%'
--DBCC DBREINDEX(@.oname, @.iname)
DBCC INDEXDEFRAG(@.dbname,@.oname,@.iname)
SET @.TableCnt = @.TableCnt - 1
END
--Release resources
DROP TABLE #UserTables
DROP TABLE #Defragmentation
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Thanks,
Silaphet,Hi Silaphet
It's not the code that needs to change, but the roles assigned to whomever
is running this code.
DBO is not a role. It is a user name. You can assign the users who need to
run this to the db_owner or db_ddladmin role. Table owners can only defrag
or showcontig on their own tables.
--
FROM BOL:
Permissions
DBCC INDEXDEFRAG permissions default to members of the sysadmin fixed server
role, the db_owner and db_ddladmin fixed database role, and the table owner,
and are not transferable.
DBCC SHOWCONTIG permissions default to members of the sysadmin fixed server
role, the db_owner and db_ddladmin fixed database roles, and the table
owner, and are not transferable.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<smounkhaty@.bremer.com> wrote in message
news:1133496455.993570.168870@.g44g2000cwa.googlegroups.com...
> Hi Guru,
> One of my vendor database has about 500 tables where owner not belong
> to dbo. I'm having a hard time to do online defrag from M-F because my
> code is not working. Please look at my code and modify it when owner
> not belong to DBO.
> USE ObjectManager
> GO
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[uspOM_OnlineFragmentation]') and OBJECTPROPERTY(id,
> N'IsProcedure') = 1)
> drop procedure [dbo].[uspOM_OnlineFragmentation]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE PROC dbo.uspOM_OnlineFragmentation
> @.dbname sysname
> AS
> SET NOCOUNT ON
> CREATE TABLE #UserTables(
> [id] INT,
> [name] VARCHAR(60)
> )
> --Create temporary table to hold DBCC SHOWCONTIG output
> CREATE TABLE #FragmentationResult(
> ObjectName VARCHAR(255), ObjectId INT, IndexName VARCHAR(255),
> IndexId INT, [Level] INT, Pages INT, [Rows] INT,
> MinimumRecordSize INT, MaximumRecordSize INT,
> AverageRecordSize FLOAT, ForwardedRecords INT, Extents INT,
> ExtentSwitches INT, AverageFreeBytes FLOAT,
> AveragePageDensity FLOAT, ScanDensity FLOAT, BestCount INT,
> ActualCount INT, LogicalFragmentation FLOAT,
> ExtentFragmentation FLOAT
> )
> --Create temporary table to hold tables/indexes that require
> -- defragmentation
> CREATE TABLE #Defragmentation(
> [id] INT IDENTITY,
> ObjectName VARCHAR(255),
> IndexName VARCHAR(255),
> ScanDensity FLOAT
> )
> --Identify all user tables in the current database to analyze
> -- fragmentation
> INSERT INTO #UserTables([id], [name])
> EXEC ('SELECT [id], [name] FROM ' + @.dbname + '.dbo.sysobjects WHERE
> xtype = ''U'' and [name] <> ''dtproperties''')
> --SELECT [id], [name] INTO #UserTables
> -- FROM sysobjects
> -- WHERE type = 'U'
> -- ORDER BY [id]
> --Determine fragmentation of every user table/index
> DECLARE @.id INT, @.name VARCHAR(255), @.TableCnt INT
> SET @.id = 0
> SELECT @.TableCnt = COUNT(*) FROM #UserTables
> WHILE @.TableCnt > 0
> BEGIN
> SELECT TOP 1 @.id=[id], @.name=[name]
> FROM #UserTables
> WHERE [id] > @.id
> INSERT INTO #FragmentationResult
> EXEC('USE ' + @.dbname + ' DBCC SHOWCONTIG ([' + @.name + ']) WITH
> ALL_INDEXES, TABLERESULTS')
> SET @.TableCnt = @.TableCnt - 1
> END
> --Determine user tables/indexes that require defragmentation
> INSERT INTO #Defragmentation
> SELECT ObjectName, IndexName, ScanDensity
> FROM #FragmentationResult
> WHERE IndexID NOT IN(0,255)
> AND ScanDensity < 40 --Scan Density is low
> AND LogicalFragmentation > 10 --Logical Scan Fragmentation is high
> AND PAGES > 8 --Not a very small table
> DROP TABLE #FragmentationResult
> --Defragment tables/indexes with high fragmentation
> DECLARE @.oname VARCHAR(255), @.iname VARCHAR(255), @.sdensity FLOAT
> SET @.id = 0
> SELECT @.TableCnt = COUNT(*) FROM #Defragmentation
> WHILE @.TableCnt > 0
> BEGIN
> SELECT TOP 1 @.id=[id]
> , @.oname = ObjectName
> , @.iname = IndexName
> , @.sdensity = ScanDensity
> FROM #Defragmentation
> WHERE [id] > @.id
> PRINT '** De-fragmentation #' + CAST(@.id AS VARCHAR(15))+ ' **'
> PRINT 'DBCC INDEXDEFRAG on [' + @.oname + '].[' + @.iname
> + '] with ScanDensity = ' + CAST(@.sdensity AS VARCHAR(15)) + '%'
> --DBCC DBREINDEX(@.oname, @.iname)
> DBCC INDEXDEFRAG(@.dbname,@.oname,@.iname)
> SET @.TableCnt = @.TableCnt - 1
> END
> --Release resources
> DROP TABLE #UserTables
> DROP TABLE #Defragmentation
> SET NOCOUNT OFF
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> Thanks,
> Silaphet,
>

DBCC INDEXDEFRAG

Hi Guru,
One of my vendor database has about 500 tables where owner not belong
to dbo. I'm having a hard time to do online defrag from M-F because my
code is not working. Please look at my code and modify it when owner
not belong to DBO.
USE ObjectManager
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[uspOM_OnlineFragmentation]') and OBJECTPROPERTY(i
d,
N'IsProcedure') = 1)
drop procedure [dbo].[uspOM_OnlineFragmentation]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROC dbo.uspOM_OnlineFragmentation
@.dbname sysname
AS
SET NOCOUNT ON
CREATE TABLE #UserTables(
[id] INT,
[name] VARCHAR(60)
)
--Create temporary table to hold DBCC SHOWCONTIG output
CREATE TABLE #FragmentationResult(
ObjectName VARCHAR(255), ObjectId INT, IndexName VARCHAR(255),
IndexId INT, [Level] INT, Pages INT, [Rows] INT,
MinimumRecordSize INT, MaximumRecordSize INT,
AverageRecordSize FLOAT, ForwardedRecords INT, Extents INT,
ExtentSwitches INT, AverageFreeBytes FLOAT,
AveragePageDensity FLOAT, ScanDensity FLOAT, BestCount INT,
ActualCount INT, LogicalFragmentation FLOAT,
ExtentFragmentation FLOAT
)
--Create temporary table to hold tables/indexes that require
-- defragmentation
CREATE TABLE #Defragmentation(
[id] INT IDENTITY,
ObjectName VARCHAR(255),
IndexName VARCHAR(255),
ScanDensity FLOAT
)
--Identify all user tables in the current database to analyze
-- fragmentation
INSERT INTO #UserTables([id], [name])
EXEC ('SELECT [id], [name] FROM ' + @.dbname + '.dbo.sysobjects WHERE
xtype = ''U'' and [name] <> ''dtproperties''')
--SELECT [id], [name] INTO #UserTables
-- FROM sysobjects
-- WHERE type = 'U'
-- ORDER BY [id]
--Determine fragmentation of every user table/index
DECLARE @.id INT, @.name VARCHAR(255), @.TableCnt INT
SET @.id = 0
SELECT @.TableCnt = COUNT(*) FROM #UserTables
WHILE @.TableCnt > 0
BEGIN
SELECT TOP 1 @.id=[id], @.name=[name]
FROM #UserTables
WHERE [id] > @.id
INSERT INTO #FragmentationResult
EXEC('USE ' + @.dbname + ' DBCC SHOWCONTIG ([' + @.name + ']) WITH
ALL_INDEXES, TABLERESULTS')
SET @.TableCnt = @.TableCnt - 1
END
--Determine user tables/indexes that require defragmentation
INSERT INTO #Defragmentation
SELECT ObjectName, IndexName, ScanDensity
FROM #FragmentationResult
WHERE IndexID NOT IN(0,255)
AND ScanDensity < 40 --Scan Density is low
AND LogicalFragmentation > 10 --Logical Scan Fragmentation is high
AND PAGES > 8 --Not a very small table
DROP TABLE #FragmentationResult
--Defragment tables/indexes with high fragmentation
DECLARE @.oname VARCHAR(255), @.iname VARCHAR(255), @.sdensity FLOAT
SET @.id = 0
SELECT @.TableCnt = COUNT(*) FROM #Defragmentation
WHILE @.TableCnt > 0
BEGIN
SELECT TOP 1 @.id=[id]
, @.oname = ObjectName
, @.iname = IndexName
, @.sdensity = ScanDensity
FROM #Defragmentation
WHERE [id] > @.id
PRINT '** De-fragmentation #' + CAST(@.id AS VARCHAR(15))+ ' **'
PRINT 'DBCC INDEXDEFRAG on [' + @.oname + '].[' + @.iname
+ '] with ScanDensity = ' + CAST(@.sdensity AS VARCHAR(15)) + '%'
--DBCC DBREINDEX(@.oname, @.iname)
DBCC INDEXDEFRAG(@.dbname,@.oname,@.iname)
SET @.TableCnt = @.TableCnt - 1
END
--Release resources
DROP TABLE #UserTables
DROP TABLE #Defragmentation
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Thanks,
Silaphet,Hi Silaphet
It's not the code that needs to change, but the roles assigned to whomever
is running this code.
DBO is not a role. It is a user name. You can assign the users who need to
run this to the db_owner or db_ddladmin role. Table owners can only defrag
or showcontig on their own tables.
FROM BOL:
Permissions
DBCC INDEXDEFRAG permissions default to members of the sysadmin fixed server
role, the db_owner and db_ddladmin fixed database role, and the table owner,
and are not transferable.
DBCC SHOWCONTIG permissions default to members of the sysadmin fixed server
role, the db_owner and db_ddladmin fixed database roles, and the table
owner, and are not transferable.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<smounkhaty@.bremer.com> wrote in message
news:1133496455.993570.168870@.g44g2000cwa.googlegroups.com...
> Hi Guru,
> One of my vendor database has about 500 tables where owner not belong
> to dbo. I'm having a hard time to do online defrag from M-F because my
> code is not working. Please look at my code and modify it when owner
> not belong to DBO.
> USE ObjectManager
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[uspOM_OnlineFragmentation]') and OBJECTPROPERTY
(id,
> N'IsProcedure') = 1)
> drop procedure [dbo].[uspOM_OnlineFragmentation]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE PROC dbo.uspOM_OnlineFragmentation
> @.dbname sysname
> AS
> SET NOCOUNT ON
> CREATE TABLE #UserTables(
> [id] INT,
> [name] VARCHAR(60)
> )
> --Create temporary table to hold DBCC SHOWCONTIG output
> CREATE TABLE #FragmentationResult(
> ObjectName VARCHAR(255), ObjectId INT, IndexName VARCHAR(255),
> IndexId INT, [Level] INT, Pages INT, [Rows] INT,
> MinimumRecordSize INT, MaximumRecordSize INT,
> AverageRecordSize FLOAT, ForwardedRecords INT, Extents INT,
> ExtentSwitches INT, AverageFreeBytes FLOAT,
> AveragePageDensity FLOAT, ScanDensity FLOAT, BestCount INT,
> ActualCount INT, LogicalFragmentation FLOAT,
> ExtentFragmentation FLOAT
> )
> --Create temporary table to hold tables/indexes that require
> -- defragmentation
> CREATE TABLE #Defragmentation(
> [id] INT IDENTITY,
> ObjectName VARCHAR(255),
> IndexName VARCHAR(255),
> ScanDensity FLOAT
> )
> --Identify all user tables in the current database to analyze
> -- fragmentation
> INSERT INTO #UserTables([id], [name])
> EXEC ('SELECT [id], [name] FROM ' + @.dbname + '.dbo.sysobjects WHE
RE
> xtype = ''U'' and [name] <> ''dtproperties''')
> --SELECT [id], [name] INTO #UserTables
> -- FROM sysobjects
> -- WHERE type = 'U'
> -- ORDER BY [id]
> --Determine fragmentation of every user table/index
> DECLARE @.id INT, @.name VARCHAR(255), @.TableCnt INT
> SET @.id = 0
> SELECT @.TableCnt = COUNT(*) FROM #UserTables
> WHILE @.TableCnt > 0
> BEGIN
> SELECT TOP 1 @.id=[id], @.name=[name]
> FROM #UserTables
> WHERE [id] > @.id
> INSERT INTO #FragmentationResult
> EXEC('USE ' + @.dbname + ' DBCC SHOWCONTIG ([' + @.name + ']) WITH
> ALL_INDEXES, TABLERESULTS')
> SET @.TableCnt = @.TableCnt - 1
> END
> --Determine user tables/indexes that require defragmentation
> INSERT INTO #Defragmentation
> SELECT ObjectName, IndexName, ScanDensity
> FROM #FragmentationResult
> WHERE IndexID NOT IN(0,255)
> AND ScanDensity < 40 --Scan Density is low
> AND LogicalFragmentation > 10 --Logical Scan Fragmentation is high
> AND PAGES > 8 --Not a very small table
> DROP TABLE #FragmentationResult
> --Defragment tables/indexes with high fragmentation
> DECLARE @.oname VARCHAR(255), @.iname VARCHAR(255), @.sdensity FLOAT
> SET @.id = 0
> SELECT @.TableCnt = COUNT(*) FROM #Defragmentation
> WHILE @.TableCnt > 0
> BEGIN
> SELECT TOP 1 @.id=[id]
> , @.oname = ObjectName
> , @.iname = IndexName
> , @.sdensity = ScanDensity
> FROM #Defragmentation
> WHERE [id] > @.id
> PRINT '** De-fragmentation #' + CAST(@.id AS VARCHAR(15))+ ' **'
> PRINT 'DBCC INDEXDEFRAG on [' + @.oname + '].[' + @.iname
> + '] with ScanDensity = ' + CAST(@.sdensity AS VARCHAR(15)) + '%'
> --DBCC DBREINDEX(@.oname, @.iname)
> DBCC INDEXDEFRAG(@.dbname,@.oname,@.iname)
> SET @.TableCnt = @.TableCnt - 1
> END
> --Release resources
> DROP TABLE #UserTables
> DROP TABLE #Defragmentation
> SET NOCOUNT OFF
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> Thanks,
> Silaphet,
>

dbcc dbreindex problems

sql2k sp3
When running:
dbcc dbreindex ('dev.dbo.transdtl')
I get:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 1. Most significant primary key is '8216076'.
The statement has been terminated.
Now this doesn't make sense. TransDTLKey is the PK column for this table. No
other unique constraints are defined. So I:
select transdtlkey
from transdtl
group by transdtlkey
having count(transdtlkey) > 1
and get back 0 records.
Ideas?
TIA, ChrisRSmells like data corruption to me. Did you DBCC CHECKTABLE? Also, I vaguely recall this (I might be
have been dreaming, though), I'd search the newsgroups archive as well...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:2C94CEF3-9E9C-4C11-8EE6-B302CEC8ABBD@.microsoft.com...
> sql2k sp3
> When running:
> dbcc dbreindex ('dev.dbo.transdtl')
> I get:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 1. Most significant primary key is '8216076'.
> The statement has been terminated.
> Now this doesn't make sense. TransDTLKey is the PK column for this table. No
> other unique constraints are defined. So I:
> select transdtlkey
> from transdtl
> group by transdtlkey
> having count(transdtlkey) > 1
> and get back 0 records.
>
> Ideas?
> TIA, ChrisR|||You are correct:
Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 2511: [Microsoft][ODBC SQL
Server Driver][SQL Server]Table error: Object ID 1945773989, Index ID 0. Keys
out of order on page (1:817776), slots 57 and 58.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
1945773989, Index ID 16. Keys out of order on page (1:637458), slots 128 and
129.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 2 consistency errors in table 'TransDtl' (object ID 1945773989).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 2 consistency errors in database 'DEV'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_rebuild is the minimum
repair level for the errors found by DBCC CHECKDB (DEV ).
The problem is though that I ran dbcc checkdb with repair_rebuild and it
errors out because of this issue. So the Checkdb cant fix it and neither can
the Reindex. Any other ideas? A few years back at another job we had to:
Put the data into new tables.
Drop the old table.
Rename the new table.
to get rid of other strange behavior and it seemed to do the trick. I may
try this again here.
"Tibor Karaszi" wrote:
> Smells like data corruption to me. Did you DBCC CHECKTABLE? Also, I vaguely recall this (I might be
> have been dreaming, though), I'd search the newsgroups archive as well...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:2C94CEF3-9E9C-4C11-8EE6-B302CEC8ABBD@.microsoft.com...
> > sql2k sp3
> >
> > When running:
> >
> > dbcc dbreindex ('dev.dbo.transdtl')
> >
> > I get:
> >
> > Server: Msg 1505, Level 16, State 1, Line 1
> > CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> > ID 1. Most significant primary key is '8216076'.
> > The statement has been terminated.
> >
> > Now this doesn't make sense. TransDTLKey is the PK column for this table. No
> > other unique constraints are defined. So I:
> >
> > select transdtlkey
> > from transdtl
> > group by transdtlkey
> > having count(transdtlkey) > 1
> >
> > and get back 0 records.
> >
> >
> > Ideas?
> >
> > TIA, ChrisR
>
>|||ChrisR wrote:
> You are correct:
> Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 2511:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> 1945773989, Index ID 0. Keys out of order on page (1:817776), slots
> 57 and 58. [Microsoft][ODBC SQL Server Driver][SQL Server]Table
> error: Object ID 1945773989, Index ID 16. Keys out of order on page
> (1:637458), slots 128 and 129.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0
> allocation errors and 2 consistency errors in table 'TransDtl'
> (object ID 1945773989). [Microsoft][ODBC SQL Server Driver][SQL
> Server]CHECKDB found 0 allocation errors and 2 consistency errors in
> database 'DEV'. [Microsoft][ODBC SQL Server Driver][SQL
> Server]repair_rebuild is the minimum repair level for the errors
> found by DBCC CHECKDB (DEV ).
>
> The problem is though that I ran dbcc checkdb with repair_rebuild and
> it errors out because of this issue. So the Checkdb cant fix it and
> neither can the Reindex. Any other ideas? A few years back at another
> job we had to:
> Put the data into new tables.
> Drop the old table.
> Rename the new table.
> to get rid of other strange behavior and it seemed to do the trick. I
> may try this again here.
>
If you script out the table and data, can you try using the
REPAIR_ALLOW_DATA_LOSS option with dbcc checkdb. You can do this under a
user transaction to examine the differences (or data loss) when compared
to the copy of the table. If you find the repairs sufficient, you can
update rows from the table copy and then insert missing rows from the
table copy to get the tables in sync.
You obviously want to script out this whole procedure before you commit
to tying up production data for too long. OTOH, if your production table
is corrupt, the sooner you fix it, the better.
--
David Gugick
Imceda Software
www.imceda.com|||Im lucky in that this is a development db.
"David Gugick" wrote:
> ChrisR wrote:
> > You are correct:
> >
> > Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 2511:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> > 1945773989, Index ID 0. Keys out of order on page (1:817776), slots
> > 57 and 58. [Microsoft][ODBC SQL Server Driver][SQL Server]Table
> > error: Object ID 1945773989, Index ID 16. Keys out of order on page
> > (1:637458), slots 128 and 129.
> > [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0
> > allocation errors and 2 consistency errors in table 'TransDtl'
> > (object ID 1945773989). [Microsoft][ODBC SQL Server Driver][SQL
> > Server]CHECKDB found 0 allocation errors and 2 consistency errors in
> > database 'DEV'. [Microsoft][ODBC SQL Server Driver][SQL
> > Server]repair_rebuild is the minimum repair level for the errors
> > found by DBCC CHECKDB (DEV ).
> >
> >
> > The problem is though that I ran dbcc checkdb with repair_rebuild and
> > it errors out because of this issue. So the Checkdb cant fix it and
> > neither can the Reindex. Any other ideas? A few years back at another
> > job we had to:
> >
> > Put the data into new tables.
> > Drop the old table.
> > Rename the new table.
> >
> > to get rid of other strange behavior and it seemed to do the trick. I
> > may try this again here.
> >
> >
> If you script out the table and data, can you try using the
> REPAIR_ALLOW_DATA_LOSS option with dbcc checkdb. You can do this under a
> user transaction to examine the differences (or data loss) when compared
> to the copy of the table. If you find the repairs sufficient, you can
> update rows from the table copy and then insert missing rows from the
> table copy to get the tables in sync.
> You obviously want to script out this whole procedure before you commit
> to tying up production data for too long. OTOH, if your production table
> is corrupt, the sooner you fix it, the better.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||1. Read about the specific error numbers in the updated Books Online. The update includes detailed
explanations for all corruption types of errors, including suggested actions. Execute the DBCC CHECK
command from QA so you get all error numbers back (Maint Wiz only return error number from first
error that a command generates).
2. Generic recommendations:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:BE9354B5-AB7D-407E-ABCB-60E8A46233F2@.microsoft.com...
> You are correct:
> Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 2511: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Table error: Object ID 1945773989, Index ID 0. Keys
> out of order on page (1:817776), slots 57 and 58.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
> 1945773989, Index ID 16. Keys out of order on page (1:637458), slots 128 and
> 129.
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
> errors and 2 consistency errors in table 'TransDtl' (object ID 1945773989).
> [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
> errors and 2 consistency errors in database 'DEV'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]repair_rebuild is the minimum
> repair level for the errors found by DBCC CHECKDB (DEV ).
>
> The problem is though that I ran dbcc checkdb with repair_rebuild and it
> errors out because of this issue. So the Checkdb cant fix it and neither can
> the Reindex. Any other ideas? A few years back at another job we had to:
> Put the data into new tables.
> Drop the old table.
> Rename the new table.
> to get rid of other strange behavior and it seemed to do the trick. I may
> try this again here.
>
> "Tibor Karaszi" wrote:
>> Smells like data corruption to me. Did you DBCC CHECKTABLE? Also, I vaguely recall this (I might
>> be
>> have been dreaming, though), I'd search the newsgroups archive as well...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:2C94CEF3-9E9C-4C11-8EE6-B302CEC8ABBD@.microsoft.com...
>> > sql2k sp3
>> >
>> > When running:
>> >
>> > dbcc dbreindex ('dev.dbo.transdtl')
>> >
>> > I get:
>> >
>> > Server: Msg 1505, Level 16, State 1, Line 1
>> > CREATE UNIQUE INDEX terminated because a duplicate key was found for index
>> > ID 1. Most significant primary key is '8216076'.
>> > The statement has been terminated.
>> >
>> > Now this doesn't make sense. TransDTLKey is the PK column for this table. No
>> > other unique constraints are defined. So I:
>> >
>> > select transdtlkey
>> > from transdtl
>> > group by transdtlkey
>> > having count(transdtlkey) > 1
>> >
>> > and get back 0 records.
>> >
>> >
>> > Ideas?
>> >
>> > TIA, ChrisR
>>

DBCC DBREINDEX Help

DBCC DBREINDEX ('pubs.dbo.authors', UPKCL_auidind, 80)
when the above given command is executed i get the result as
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
instead of
Index (ID = 1) is being rebuilt.
Index (ID = 2) is being rebuilt.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
how can i get the individual index id once they are reindexed?
pls respond me as soon as possible
Regards
Sudarshan SelvarajaWhy do you need the individual index ID? You specified an index so you
should know what it's ID is. In your case only that one index will be
rebuilt.
Andrew J. Kelly SQL MVP
"sudarshan selvaraja" <sudarshanselvaraja@.discussions.microsoft.com> wrote
in message news:136BB536-3A7D-462E-9FC0-9A714A40FF2C@.microsoft.com...
> DBCC DBREINDEX ('pubs.dbo.authors', UPKCL_auidind, 80)
> when the above given command is executed i get the result as
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> instead of
> Index (ID = 1) is being rebuilt.
> Index (ID = 2) is being rebuilt.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> how can i get the individual index id once they are reindexed?
> pls respond me as soon as possible
> Regards
> Sudarshan Selvaraja
>
>|||Andrew,
He has taken the example from BOL. Coming to think of it, though I never
realised, I have never got any message when index is being rebuilt.
I do it for defrag once in a while, so use showcontig after I run this.
And, to answer your question. The following doesn't return any messages
either.
use northwind
DBCC DBREINDEX (Employees, '', 80)
And its got two indexes.|||To be honest I can't remember if it shows these messages or not. But if you
reindex a clustered index that is not unique it will have to rebuild all the
non-clustered indexes as well. This is due to the way it enforces uniqueness
on the clustered index in 2000. So what you see may depend on if the
clustered index is unique or not.
Andrew J. Kelly SQL MVP
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:1AC7EEDA-F769-4375-82FB-DD4F9AD9F235@.microsoft.com...
> Andrew,
> He has taken the example from BOL. Coming to think of it, though I never
> realised, I have never got any message when index is being rebuilt.
> I do it for defrag once in a while, so use showcontig after I run this.
> And, to answer your question. The following doesn't return any messages
> either.
> use northwind
> DBCC DBREINDEX (Employees, '', 80)
> And its got two indexes.|||In BOL they have mentioned that when DBCC DBREINDEX is executed the output
will be
Index (ID = 1) is being rebuilt.
Index (ID = 2) is being rebuilt.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator
Actually i need this because when i reindex all the tables in the db i need
to know what are the index reindexed.
Regards
Sudarshan Selvaraja
"Andrew J. Kelly" wrote:

> To be honest I can't remember if it shows these messages or not. But if y
ou
> reindex a clustered index that is not unique it will have to rebuild all t
he
> non-clustered indexes as well. This is due to the way it enforces uniquene
ss
> on the clustered index in 2000. So what you see may depend on if the
> clustered index is unique or not.
> --
> Andrew J. Kelly SQL MVP
>
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:1AC7EEDA-F769-4375-82FB-DD4F9AD9F235@.microsoft.com...
>
>|||USE Northwind --Enter the name of the database you want to reindex
go
DECLARE @.TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@.TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
When the above given code is executed i get the output as
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I dont know exactly what are the index reindexed ...any suggestion pls
"sudarshan selvaraja" wrote:
> In BOL they have mentioned that when DBCC DBREINDEX is executed the output
> will be
> Index (ID = 1) is being rebuilt.
> Index (ID = 2) is being rebuilt.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator
> Actually i need this because when i reindex all the tables in the db i nee
d
> to know what are the index reindexed.
> Regards
> Sudarshan Selvaraja
>
> "Andrew J. Kelly" wrote:
>|||This will reindex all of them. Do you really need to know which ones when
it is all of them? You can find out what indexes are on each table by
looking in sysindexes or sp_helpindex. You can also build a cursor based on
either and rebuild the indexes one at a time but why bother if you are going
to do them all anyway? There is an example in BOL under DBCC SHOWCONTIG
that will only reindex or Defrag indexes above a certain fragmentation
level. Maybe this is more of what you want.
Andrew J. Kelly SQL MVP
"sudarshan selvaraja" <sudarshanselvaraja@.discussions.microsoft.com> wrote
in message news:BE1AB6D9-40BC-40F4-8E62-679497A6BB61@.microsoft.com...
> USE Northwind --Enter the name of the database you want to reindex
> go
> DECLARE @.TableName varchar(255)
> DECLARE TableCursor CURSOR FOR
> SELECT table_name FROM information_schema.tables
> WHERE table_type = 'base table'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> DBCC DBREINDEX(@.TableName,' ',90)
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> When the above given code is executed i get the output as
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> I dont know exactly what are the index reindexed ...any suggestion pls
>
>
> "sudarshan selvaraja" wrote:
>|||Ok thx andrew i thought i could get the individual index id when it is
reindexed..any how it seems that i can use the code in BOL given with
showcontig..
"Andrew J. Kelly" wrote:

> This will reindex all of them. Do you really need to know which ones when
> it is all of them? You can find out what indexes are on each table by
> looking in sysindexes or sp_helpindex. You can also build a cursor based o
n
> either and rebuild the indexes one at a time but why bother if you are goi
ng
> to do them all anyway? There is an example in BOL under DBCC SHOWCONTIG
> that will only reindex or Defrag indexes above a certain fragmentation
> level. Maybe this is more of what you want.
> --
> Andrew J. Kelly SQL MVP
>
> "sudarshan selvaraja" <sudarshanselvaraja@.discussions.microsoft.com> wrote
> in message news:BE1AB6D9-40BC-40F4-8E62-679497A6BB61@.microsoft.com...
>
>

DBCC Commands

Hi

Can we use

DBCC CHECKIDENT ('dbo.MyTable', RESEED, 0)

Command in Stored procedure. Which executes daily.

Thanks

Sridhar K

yes u can very well do. You can create a sp and then schedule the sp though Job. else u can include the script directly in Job

what is the probelm u faced when u tried?

Madhu

|||

Thanks Madhu

I'm not having any problem. But I would like to know and as per my knowledge am not sure that these statements can execute daily on DB. Somebody said we have to use it periodically in maintenance.

Thanks

Sridhar K

|||

Yes there are dbcc commands which used for database maintenance. Those are generally used periodically like DBCC DBREINDEX/IndexDefrag/Checkalloc/CheckDB etc etc...

Madhu

DBCC CHECKTABLE with REPAIR_REBUILD

How can I use it on the dbo.syscomments table with only
REPAIR_REBUILD mode ?
DBCC CHECKTABLE
( 'table_name' | 'view_name'
[ , NOINDEX
| index_id
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD }
]
) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
What error are you trying to repair?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"MK" <anonymous@.discussions.microsoft.com> wrote in message
news:000001c4ac8f$6818c0d0$a501280a@.phx.gbl...
> How can I use it on the dbo.syscomments table with only
> REPAIR_REBUILD mode ?
>
> DBCC CHECKTABLE
> ( 'table_name' | 'view_name'
> [ , NOINDEX
> | index_id
> | { REPAIR_ALLOW_DATA_LOSS
> | REPAIR_FAST
> | REPAIR_REBUILD }
> ]
> ) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
> [ , [ TABLOCK ] ]
> [ , [ ESTIMATEONLY ] ]
> [ , [ PHYSICAL_ONLY ] ]
> }
> ]
>
>
|||Yuu should be able to run DBCC CHECKTABLE ('syscomments', REPAIR_REBUILD) without a problem. This will only allow REPAIR to repair errors that can be fixed by a rebuild the clustered index on syscomments. Have you found corruption in this table? I'm curious as to why you're looking for a way to do this.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
"MK" <anonymous@.discussions.microsoft.com> wrote in message news:000001c4ac8f$6818c0d0$a501280a@.phx.gbl...
How can I use it on the dbo.syscomments table with only
REPAIR_REBUILD mode ?
DBCC CHECKTABLE
( 'table_name' | 'view_name'
[ , NOINDEX
| index_id
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD }
]
) [ WITH { [ ALL_ERRORMSGS | NO_INFOMSGS ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
}
]
|||DBCC CHECKTABLE ('syscomments')
GO
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 6, index ID 1. Page (1:94571) is
missing a reference from previous page (1:1031985).
Possible chain linkage problem.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 6, index ID 1. Page (1:1031984) was
not seen in the scan although its parent (1:51) and
previous (4:4594265) refer to it. Check any previous
errors.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 6, index ID 1. Page (1:1031985) was
not seen in the scan although its parent (1:51) and
previous (1:94572) refer to it. Check any previous errors.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 6, index ID 1. Page (4:2785610) is
missing a reference from previous page (1:1031984).
Possible chain linkage problem.
DBCC results for 'syscomments'.
There are 674 rows in 133 pages for object 'syscomments'.
CHECKTABLE found 0 allocation errors and 4 consistency
errors in table 'syscomments' (object ID 6).
repair_rebuild is the minimum repair level for the errors
found by DBCC CHECKTABLE (SAB.dbo.syscomments ).

>--Original Message--
>What error are you trying to repair?
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.