DBCC ShowContig With TableResults

I've looked through BOL, and tried all the samples I could find, but I can't seem to get it to work. I need either the results from ShowContig or more specifically the Scan Density number. I need this in a table or a recordset so I can get to it from an .exe. So far I haven't been able to get this to work.

This needs to work with both SQL 7.0 & 2000. Some of the options aren't avaible under 7.0, and the parameters differ slightly.

My code runs it, but doesn't put the results into the temp table.

Does anyone know the way to simply get the Scan Density value without using ShowContig?

Here's the code I've got so far:
-- SQL
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)

INSERT INTO #fraglist
(ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag)

EXEC ('DBCC SHOWCONTIG (270624007, 2)
WITH TABLERESULTS, NO_INFOMSGS')
Select * from #fraglist
Drop table #fraglist

-- Output
DBCC SHOWCONTIG scanning 'tbInventory' table...
Table: 'tbInventory' (270624007); index ID: 2, database ID: 5
LEAF level scan performed.
- Pages Scanned........................: 65
- Extents Scanned.......................: 9
- Extent Switches.......................: 8
- Avg. Pages per Extent..................: 7.2
- Scan Density [Best Count:Actual Count]......: 100.00% [9:9]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 11.11%
- Avg. Bytes Free per Page................: 112.8
- Avg. Page Density (full)................: 98.61%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(0 row(s) affected)

ObjectName ObjectId IndexName IndexId Lvl CountPages CountRows MinRecSize MaxRecSize AvgRecSize ForRecCount Extents ExtentSwitches AvgFreeBytes AvgPageDensity ScanDensity BestCount ActualCount LogicalFrag ExtentFrag
---------------------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----- ---- ----- ------- ---- ---- ------- -------

(0 row(s) affected)

Thanks!Hi

I ran the script that you posted on SQL Server 2000, however I changed the DBCC command to :

EXEC ('DBCC SHOWCONTIG (''authors'') WITH TABLERESULTS, NO_INFOMSGS')

Since I don't have your table, I used authors in Pubs. This worked fine:

ObjectName ObjectId IndexName
----- ---- ------
authors 1977058079 UPKCL_auidind

I didn't include the whole output, it just wraps around.

For SQL Server 7.0 you'll need to do some coding:

set nocount on

create table #Density(line varchar(80))
create table #contig (tb varchar(40), value float, description varchar(30))

declare @.cmd varchar(250)
declare @.tb sysname
declare @.id int

declare tb_cur cursor
for
select table_name,
object_id(table_name)
from Information_Schema.tables
where table_type = 'base table'

open tb_cur

fetch next from tb_cur into @.tb, @.id

while @.@.fetch_status = 0 begin
select @.cmd = 'use ' + db_name()
+ ' insert #Density EXECUTE master..xp_cmdshell ''isql -E -Snike -dSDMT -Q"dbcc showcontig (' + convert(varchar(100),@.id) + ')"'''

exec (@.cmd)

insert #contig
SELECT @.tb,
CONVERT(FLOAT,REPLACE(SUBSTRING(line, PATINDEX('%: %', line) + 2, 6),'%','')),
CONVERT(VARCHAR(30),
CASE
WHEN line LIKE '%Pages Scanned%' THEN 'Pages Scanned'
WHEN line LIKE '%Extents Scanned%' THEN 'Extents Scanned'
WHEN line LIKE '%Extent Switches%' THEN 'Extent Switches'
WHEN line LIKE '%Avg. Pages per %' THEN 'Avg. Pages per Extent'
WHEN line LIKE '%Scan Density%Best%' THEN 'Scan Density'
WHEN line LIKE '%Logical Scan%' THEN 'Logical Scan Fragmentation'
WHEN line LIKE '%Extent Scan%' THEN 'Extent Scan Fragmentation'
WHEN line LIKE '%Avg. Bytes Free%' THEN 'Avg. Bytes Free per Page'
WHEN line LIKE '%Avg. Page Density%' THEN 'Avg. Page Density (full)'
ELSE Line
END)
FROM #Density
WHERE line LIKE '%- %'

truncate table #Density
fetch next from tb_cur into @.tb, @.id
end
close tb_cur
deallocate tb_cur

select *
from #contig

drop table #Density
drop table #contig
go

It's not the fastest code but it works.|||I've been working with your code (thank you!) and also doing some other testing. This is interesting...

-- SQL2000
EXEC ('DBCC SHOWCONTIG (''authors'') WITH TABLERESULTS, NO_INFOMSGS')

Produces 1 row of data, looks like you did a select top 1 * from a table.

-- SQL7
EXEC ('DBCC SHOWCONTIG (117575457) WITH TABLERESULTS, NO_INFOMSGS')

or
dbcc showcontig (117575457, 1) with TableResults

Produces:
DBCC SHOWCONTIG scanning 'authors' table...
Table: 'authors' (117575457); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned........................: 1
- Extents Scanned.......................: 1
- Extent Switches.......................: 0
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 100.00% [1:1]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 6008.0
- Avg. Page Density (full)................: 25.77%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

It doesn't look like SQL7 is honoring the TableResults parm.

Unfortunately the example you provided (which does work) runs extremely slow on our server. Not sure exactly why, none of it looks that complicated. I'm guessing it's because we are shelling out to isql??|||Is there any way to use OpenRowSet with this DBCC Command?

DBCC INDEXDEFRAG causing blocking

I am using DBCC INDEXDEFRAG to defragment indexes. According to BOL
DBCC INDEXDEFRAG "does not hold locks long term and thus will not
block running queries or updates." But several SPIDs are blocked by
the INDEXDEFRAG SPID. The blocked SPIDs have a waitresource of TAB:
5:436352769 [] (436352769 is the table whose index I am defragmenting)
and waittype of LCK_M_IS. These SPIDs are being blocked for a long
time (30+ minutes).
Any help would be appreciated.
This is on SQL Server 2000, sp4
Thanks!Known 'feature', with hotfix available.
http://support.microsoft.com/kb/907250
--
TheSQLGuru
President
Indicium Resources, Inc.
<sfglossolalia@.gmail.com> wrote in message
news:1179179563.268238.322690@.u30g2000hsc.googlegroups.com...
>I am using DBCC INDEXDEFRAG to defragment indexes. According to BOL
> DBCC INDEXDEFRAG "does not hold locks long term and thus will not
> block running queries or updates." But several SPIDs are blocked by
> the INDEXDEFRAG SPID. The blocked SPIDs have a waitresource of TAB:
> 5:436352769 [] (436352769 is the table whose index I am defragmenting)
> and waittype of LCK_M_IS. These SPIDs are being blocked for a long
> time (30+ minutes).
> Any help would be appreciated.
> This is on SQL Server 2000, sp4
> Thanks!
>

DBCC INDEXDEFRAG , DBREINDEX

I modified the DBCC INDEXDEFRAG script from SQL Server
BOL. I added the DBCC DBREINDEX to the script. I used in
the script listed below if the logical fragmentation is
greater than 75 then used DBCC DBREINDEX, else then
between 5 and 75 then use DBCC INDEXDEFRAG.
The script
INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
eliminates all cluster indexes.
Should this statement be eliminate from my script?
I would like to re-index or defragment all indexes in my
database without using the SQL Server maintenance
Optimization job.
Thanks,
Don
SET NOCOUNT ON
DECLARE @.tablename VARCHAR (128)
DECLARE @.execstr VARCHAR (255)
DECLARE @.objectid INT
DECLARE @.indexid INT
DECLARE @.frag DECIMAL
DECLARE @.IndexName VARCHAR (255)
DECLARE @.maxfrag DECIMAL
DECLARE @.CmdType VARCHAR (255)
-- Decide on the maximum fragmentation to allow
SELECT @.maxfrag = 5.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) NULL,
ObjectId INT NULL,
IndexName CHAR (255) NULL,
IndexId INT NULL,
Lvl INT NULL,
CountPages INT NULL,
CountRows INT NULL,
MinRecSize INT NULL,
MaxRecSize INT NULL,
AvgRecSize INT NULL,
ForRecCount INT NULL,
Extents INT NULL,
ExtentSwitches INT NULL,
AvgFreeBytes INT NULL,
AvgPageDensity INT NULL,
ScanDensity DECIMAL NULL,
BestCount INT NULL,
ActualCount INT NULL,
LogicalFrag DECIMAL NULL,
ExtentFrag DECIMAL NULL)
-- 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 (''' + @.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 #FragList. ObjectName
, #FragList. ObjectId
, #FragList. IndexId
, #FragList. LogicalFrag
, #FragList. IndexName
FROM #fraglist
WHERE LogicalFrag >= @._iMinDefrag
AND INDEXPROPERTY (ObjectId,
IndexName, 'IndexDepth') > 0
ORDER BY LogicalFrag desc
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Executing Check on (0, ' + RTRIM(@.tablename)
+ ', ' + RTRIM(@.IndexName) + ') - fragmentation
currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
if @.frag > 75
select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM
(@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
else if @.frag between @.maxfrag AND 75
SELECT @.Cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM
(@.objectid) + ', ' + RTRIM(@.indexid) + ')'
EXEC (@.CmdType)
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag,
@.IndexName
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GODon,
My reading of INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0 is that
it tells how many levels there are in the index tree (levels above the
leaves). That should not _specifically_ target clustered indexes.
In any case, your use of this property depends on what you are wanting to
do. (I wouldn't use it, personally.)
Russell Fields
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1b6eb01c44fbf$b00964d0$a001280a@.phx.gbl...
> I modified the DBCC INDEXDEFRAG script from SQL Server
> BOL. I added the DBCC DBREINDEX to the script. I used in
> the script listed below if the logical fragmentation is
> greater than 75 then used DBCC DBREINDEX, else then
> between 5 and 75 then use DBCC INDEXDEFRAG.
>
> The script
> INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
> eliminates all cluster indexes.
> Should this statement be eliminate from my script?
> I would like to re-index or defragment all indexes in my
> database without using the SQL Server maintenance
> Optimization job.
> Thanks,
> Don
>
> SET NOCOUNT ON
> DECLARE @.tablename VARCHAR (128)
> DECLARE @.execstr VARCHAR (255)
> DECLARE @.objectid INT
> DECLARE @.indexid INT
> DECLARE @.frag DECIMAL
> DECLARE @.IndexName VARCHAR (255)
> DECLARE @.maxfrag DECIMAL
> DECLARE @.CmdType VARCHAR (255)
> -- Decide on the maximum fragmentation to allow
> SELECT @.maxfrag = 5.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) NULL,
> ObjectId INT NULL,
> IndexName CHAR (255) NULL,
> IndexId INT NULL,
> Lvl INT NULL,
> CountPages INT NULL,
> CountRows INT NULL,
> MinRecSize INT NULL,
> MaxRecSize INT NULL,
> AvgRecSize INT NULL,
> ForRecCount INT NULL,
> Extents INT NULL,
> ExtentSwitches INT NULL,
> AvgFreeBytes INT NULL,
> AvgPageDensity INT NULL,
> ScanDensity DECIMAL NULL,
> BestCount INT NULL,
> ActualCount INT NULL,
> LogicalFrag DECIMAL NULL,
> ExtentFrag DECIMAL NULL)
> -- 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 (''' + @.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 #FragList. ObjectName
> , #FragList. ObjectId
> , #FragList. IndexId
> , #FragList. LogicalFrag
> , #FragList. IndexName
> FROM #fraglist
> WHERE LogicalFrag >= @._iMinDefrag
> AND INDEXPROPERTY (ObjectId,
> IndexName, 'IndexDepth') > 0
> ORDER BY LogicalFrag desc
> -- Open the cursor
> OPEN indexes
> -- loop through the indexes
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Executing Check on (0, ' + RTRIM(@.tablename)
> + ', ' + RTRIM(@.IndexName) + ') - fragmentation
> currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
> if @.frag > 75
> select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM
> (@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
> else if @.frag between @.maxfrag AND 75
> SELECT @.Cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM
> (@.objectid) + ', ' + RTRIM(@.indexid) + ')'
>
> EXEC (@.CmdType)
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag,
> @.IndexName
> END
> -- Close and deallocate the cursor
> CLOSE indexes
> DEALLOCATE indexes
> -- Delete the temporary table
> DROP TABLE #fraglist
> GO
>|||The INDEXPROPERTY clause I included in the BOL example removes all heaps
from consideration. All indexes (clustered or non-clustered) have depth > 0.
You have to keep that clause in there or you may get errors.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:e$$F$T#TEHA.3872@.TK2MSFTNGP10.phx.gbl...
> Don,
> My reading of INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0 is
that
> it tells how many levels there are in the index tree (levels above the
> leaves). That should not _specifically_ target clustered indexes.
> In any case, your use of this property depends on what you are wanting to
> do. (I wouldn't use it, personally.)
> Russell Fields
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:1b6eb01c44fbf$b00964d0$a001280a@.phx.gbl...
> >
> > I modified the DBCC INDEXDEFRAG script from SQL Server
> > BOL. I added the DBCC DBREINDEX to the script. I used in
> > the script listed below if the logical fragmentation is
> > greater than 75 then used DBCC DBREINDEX, else then
> > between 5 and 75 then use DBCC INDEXDEFRAG.
> >
> >
> > The script
> > INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
> > eliminates all cluster indexes.
> >
> > Should this statement be eliminate from my script?
> >
> > I would like to re-index or defragment all indexes in my
> > database without using the SQL Server maintenance
> > Optimization job.
> >
> > Thanks,
> >
> > Don
> >
> >
> >
> > SET NOCOUNT ON
> > DECLARE @.tablename VARCHAR (128)
> > DECLARE @.execstr VARCHAR (255)
> > DECLARE @.objectid INT
> > DECLARE @.indexid INT
> > DECLARE @.frag DECIMAL
> > DECLARE @.IndexName VARCHAR (255)
> > DECLARE @.maxfrag DECIMAL
> > DECLARE @.CmdType VARCHAR (255)
> >
> > -- Decide on the maximum fragmentation to allow
> > SELECT @.maxfrag = 5.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) NULL,
> > ObjectId INT NULL,
> > IndexName CHAR (255) NULL,
> > IndexId INT NULL,
> > Lvl INT NULL,
> > CountPages INT NULL,
> > CountRows INT NULL,
> > MinRecSize INT NULL,
> > MaxRecSize INT NULL,
> > AvgRecSize INT NULL,
> > ForRecCount INT NULL,
> > Extents INT NULL,
> > ExtentSwitches INT NULL,
> > AvgFreeBytes INT NULL,
> > AvgPageDensity INT NULL,
> > ScanDensity DECIMAL NULL,
> > BestCount INT NULL,
> > ActualCount INT NULL,
> > LogicalFrag DECIMAL NULL,
> > ExtentFrag DECIMAL NULL)
> >
> > -- 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 (''' + @.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 #FragList. ObjectName
> > , #FragList. ObjectId
> > , #FragList. IndexId
> > , #FragList. LogicalFrag
> > , #FragList. IndexName
> > FROM #fraglist
> > WHERE LogicalFrag >= @._iMinDefrag
> > AND INDEXPROPERTY (ObjectId,
> > IndexName, 'IndexDepth') > 0
> > ORDER BY LogicalFrag desc
> >
> > -- Open the cursor
> > OPEN indexes
> >
> > -- loop through the indexes
> > FETCH NEXT
> > FROM indexes
> > INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
> >
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > PRINT 'Executing Check on (0, ' + RTRIM(@.tablename)
> > + ', ' + RTRIM(@.IndexName) + ') - fragmentation
> > currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
> > if @.frag > 75
> > select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM
> > (@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
> > else if @.frag between @.maxfrag AND 75
> > SELECT @.Cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM
> > (@.objectid) + ', ' + RTRIM(@.indexid) + ')'
> >
> >
> > EXEC (@.CmdType)
> >
> > FETCH NEXT
> > FROM indexes
> > INTO @.tablename, @.objectid, @.indexid, @.frag,
> > @.IndexName
> > END
> >
> > -- Close and deallocate the cursor
> > CLOSE indexes
> > DEALLOCATE indexes
> >
> > -- Delete the temporary table
> > DROP TABLE #fraglist
> > GO
> >
> >
>

dbcc dbrepair (drop database)

I need to drop a damaged database and BOL no longer provides the syntax for
dbcc dbrepair. Drop database doesn't work because it's being replicated.
The database is in a 'loading' state, but is not actually being restored.
When I issue drop database command it tells me it can't because it's being
replicated. This is true but I can't get rid of the publications because
it's in the 'loading' state.
Anyone know the syntax for dbcc dbrepair or how I can drop this database?
A database in loading status can't be a publisher. If it's a subscriber, you
should be able to drop it.
Any reason why it's in loading?
"Colleen" <Colleen@.discussions.microsoft.com> wrote in message
news:90A47C0C-F0B0-41E5-B58A-668A785B90FC@.microsoft.com...
> I need to drop a damaged database and BOL no longer provides the syntax
for
> dbcc dbrepair. Drop database doesn't work because it's being replicated.
> The database is in a 'loading' state, but is not actually being restored.
> When I issue drop database command it tells me it can't because it's being
> replicated. This is true but I can't get rid of the publications because
> it's in the 'loading' state.
> Anyone know the syntax for dbcc dbrepair or how I can drop this database?
>

dbcc dbrepair (drop database)

I need to drop a damaged database and BOL no longer provides the syntax for
dbcc dbrepair. Drop database doesn't work because it's being replicated.
The database is in a 'loading' state, but is not actually being restored.
When I issue drop database command it tells me it can't because it's being
replicated. This is true but I can't get rid of the publications because
it's in the 'loading' state.
Anyone know the syntax for dbcc dbrepair or how I can drop this database?A database in loading status can't be a publisher. If it's a subscriber, you
should be able to drop it.
Any reason why it's in loading?
"Colleen" <Colleen@.discussions.microsoft.com> wrote in message
news:90A47C0C-F0B0-41E5-B58A-668A785B90FC@.microsoft.com...
> I need to drop a damaged database and BOL no longer provides the syntax
for
> dbcc dbrepair. Drop database doesn't work because it's being replicated.
> The database is in a 'loading' state, but is not actually being restored.
> When I issue drop database command it tells me it can't because it's being
> replicated. This is true but I can't get rid of the publications because
> it's in the 'loading' state.
> Anyone know the syntax for dbcc dbrepair or how I can drop this database?
>

DBCC DBREINDEX\INDEXDEFRAG

I modified the DBCC INDEXDEFRAG script from BOL with the
DBCC DBREINDEX. If the LogicalFragmentation is greater
than 75 then DBCC REINDEX the index. If the
LogicalFragmentation is between 5 and 75 then used the
DBCC INDEXDEFRAG.
I applied the script listed below to a database that has
1500 indexes that is heavy fragmented. The script on
found approximately 75 indexes to reindex or defrag.
Please help me with the missing indexes that need to be
reindexed or defragmented.
Thanks,
Dan
DECLARE @.tablename VARCHAR (128)
DECLARE @.execstr VARCHAR (255)
DECLARE @.objectid INT
DECLARE @.indexid INT
DECLARE @.frag DECIMAL
DECLARE @.IndexName VARCHAR (255)
DECLARE @.maxfrag DECIMAL
DECLARE @.CmdType VARCHAR (255)
-- Decide on the maximum fragmentation to allow
SELECT @.maxfrag = 5
-- 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) NULL,
ObjectId INT NULL,
IndexName CHAR (255) NULL,
IndexId INT NULL,
Lvl INT NULL,
CountPages INT NULL,
CountRows INT NULL,
MinRecSize INT NULL,
MaxRecSize INT NULL,
AvgRecSize INT NULL,
ForRecCount INT NULL,
Extents INT NULL,
ExtentSwitches INT NULL,
AvgFreeBytes INT NULL,
AvgPageDensity INT NULL,
ScanDensity DECIMAL NULL,
BestCount INT NULL,
ActualCount INT NULL,
LogicalFrag DECIMAL NULL,
ExtentFrag DECIMAL NULL)
-- 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 (''' + @.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 #fraglist. ObjectName
, #fraglist. ObjectId
, #fraglist. IndexId
, #fraglist. LogicalFrag
, #fraglist. IndexName
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId,
IndexName, 'IndexDepth') > 0
ORDER BY LogicalFrag desc, ObjectName asc
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Executing Check on (0, ' + RTRIM(@.tablename)
+ ', ' + RTRIM(@.IndexName) + ') - fragmentation
currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
if @.frag > 75
select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM
(@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
else
if @.frag between 5 AND 75
SELECT @.cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM
(@.objectid) + ', ' + RTRIM(@.indexid) + ')'
EXEC (@.execstr)
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag,
@.IndexName
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GO
Dan,
You were executing the wrong variable (@.execstr), a variable you never assigned a value to. You should execute
the @.CmdType variable instead. You can use below code, but you need to comment out my print and remove the
comment for your EXEC:
DECLARE @.tablename VARCHAR (128)
DECLARE @.execstr VARCHAR (255)
DECLARE @.objectid INT
DECLARE @.indexid INT
DECLARE @.frag DECIMAL
DECLARE @.IndexName VARCHAR (255)
DECLARE @.maxfrag DECIMAL
DECLARE @.CmdType VARCHAR (255)
-- Decide on the maximum fragmentation to allow
SELECT @.maxfrag = 5
-- 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) NULL,
ObjectId INT NULL,
IndexName CHAR (255) NULL,
IndexId INT NULL,
Lvl INT NULL,
CountPages INT NULL,
CountRows INT NULL,
MinRecSize INT NULL,
MaxRecSize INT NULL,
AvgRecSize INT NULL,
ForRecCount INT NULL,
Extents INT NULL,
ExtentSwitches INT NULL,
AvgFreeBytes INT NULL,
AvgPageDensity INT NULL,
ScanDensity DECIMAL NULL,
BestCount INT NULL,
ActualCount INT NULL,
LogicalFrag DECIMAL NULL,
ExtentFrag DECIMAL NULL)
-- 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 (''' + @.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 #fraglist. ObjectName
, #fraglist. ObjectId
, #fraglist. IndexId
, #fraglist. LogicalFrag
, #fraglist. IndexName
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
ORDER BY LogicalFrag desc, ObjectName asc
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Executing Check on (0, ' + RTRIM(@.tablename) + ', ' + RTRIM(@.IndexName) + ') - fragmentation
currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
if @.frag > 75
select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM(@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
else
if @.frag between 5 AND 75
SELECT @.cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ', ' + RTRIM(@.indexid) + ')'
-- EXEC (@.cmdtype)
print @.cmdtype
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <anonymous@.discussions.microsoft.com> wrote in message news:1927901c44d03$1b2cad90$a601280a@.phx.gbl...
> I modified the DBCC INDEXDEFRAG script from BOL with the
> DBCC DBREINDEX. If the LogicalFragmentation is greater
> than 75 then DBCC REINDEX the index. If the
> LogicalFragmentation is between 5 and 75 then used the
> DBCC INDEXDEFRAG.
> I applied the script listed below to a database that has
> 1500 indexes that is heavy fragmented. The script on
> found approximately 75 indexes to reindex or defrag.
> Please help me with the missing indexes that need to be
> reindexed or defragmented.
> Thanks,
> Dan
> DECLARE @.tablename VARCHAR (128)
> DECLARE @.execstr VARCHAR (255)
> DECLARE @.objectid INT
> DECLARE @.indexid INT
> DECLARE @.frag DECIMAL
> DECLARE @.IndexName VARCHAR (255)
> DECLARE @.maxfrag DECIMAL
> DECLARE @.CmdType VARCHAR (255)
> -- Decide on the maximum fragmentation to allow
> SELECT @.maxfrag = 5
> -- 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) NULL,
> ObjectId INT NULL,
> IndexName CHAR (255) NULL,
> IndexId INT NULL,
> Lvl INT NULL,
> CountPages INT NULL,
> CountRows INT NULL,
> MinRecSize INT NULL,
> MaxRecSize INT NULL,
> AvgRecSize INT NULL,
> ForRecCount INT NULL,
> Extents INT NULL,
> ExtentSwitches INT NULL,
> AvgFreeBytes INT NULL,
> AvgPageDensity INT NULL,
> ScanDensity DECIMAL NULL,
> BestCount INT NULL,
> ActualCount INT NULL,
> LogicalFrag DECIMAL NULL,
> ExtentFrag DECIMAL NULL)
> -- 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 (''' + @.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 #fraglist. ObjectName
> , #fraglist. ObjectId
> , #fraglist. IndexId
> , #fraglist. LogicalFrag
> , #fraglist. IndexName
> FROM #fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId,
> IndexName, 'IndexDepth') > 0
> ORDER BY LogicalFrag desc, ObjectName asc
> -- Open the cursor
> OPEN indexes
> -- loop through the indexes
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Executing Check on (0, ' + RTRIM(@.tablename)
> + ', ' + RTRIM(@.IndexName) + ') - fragmentation
> currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
> if @.frag > 75
> select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM
> (@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
> else
> if @.frag between 5 AND 75
> SELECT @.cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM
> (@.objectid) + ', ' + RTRIM(@.indexid) + ')'
> EXEC (@.execstr)
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag,
> @.IndexName
> END
> -- Close and deallocate the cursor
> CLOSE indexes
> DEALLOCATE indexes
> -- Delete the temporary table
> DROP TABLE #fraglist
> GO
>
|||Dan
Run DBCC SHOWCONTIG to identify fragmentation.
For more details please refer to the BOL.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:1927901c44d03$1b2cad90$a601280a@.phx.gbl...
> I modified the DBCC INDEXDEFRAG script from BOL with the
> DBCC DBREINDEX. If the LogicalFragmentation is greater
> than 75 then DBCC REINDEX the index. If the
> LogicalFragmentation is between 5 and 75 then used the
> DBCC INDEXDEFRAG.
> I applied the script listed below to a database that has
> 1500 indexes that is heavy fragmented. The script on
> found approximately 75 indexes to reindex or defrag.
> Please help me with the missing indexes that need to be
> reindexed or defragmented.
> Thanks,
> Dan
> DECLARE @.tablename VARCHAR (128)
> DECLARE @.execstr VARCHAR (255)
> DECLARE @.objectid INT
> DECLARE @.indexid INT
> DECLARE @.frag DECIMAL
> DECLARE @.IndexName VARCHAR (255)
> DECLARE @.maxfrag DECIMAL
> DECLARE @.CmdType VARCHAR (255)
> -- Decide on the maximum fragmentation to allow
> SELECT @.maxfrag = 5
> -- 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) NULL,
> ObjectId INT NULL,
> IndexName CHAR (255) NULL,
> IndexId INT NULL,
> Lvl INT NULL,
> CountPages INT NULL,
> CountRows INT NULL,
> MinRecSize INT NULL,
> MaxRecSize INT NULL,
> AvgRecSize INT NULL,
> ForRecCount INT NULL,
> Extents INT NULL,
> ExtentSwitches INT NULL,
> AvgFreeBytes INT NULL,
> AvgPageDensity INT NULL,
> ScanDensity DECIMAL NULL,
> BestCount INT NULL,
> ActualCount INT NULL,
> LogicalFrag DECIMAL NULL,
> ExtentFrag DECIMAL NULL)
> -- 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 (''' + @.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 #fraglist. ObjectName
> , #fraglist. ObjectId
> , #fraglist. IndexId
> , #fraglist. LogicalFrag
> , #fraglist. IndexName
> FROM #fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId,
> IndexName, 'IndexDepth') > 0
> ORDER BY LogicalFrag desc, ObjectName asc
> -- Open the cursor
> OPEN indexes
> -- loop through the indexes
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Executing Check on (0, ' + RTRIM(@.tablename)
> + ', ' + RTRIM(@.IndexName) + ') - fragmentation
> currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
> if @.frag > 75
> select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM
> (@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
> else
> if @.frag between 5 AND 75
> SELECT @.cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM
> (@.objectid) + ', ' + RTRIM(@.indexid) + ')'
> EXEC (@.execstr)
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag,
> @.IndexName
> END
> -- Close and deallocate the cursor
> CLOSE indexes
> DEALLOCATE indexes
> -- Delete the temporary table
> DROP TABLE #fraglist
> GO
>

DBCC DBREINDEX\INDEXDEFRAG

I modified the DBCC INDEXDEFRAG script from BOL with the
DBCC DBREINDEX. If the LogicalFragmentation is greater
than 75 then DBCC REINDEX the index. If the
LogicalFragmentation is between 5 and 75 then used the
DBCC INDEXDEFRAG.
I applied the script listed below to a database that has
1500 indexes that is heavy fragmented. The script on
found approximately 75 indexes to reindex or defrag.
Please help me with the missing indexes that need to be
reindexed or defragmented.
Thanks,
Dan
DECLARE @.tablename VARCHAR (128)
DECLARE @.execstr VARCHAR (255)
DECLARE @.objectid INT
DECLARE @.indexid INT
DECLARE @.frag DECIMAL
DECLARE @.IndexName VARCHAR (255)
DECLARE @.maxfrag DECIMAL
DECLARE @.CmdType VARCHAR (255)
-- Decide on the maximum fragmentation to allow
SELECT @.maxfrag = 5
-- 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) NULL,
ObjectId INT NULL,
IndexName CHAR (255) NULL,
IndexId INT NULL,
Lvl INT NULL,
CountPages INT NULL,
CountRows INT NULL,
MinRecSize INT NULL,
MaxRecSize INT NULL,
AvgRecSize INT NULL,
ForRecCount INT NULL,
Extents INT NULL,
ExtentSwitches INT NULL,
AvgFreeBytes INT NULL,
AvgPageDensity INT NULL,
ScanDensity DECIMAL NULL,
BestCount INT NULL,
ActualCount INT NULL,
LogicalFrag DECIMAL NULL,
ExtentFrag DECIMAL NULL)
-- 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 (''' + @.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 #fraglist. ObjectName
, #fraglist. ObjectId
, #fraglist. IndexId
, #fraglist. LogicalFrag
, #fraglist. IndexName
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId,
IndexName, 'IndexDepth') > 0
ORDER BY LogicalFrag desc, ObjectName asc
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Executing Check on (0, ' + RTRIM(@.tablename)
+ ', ' + RTRIM(@.IndexName) + ') - fragmentation
currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
if @.frag > 75
select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM
(@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
else
if @.frag between 5 AND 75
SELECT @.cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM
(@.objectid) + ', ' + RTRIM(@.indexid) + ')'
EXEC (@.execstr)
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag,
@.IndexName
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GODan,
You were executing the wrong variable (@.execstr), a variable you never assigned a value to. You should execute
the @.CmdType variable instead. You can use below code, but you need to comment out my print and remove the
comment for your EXEC:
DECLARE @.tablename VARCHAR (128)
DECLARE @.execstr VARCHAR (255)
DECLARE @.objectid INT
DECLARE @.indexid INT
DECLARE @.frag DECIMAL
DECLARE @.IndexName VARCHAR (255)
DECLARE @.maxfrag DECIMAL
DECLARE @.CmdType VARCHAR (255)
-- Decide on the maximum fragmentation to allow
SELECT @.maxfrag = 5
-- 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) NULL,
ObjectId INT NULL,
IndexName CHAR (255) NULL,
IndexId INT NULL,
Lvl INT NULL,
CountPages INT NULL,
CountRows INT NULL,
MinRecSize INT NULL,
MaxRecSize INT NULL,
AvgRecSize INT NULL,
ForRecCount INT NULL,
Extents INT NULL,
ExtentSwitches INT NULL,
AvgFreeBytes INT NULL,
AvgPageDensity INT NULL,
ScanDensity DECIMAL NULL,
BestCount INT NULL,
ActualCount INT NULL,
LogicalFrag DECIMAL NULL,
ExtentFrag DECIMAL NULL)
-- 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 (''' + @.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 #fraglist. ObjectName
, #fraglist. ObjectId
, #fraglist. IndexId
, #fraglist. LogicalFrag
, #fraglist. IndexName
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
ORDER BY LogicalFrag desc, ObjectName asc
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Executing Check on (0, ' + RTRIM(@.tablename) + ', ' + RTRIM(@.IndexName) + ') - fragmentation
currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
if @.frag > 75
select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM(@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
else
if @.frag between 5 AND 75
SELECT @.cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ', ' + RTRIM(@.indexid) + ')'
-- EXEC (@.cmdtype)
print @.cmdtype
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <anonymous@.discussions.microsoft.com> wrote in message news:1927901c44d03$1b2cad90$a601280a@.phx.gbl...
> I modified the DBCC INDEXDEFRAG script from BOL with the
> DBCC DBREINDEX. If the LogicalFragmentation is greater
> than 75 then DBCC REINDEX the index. If the
> LogicalFragmentation is between 5 and 75 then used the
> DBCC INDEXDEFRAG.
> I applied the script listed below to a database that has
> 1500 indexes that is heavy fragmented. The script on
> found approximately 75 indexes to reindex or defrag.
> Please help me with the missing indexes that need to be
> reindexed or defragmented.
> Thanks,
> Dan
> DECLARE @.tablename VARCHAR (128)
> DECLARE @.execstr VARCHAR (255)
> DECLARE @.objectid INT
> DECLARE @.indexid INT
> DECLARE @.frag DECIMAL
> DECLARE @.IndexName VARCHAR (255)
> DECLARE @.maxfrag DECIMAL
> DECLARE @.CmdType VARCHAR (255)
> -- Decide on the maximum fragmentation to allow
> SELECT @.maxfrag = 5
> -- 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) NULL,
> ObjectId INT NULL,
> IndexName CHAR (255) NULL,
> IndexId INT NULL,
> Lvl INT NULL,
> CountPages INT NULL,
> CountRows INT NULL,
> MinRecSize INT NULL,
> MaxRecSize INT NULL,
> AvgRecSize INT NULL,
> ForRecCount INT NULL,
> Extents INT NULL,
> ExtentSwitches INT NULL,
> AvgFreeBytes INT NULL,
> AvgPageDensity INT NULL,
> ScanDensity DECIMAL NULL,
> BestCount INT NULL,
> ActualCount INT NULL,
> LogicalFrag DECIMAL NULL,
> ExtentFrag DECIMAL NULL)
> -- 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 (''' + @.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 #fraglist. ObjectName
> , #fraglist. ObjectId
> , #fraglist. IndexId
> , #fraglist. LogicalFrag
> , #fraglist. IndexName
> FROM #fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId,
> IndexName, 'IndexDepth') > 0
> ORDER BY LogicalFrag desc, ObjectName asc
> -- Open the cursor
> OPEN indexes
> -- loop through the indexes
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Executing Check on (0, ' + RTRIM(@.tablename)
> + ', ' + RTRIM(@.IndexName) + ') - fragmentation
> currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
> if @.frag > 75
> select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM
> (@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
> else
> if @.frag between 5 AND 75
> SELECT @.cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM
> (@.objectid) + ', ' + RTRIM(@.indexid) + ')'
> EXEC (@.execstr)
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag,
> @.IndexName
> END
> -- Close and deallocate the cursor
> CLOSE indexes
> DEALLOCATE indexes
> -- Delete the temporary table
> DROP TABLE #fraglist
> GO
>|||Dan
Run DBCC SHOWCONTIG to identify fragmentation.
For more details please refer to the BOL.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:1927901c44d03$1b2cad90$a601280a@.phx.gbl...
> I modified the DBCC INDEXDEFRAG script from BOL with the
> DBCC DBREINDEX. If the LogicalFragmentation is greater
> than 75 then DBCC REINDEX the index. If the
> LogicalFragmentation is between 5 and 75 then used the
> DBCC INDEXDEFRAG.
> I applied the script listed below to a database that has
> 1500 indexes that is heavy fragmented. The script on
> found approximately 75 indexes to reindex or defrag.
> Please help me with the missing indexes that need to be
> reindexed or defragmented.
> Thanks,
> Dan
> DECLARE @.tablename VARCHAR (128)
> DECLARE @.execstr VARCHAR (255)
> DECLARE @.objectid INT
> DECLARE @.indexid INT
> DECLARE @.frag DECIMAL
> DECLARE @.IndexName VARCHAR (255)
> DECLARE @.maxfrag DECIMAL
> DECLARE @.CmdType VARCHAR (255)
> -- Decide on the maximum fragmentation to allow
> SELECT @.maxfrag = 5
> -- 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) NULL,
> ObjectId INT NULL,
> IndexName CHAR (255) NULL,
> IndexId INT NULL,
> Lvl INT NULL,
> CountPages INT NULL,
> CountRows INT NULL,
> MinRecSize INT NULL,
> MaxRecSize INT NULL,
> AvgRecSize INT NULL,
> ForRecCount INT NULL,
> Extents INT NULL,
> ExtentSwitches INT NULL,
> AvgFreeBytes INT NULL,
> AvgPageDensity INT NULL,
> ScanDensity DECIMAL NULL,
> BestCount INT NULL,
> ActualCount INT NULL,
> LogicalFrag DECIMAL NULL,
> ExtentFrag DECIMAL NULL)
> -- 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 (''' + @.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 #fraglist. ObjectName
> , #fraglist. ObjectId
> , #fraglist. IndexId
> , #fraglist. LogicalFrag
> , #fraglist. IndexName
> FROM #fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId,
> IndexName, 'IndexDepth') > 0
> ORDER BY LogicalFrag desc, ObjectName asc
> -- Open the cursor
> OPEN indexes
> -- loop through the indexes
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Executing Check on (0, ' + RTRIM(@.tablename)
> + ', ' + RTRIM(@.IndexName) + ') - fragmentation
> currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
> if @.frag > 75
> select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM
> (@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
> else
> if @.frag between 5 AND 75
> SELECT @.cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM
> (@.objectid) + ', ' + RTRIM(@.indexid) + ')'
> EXEC (@.execstr)
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag,
> @.IndexName
> END
> -- Close and deallocate the cursor
> CLOSE indexes
> DEALLOCATE indexes
> -- Delete the temporary table
> DROP TABLE #fraglist
> GO
>

DBCC DBREINDEX\INDEXDEFRAG

I modified the DBCC INDEXDEFRAG script from BOL with the
DBCC DBREINDEX. If the LogicalFragmentation is greater
than 75 then DBCC REINDEX the index. If the
LogicalFragmentation is between 5 and 75 then used the
DBCC INDEXDEFRAG.
I applied the script listed below to a database that has
1500 indexes that is heavy fragmented. The script on
found approximately 75 indexes to reindex or defrag.
Please help me with the missing indexes that need to be
reindexed or defragmented.
Thanks,
Dan
DECLARE @.tablename VARCHAR (128)
DECLARE @.execstr VARCHAR (255)
DECLARE @.objectid INT
DECLARE @.indexid INT
DECLARE @.frag DECIMAL
DECLARE @.IndexName VARCHAR (255)
DECLARE @.maxfrag DECIMAL
DECLARE @.CmdType VARCHAR (255)
-- Decide on the maximum fragmentation to allow
SELECT @.maxfrag = 5
-- 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) NULL,
ObjectId INT NULL,
IndexName CHAR (255) NULL,
IndexId INT NULL,
Lvl INT NULL,
CountPages INT NULL,
CountRows INT NULL,
MinRecSize INT NULL,
MaxRecSize INT NULL,
AvgRecSize INT NULL,
ForRecCount INT NULL,
Extents INT NULL,
ExtentSwitches INT NULL,
AvgFreeBytes INT NULL,
AvgPageDensity INT NULL,
ScanDensity DECIMAL NULL,
BestCount INT NULL,
ActualCount INT NULL,
LogicalFrag DECIMAL NULL,
ExtentFrag DECIMAL NULL)
-- 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 (''' + @.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 #fraglist. ObjectName
, #fraglist. ObjectId
, #fraglist. IndexId
, #fraglist. LogicalFrag
, #fraglist. IndexName
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId,
IndexName, 'IndexDepth') > 0
ORDER BY LogicalFrag desc, ObjectName asc
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Executing Check on (0, ' + RTRIM(@.tablename)
+ ', ' + RTRIM(@.IndexName) + ') - fragmentation
currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
if @.frag > 75
select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM
(@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
else
if @.frag between 5 AND 75
SELECT @.cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM
(@.objectid) + ', ' + RTRIM(@.indexid) + ')'
EXEC (@.execstr)
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag,
@.IndexName
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GODan,
You were executing the wrong variable (@.execstr), a variable you never assig
ned a value to. You should execute
the @.CmdType variable instead. You can use below code, but you need to comme
nt out my print and remove the
comment for your EXEC:
DECLARE @.tablename VARCHAR (128)
DECLARE @.execstr VARCHAR (255)
DECLARE @.objectid INT
DECLARE @.indexid INT
DECLARE @.frag DECIMAL
DECLARE @.IndexName VARCHAR (255)
DECLARE @.maxfrag DECIMAL
DECLARE @.CmdType VARCHAR (255)
-- Decide on the maximum fragmentation to allow
SELECT @.maxfrag = 5
-- 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) NULL,
ObjectId INT NULL,
IndexName CHAR (255) NULL,
IndexId INT NULL,
Lvl INT NULL,
CountPages INT NULL,
CountRows INT NULL,
MinRecSize INT NULL,
MaxRecSize INT NULL,
AvgRecSize INT NULL,
ForRecCount INT NULL,
Extents INT NULL,
ExtentSwitches INT NULL,
AvgFreeBytes INT NULL,
AvgPageDensity INT NULL,
ScanDensity DECIMAL NULL,
BestCount INT NULL,
ActualCount INT NULL,
LogicalFrag DECIMAL NULL,
ExtentFrag DECIMAL NULL)
-- 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 (''' + @.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 #fraglist. ObjectName
, #fraglist. ObjectId
, #fraglist. IndexId
, #fraglist. LogicalFrag
, #fraglist. IndexName
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
ORDER BY LogicalFrag desc, ObjectName asc
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Executing Check on (0, ' + RTRIM(@.tablename) + ', ' + RTRIM(@.IndexNam
e) + ') - fragmentation
currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
if @.frag > 75
select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM(@.tablename) + ',' + rtrim(
@.IndexName) + ', 0)'
else
if @.frag between 5 AND 75
SELECT @.cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ', ' + RTRIM(
@.indexid) + ')'
-- EXEC (@.cmdtype)
print @.cmdtype
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <anonymous@.discussions.microsoft.com> wrote in message news:1927901c44d03$1b2cad90$a60
1280a@.phx.gbl...
> I modified the DBCC INDEXDEFRAG script from BOL with the
> DBCC DBREINDEX. If the LogicalFragmentation is greater
> than 75 then DBCC REINDEX the index. If the
> LogicalFragmentation is between 5 and 75 then used the
> DBCC INDEXDEFRAG.
> I applied the script listed below to a database that has
> 1500 indexes that is heavy fragmented. The script on
> found approximately 75 indexes to reindex or defrag.
> Please help me with the missing indexes that need to be
> reindexed or defragmented.
> Thanks,
> Dan
> DECLARE @.tablename VARCHAR (128)
> DECLARE @.execstr VARCHAR (255)
> DECLARE @.objectid INT
> DECLARE @.indexid INT
> DECLARE @.frag DECIMAL
> DECLARE @.IndexName VARCHAR (255)
> DECLARE @.maxfrag DECIMAL
> DECLARE @.CmdType VARCHAR (255)
> -- Decide on the maximum fragmentation to allow
> SELECT @.maxfrag = 5
> -- 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) NULL,
> ObjectId INT NULL,
> IndexName CHAR (255) NULL,
> IndexId INT NULL,
> Lvl INT NULL,
> CountPages INT NULL,
> CountRows INT NULL,
> MinRecSize INT NULL,
> MaxRecSize INT NULL,
> AvgRecSize INT NULL,
> ForRecCount INT NULL,
> Extents INT NULL,
> ExtentSwitches INT NULL,
> AvgFreeBytes INT NULL,
> AvgPageDensity INT NULL,
> ScanDensity DECIMAL NULL,
> BestCount INT NULL,
> ActualCount INT NULL,
> LogicalFrag DECIMAL NULL,
> ExtentFrag DECIMAL NULL)
> -- 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 (''' + @.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 #fraglist. ObjectName
> , #fraglist. ObjectId
> , #fraglist. IndexId
> , #fraglist. LogicalFrag
> , #fraglist. IndexName
> FROM #fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId,
> IndexName, 'IndexDepth') > 0
> ORDER BY LogicalFrag desc, ObjectName asc
> -- Open the cursor
> OPEN indexes
> -- loop through the indexes
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Executing Check on (0, ' + RTRIM(@.tablename)
> + ', ' + RTRIM(@.IndexName) + ') - fragmentation
> currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
> if @.frag > 75
> select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM
> (@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
> else
> if @.frag between 5 AND 75
> SELECT @.cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM
> (@.objectid) + ', ' + RTRIM(@.indexid) + ')'
> EXEC (@.execstr)
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag,
> @.IndexName
> END
> -- Close and deallocate the cursor
> CLOSE indexes
> DEALLOCATE indexes
> -- Delete the temporary table
> DROP TABLE #fraglist
> GO
>|||Dan
Run DBCC SHOWCONTIG to identify fragmentation.
For more details please refer to the BOL.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:1927901c44d03$1b2cad90$a601280a@.phx
.gbl...
> I modified the DBCC INDEXDEFRAG script from BOL with the
> DBCC DBREINDEX. If the LogicalFragmentation is greater
> than 75 then DBCC REINDEX the index. If the
> LogicalFragmentation is between 5 and 75 then used the
> DBCC INDEXDEFRAG.
> I applied the script listed below to a database that has
> 1500 indexes that is heavy fragmented. The script on
> found approximately 75 indexes to reindex or defrag.
> Please help me with the missing indexes that need to be
> reindexed or defragmented.
> Thanks,
> Dan
> DECLARE @.tablename VARCHAR (128)
> DECLARE @.execstr VARCHAR (255)
> DECLARE @.objectid INT
> DECLARE @.indexid INT
> DECLARE @.frag DECIMAL
> DECLARE @.IndexName VARCHAR (255)
> DECLARE @.maxfrag DECIMAL
> DECLARE @.CmdType VARCHAR (255)
> -- Decide on the maximum fragmentation to allow
> SELECT @.maxfrag = 5
> -- 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) NULL,
> ObjectId INT NULL,
> IndexName CHAR (255) NULL,
> IndexId INT NULL,
> Lvl INT NULL,
> CountPages INT NULL,
> CountRows INT NULL,
> MinRecSize INT NULL,
> MaxRecSize INT NULL,
> AvgRecSize INT NULL,
> ForRecCount INT NULL,
> Extents INT NULL,
> ExtentSwitches INT NULL,
> AvgFreeBytes INT NULL,
> AvgPageDensity INT NULL,
> ScanDensity DECIMAL NULL,
> BestCount INT NULL,
> ActualCount INT NULL,
> LogicalFrag DECIMAL NULL,
> ExtentFrag DECIMAL NULL)
> -- 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 (''' + @.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 #fraglist. ObjectName
> , #fraglist. ObjectId
> , #fraglist. IndexId
> , #fraglist. LogicalFrag
> , #fraglist. IndexName
> FROM #fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId,
> IndexName, 'IndexDepth') > 0
> ORDER BY LogicalFrag desc, ObjectName asc
> -- Open the cursor
> OPEN indexes
> -- loop through the indexes
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag, @.IndexName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Executing Check on (0, ' + RTRIM(@.tablename)
> + ', ' + RTRIM(@.IndexName) + ') - fragmentation
> currently ' + RTRIM(CONVERT(varchar(15),@.frag)) + '%'
> if @.frag > 75
> select @.CmdType = 'DBCC DBREINDEX ' + '(' + RTRIM
> (@.tablename) + ',' + rtrim(@.IndexName) + ', 0)'
> else
> if @.frag between 5 AND 75
> SELECT @.cmdtype = 'DBCC INDEXDEFRAG (0, ' + RTRIM
> (@.objectid) + ', ' + RTRIM(@.indexid) + ')'
> EXEC (@.execstr)
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag,
> @.IndexName
> END
> -- Close and deallocate the cursor
> CLOSE indexes
> DEALLOCATE indexes
> -- Delete the temporary table
> DROP TABLE #fraglist
> GO
>

DBCC CHECKDB

It looks like I have to setup maintenance for my 24x7 SQL Server 2000
manually. Most of the relevant pages in BOL are clear enough, but DBCC
CHECKDB isn't so clear.
I would like to schedule DBCC CHECKDB for nightly execution and (so far) I
haven't seen anything that would alter DDL at night, which is the only
restriction I see. I also have no intent of actually doing a repair
initially.
Is this the right routine? Is this the right usage?
Thanks,
JayHi Jay
"Jay" wrote:
> It looks like I have to setup maintenance for my 24x7 SQL Server 2000
> manually. Most of the relevant pages in BOL are clear enough, but DBCC
> CHECKDB isn't so clear.
> I would like to schedule DBCC CHECKDB for nightly execution and (so far) I
> haven't seen anything that would alter DDL at night, which is the only
> restriction I see. I also have no intent of actually doing a repair
> initially.
> Is this the right routine? Is this the right usage?
> Thanks,
> Jay
>
You may want to check out Paul Randals blog posts on this at
https://blogs.msdn.com/sqlserverstorageengine/archive/tags/DBCC+CHECKDB+Series/default.aspx
How often you run it will depend on many factors, but I don't know of anyone
that does it nightly! If you have scheduled other DBCC commands you may be
running some checks more than once, but you may want to run the these
commands rather than DBCC CHECKDB to reduce the time/workload on your servers.
John|||After digging in BOL, I see that DBCHECK includes CHECKALLOC and CHECKTABLE
(anything else?).
Since the runtime for CHECKDB is under 3 minutes for the primary database on
the dev system, even when expanded out I don't think it's that big a deal to
just use it.
Still, if I can do everything CHECKDB does (maybe even a little more) in an
application that spreads the load out, I should at least consider it.
So, what else is DBCHECK doing?
What else SHOULD it be doing?
Thanks,
Jay
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:74F66FBF-DFBC-456C-940A-9F1808FF0F7B@.microsoft.com...
> Hi Jay
> "Jay" wrote:
>> It looks like I have to setup maintenance for my 24x7 SQL Server 2000
>> manually. Most of the relevant pages in BOL are clear enough, but DBCC
>> CHECKDB isn't so clear.
>> I would like to schedule DBCC CHECKDB for nightly execution and (so far)
>> I
>> haven't seen anything that would alter DDL at night, which is the only
>> restriction I see. I also have no intent of actually doing a repair
>> initially.
>> Is this the right routine? Is this the right usage?
>> Thanks,
>> Jay
> You may want to check out Paul Randals blog posts on this at
> https://blogs.msdn.com/sqlserverstorageengine/archive/tags/DBCC+CHECKDB+Series/default.aspx
> How often you run it will depend on many factors, but I don't know of
> anyone
> that does it nightly! If you have scheduled other DBCC commands you may be
> running some checks more than once, but you may want to run the these
> commands rather than DBCC CHECKDB to reduce the time/workload on your
> servers.
> John|||Include DBCC CHECKCATALOG as well. SQL2005 includes this in CheckDB but 2000
omitted it.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jay" <nospam@.nospam.org> wrote in message
news:uowYuIc5HHA.2380@.TK2MSFTNGP02.phx.gbl...
> After digging in BOL, I see that DBCHECK includes CHECKALLOC and
> CHECKTABLE (anything else?).
> Since the runtime for CHECKDB is under 3 minutes for the primary database
> on the dev system, even when expanded out I don't think it's that big a
> deal to just use it.
> Still, if I can do everything CHECKDB does (maybe even a little more) in
> an application that spreads the load out, I should at least consider it.
> So, what else is DBCHECK doing?
> What else SHOULD it be doing?
> Thanks,
> Jay
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:74F66FBF-DFBC-456C-940A-9F1808FF0F7B@.microsoft.com...
>> Hi Jay
>> "Jay" wrote:
>> It looks like I have to setup maintenance for my 24x7 SQL Server 2000
>> manually. Most of the relevant pages in BOL are clear enough, but DBCC
>> CHECKDB isn't so clear.
>> I would like to schedule DBCC CHECKDB for nightly execution and (so far)
>> I
>> haven't seen anything that would alter DDL at night, which is the only
>> restriction I see. I also have no intent of actually doing a repair
>> initially.
>> Is this the right routine? Is this the right usage?
>> Thanks,
>> Jay
>> You may want to check out Paul Randals blog posts on this at
>> https://blogs.msdn.com/sqlserverstorageengine/archive/tags/DBCC+CHECKDB+Series/default.aspx
>> How often you run it will depend on many factors, but I don't know of
>> anyone
>> that does it nightly! If you have scheduled other DBCC commands you may
>> be
>> running some checks more than once, but you may want to run the these
>> commands rather than DBCC CHECKDB to reduce the time/workload on your
>> servers.
>> John
>|||Hi Jay
DBCC CHECKDB can take a long time, so as you move forward you would have to
keep an eye on the duration and workload it places on the server.
John
"Jay" wrote:
> After digging in BOL, I see that DBCHECK includes CHECKALLOC and CHECKTABLE
> (anything else?).
> Since the runtime for CHECKDB is under 3 minutes for the primary database on
> the dev system, even when expanded out I don't think it's that big a deal to
> just use it.
> Still, if I can do everything CHECKDB does (maybe even a little more) in an
> application that spreads the load out, I should at least consider it.
> So, what else is DBCHECK doing?
> What else SHOULD it be doing?
> Thanks,
> Jay
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:74F66FBF-DFBC-456C-940A-9F1808FF0F7B@.microsoft.com...
> > Hi Jay
> >
> > "Jay" wrote:
> >
> >> It looks like I have to setup maintenance for my 24x7 SQL Server 2000
> >> manually. Most of the relevant pages in BOL are clear enough, but DBCC
> >> CHECKDB isn't so clear.
> >>
> >> I would like to schedule DBCC CHECKDB for nightly execution and (so far)
> >> I
> >> haven't seen anything that would alter DDL at night, which is the only
> >> restriction I see. I also have no intent of actually doing a repair
> >> initially.
> >>
> >> Is this the right routine? Is this the right usage?
> >>
> >> Thanks,
> >> Jay
> >>
> > You may want to check out Paul Randals blog posts on this at
> > https://blogs.msdn.com/sqlserverstorageengine/archive/tags/DBCC+CHECKDB+Series/default.aspx
> >
> > How often you run it will depend on many factors, but I don't know of
> > anyone
> > that does it nightly! If you have scheduled other DBCC commands you may be
> > running some checks more than once, but you may want to run the these
> > commands rather than DBCC CHECKDB to reduce the time/workload on your
> > servers.
> >
> > John
>
>