DBfile usage: script

Showing posts with label script. Show all posts

Thursday, March 22, 2012

DBCC Results into a Table Brings Errors!

If you run the script below, it will cause an error. However, if you
run the select portion after the error, it will have accomplished the
desired result. Unfortunately, the table has to be manually dropped
after the proc is run. Any ideas why'? USing the GO keyword is not
an option as it will blow the rest of the script.
CREATE TABLE #db_file_information(
fileid integer,
theFileGroup integer,
Total_Extents integer,
Used_Extents integer,
db varchar(30),
file_Path_name varchar(300)--,
-- File_Free_space decimal(15,2),
-- Percent_Free decimal(15,2)
)
-- Get the size of the datafiles
insert into #db_file_information exec('DBCC showfilestats')
-- add two columns to the temp table
alter table #db_file_information add dude as
((Total_Extents-Used_Extents)/(Total_extents*1.0))
select * from #db_file_information
drop table #db_file_informationTry,
use northwind
go
CREATE TABLE #db_file_information(
Fileid bigint,
[FileGroup] bigint,
TotalExtents bigint,
UsedExtents bigint,
[Name] sysname,
[FileName] varchar(260)
)
go
-- Get the size of the datafiles
insert into #db_file_information
exec('DBCC showfilestats with NO_INFOMSGS')
-- add two columns to the temp table
alter table #db_file_information
add dude as ((TotalExtents - UsedExtents) / nullif((TotalExtents * 1.0), 0))
go
select * from #db_file_information
go
drop table #db_file_information
go
AMB
"dpaskiet@.comcast.net" wrote:

> If you run the script below, it will cause an error. However, if you
> run the select portion after the error, it will have accomplished the
> desired result. Unfortunately, the table has to be manually dropped
> after the proc is run. Any ideas why'? USing the GO keyword is not
> an option as it will blow the rest of the script.
>
> CREATE TABLE #db_file_information(
> fileid integer,
> theFileGroup integer,
> Total_Extents integer,
> Used_Extents integer,
> db varchar(30),
> file_Path_name varchar(300)--,
> -- File_Free_space decimal(15,2),
> -- Percent_Free decimal(15,2)
> )
> -- Get the size of the datafiles
> insert into #db_file_information exec('DBCC showfilestats')
> -- add two columns to the temp table
> alter table #db_file_information add dude as
> ((Total_Extents-Used_Extents)/(Total_extents*1.0))
>
> select * from #db_file_information
>
> drop table #db_file_information
>

Sunday, March 11, 2012

DBCC INDEXDEFRAG script not working

From the script found here
(http://msdn2.microsoft.com/en-us/library/ms175008.aspx):
USE db
-- Declare variables
SET NOCOUNT ON;
DECLARE @.tablename varchar(128);
DECLARE @.execstr varchar(255);
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.frag decimal;
DECLARE @.maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @.maxfrag = 30.0;
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table.
CREATE TABLE #fraglist (
ObjectName char(255),
ObjectId int,
IndexName char(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @.tablename;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.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 the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
' + RTRIM(@.indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@.frag)) + '%';
SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
' + RTRIM(@.indexid) + ')';
EXEC (@.execstr);
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
END;
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
GO
I get the following errors:
Server: Msg 170, Level 15, State 1, Line 52
Line 52: Incorrect syntax near ';'.
Server: Msg 156, Level 15, State 1, Line 60
Incorrect syntax near the keyword 'END'.
Server: Msg 170, Level 15, State 1, Line 82
Line 82: Incorrect syntax near ';'.
Server: Msg 156, Level 15, State 1, Line 93
Incorrect syntax near the keyword 'END'.
I thought I had completed this successfully before but obviously I hadn't.
Does anyone have any tips?If this is run on sql server 2000, "Begin" cannot be terminated with a
semi-colon.

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
> >
> >
>

Thursday, March 8, 2012

DBCC INDEXDEFRAG

I index my database nightly since I have many changes to
cluster indexes and non-cluster indexes daily. I ran a
script the would tells me every table the number of rows,
index space, data space, and unused space. Next I
applied the DBCC INDEXDEFRAG to every table and each
index which ran for approximately 40 minutes. Then I ran
the script again to tell me every table the number of
rows, index space, data space, and unused space. There
was no change in values.
Please help me with this problem?
DonMy guess is that the script you are mentioning is using the sysindexes
system table. This table is updated asynchronously. Try to update the data
for a specific table using sp_spaceused
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts
_sp_sa-sz_0kro.asp) system sp.
Next, what do you expect to change? N of rows should not change during index
defragmentation! DBCC INDEXDEFRAG is used primarily to defragment logical
fragmentation (logical <> physical order). Sometimes it might compact
indexes as well, if they are physically fragmented; if not, you won't see
any changes in index space
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts
_dbcc_30o9.asp).
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1573e01c446c9$71f94970$a401280a@.phx.gbl...
> I index my database nightly since I have many changes to
> cluster indexes and non-cluster indexes daily. I ran a
> script the would tells me every table the number of rows,
> index space, data space, and unused space. Next I
> applied the DBCC INDEXDEFRAG to every table and each
> index which ran for approximately 40 minutes. Then I ran
> the script again to tell me every table the number of
> rows, index space, data space, and unused space. There
> was no change in values.
> Please help me with this problem?
> Don
>|||In addition to what Dejan said, INDEXDEFRAG does not recompute the
statistics. You might want to consider running sp_updatestats after
INDEXDEFRAG.
Andrew J. Kelly
SQL Server MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1573e01c446c9$71f94970$a401280a@.phx.gbl...
> I index my database nightly since I have many changes to
> cluster indexes and non-cluster indexes daily. I ran a
> script the would tells me every table the number of rows,
> index space, data space, and unused space. Next I
> applied the DBCC INDEXDEFRAG to every table and each
> index which ran for approximately 40 minutes. Then I ran
> the script again to tell me every table the number of
> rows, index space, data space, and unused space. There
> was no change in values.
> Please help me with this problem?
> Don
>

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
>

Saturday, February 25, 2012

DBCC DBREINDEX

Can anyone confirm this for me. If I were to run this script below. Will it
reindex all tables indexes at one time as it goes through the cursor. Or
will it reindex one table indexes at a time as it goes through the cursor.
Basically will it pause each time the DBCC command is executed?
If the first part is true where it executes all at once then what is a good
way to only do one at a time? Can you pause between executions till each
execution is done?
THanks for the help.
DECLARE @.TableName varchar(255)
DECLARE ReindexTableCursor CURSOR FOR
SELECT name FROM sysobjects WHERE xtype = 'U' AND (name = 'Practice' OR name
= 'ID_Master')
ORDER BY name
OPEN ReindexTableCursor
-- Perform the first fetch.
FETCH NEXT FROM ReindexTableCursor INTO @.TableName
-- Check @.@.FETCH_STATUS to see if there are any rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
PRINT 'Reindexing' + ' ' + @.TableName
DBCC DBREINDEX (@.TableName )
FETCH NEXT FROM ReindexTableCursor into @.TableName
END
CLOSE ReindexTableCursor
DEALLOCATE ReindexTableCursor
See the reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Dave Mortenson" <dmortenson@.dentrix.com> wrote in message
news:enpAF$obEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Can anyone confirm this for me. If I were to run this script below. Will
it
> reindex all tables indexes at one time as it goes through the cursor. Or
> will it reindex one table indexes at a time as it goes through the cursor.
> Basically will it pause each time the DBCC command is executed?
> If the first part is true where it executes all at once then what is a
good
> way to only do one at a time? Can you pause between executions till each
> execution is done?
> THanks for the help.
> DECLARE @.TableName varchar(255)
> DECLARE ReindexTableCursor CURSOR FOR
> SELECT name FROM sysobjects WHERE xtype = 'U' AND (name = 'Practice' OR
name
> = 'ID_Master')
> ORDER BY name
> OPEN ReindexTableCursor
> -- Perform the first fetch.
> FETCH NEXT FROM ReindexTableCursor INTO @.TableName
> -- Check @.@.FETCH_STATUS to see if there are any rows to fetch.
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- This is executed as long as the previous fetch succeeds.
> PRINT 'Reindexing' + ' ' + @.TableName
> DBCC DBREINDEX (@.TableName )
> FETCH NEXT FROM ReindexTableCursor into @.TableName
> END
> CLOSE ReindexTableCursor
> DEALLOCATE ReindexTableCursor
>

DBCC DBREINDEX

Can anyone confirm this for me. If I were to run this script below. Will it
reindex all tables indexes at one time as it goes through the cursor. Or
will it reindex one table indexes at a time as it goes through the cursor.
Basically will it pause each time the DBCC command is executed?
If the first part is true where it executes all at once then what is a good
way to only do one at a time? Can you pause between executions till each
execution is done?
THanks for the help.
DECLARE @.TableName varchar(255)
DECLARE ReindexTableCursor CURSOR FOR
SELECT name FROM sysobjects WHERE xtype = 'U' AND (name = 'Practice' OR name
= 'ID_Master')
ORDER BY name
OPEN ReindexTableCursor
-- Perform the first fetch.
FETCH NEXT FROM ReindexTableCursor INTO @.TableName
-- Check @.@.FETCH_STATUS to see if there are any rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
PRINT 'Reindexing' + ' ' + @.TableName
DBCC DBREINDEX (@.TableName )
FETCH NEXT FROM ReindexTableCursor into @.TableName
END
CLOSE ReindexTableCursor
DEALLOCATE ReindexTableCursorSee the reply in the other newsgroup.
--
Andrew J. Kelly SQL MVP
"Dave Mortenson" <dmortenson@.dentrix.com> wrote in message
news:enpAF$obEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Can anyone confirm this for me. If I were to run this script below. Will
it
> reindex all tables indexes at one time as it goes through the cursor. Or
> will it reindex one table indexes at a time as it goes through the cursor.
> Basically will it pause each time the DBCC command is executed?
> If the first part is true where it executes all at once then what is a
good
> way to only do one at a time? Can you pause between executions till each
> execution is done?
> THanks for the help.
> DECLARE @.TableName varchar(255)
> DECLARE ReindexTableCursor CURSOR FOR
> SELECT name FROM sysobjects WHERE xtype = 'U' AND (name = 'Practice' OR
name
> = 'ID_Master')
> ORDER BY name
> OPEN ReindexTableCursor
> -- Perform the first fetch.
> FETCH NEXT FROM ReindexTableCursor INTO @.TableName
> -- Check @.@.FETCH_STATUS to see if there are any rows to fetch.
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- This is executed as long as the previous fetch succeeds.
> PRINT 'Reindexing' + ' ' + @.TableName
> DBCC DBREINDEX (@.TableName )
> FETCH NEXT FROM ReindexTableCursor into @.TableName
> END
> CLOSE ReindexTableCursor
> DEALLOCATE ReindexTableCursor
>

DBCC DBREINDEX

Can anyone confirm this for me. If I were to run this script below. Will it
reindex all tables indexes at one time as it goes through the cursor. Or
will it reindex one table indexes at a time as it goes through the cursor.
Basically will it pause each time the DBCC command is executed?
If the first part is true where it executes all at once then what is a good
way to only do one at a time? Can you pause between executions till each
execution is done?
THanks for the help.
DECLARE @.TableName varchar(255)
DECLARE ReindexTableCursor CURSOR FOR
SELECT name FROM sysobjects WHERE xtype = 'U' AND (name = 'Practice' OR name
= 'ID_Master')
ORDER BY name
OPEN ReindexTableCursor
-- Perform the first fetch.
FETCH NEXT FROM ReindexTableCursor INTO @.TableName
-- Check @.@.FETCH_STATUS to see if there are any rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
PRINT 'Reindexing' + ' ' + @.TableName
DBCC DBREINDEX (@.TableName )
FETCH NEXT FROM ReindexTableCursor into @.TableName
END
CLOSE ReindexTableCursor
DEALLOCATE ReindexTableCursorSee the reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Dave Mortenson" <dmortenson@.dentrix.com> wrote in message
news:enpAF$obEHA.1356@.TK2MSFTNGP09.phx.gbl...
> Can anyone confirm this for me. If I were to run this script below. Will
it
> reindex all tables indexes at one time as it goes through the cursor. Or
> will it reindex one table indexes at a time as it goes through the cursor.
> Basically will it pause each time the DBCC command is executed?
> If the first part is true where it executes all at once then what is a
good
> way to only do one at a time? Can you pause between executions till each
> execution is done?
> THanks for the help.
> DECLARE @.TableName varchar(255)
> DECLARE ReindexTableCursor CURSOR FOR
> SELECT name FROM sysobjects WHERE xtype = 'U' AND (name = 'Practice' OR
name
> = 'ID_Master')
> ORDER BY name
> OPEN ReindexTableCursor
> -- Perform the first fetch.
> FETCH NEXT FROM ReindexTableCursor INTO @.TableName
> -- Check @.@.FETCH_STATUS to see if there are any rows to fetch.
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- This is executed as long as the previous fetch succeeds.
> PRINT 'Reindexing' + ' ' + @.TableName
> DBCC DBREINDEX (@.TableName )
> FETCH NEXT FROM ReindexTableCursor into @.TableName
> END
> CLOSE ReindexTableCursor
> DEALLOCATE ReindexTableCursor
>

Sunday, February 19, 2012

dbcc checkdb to output file

I've a job set up to run every sunday night, to check the consistency of our
databases.
Unfortunately, when writing a script to check the contents of this file, it
seems that the
file is in unicode.
Does anybody know which unicode format is used for the output file, it looks
like ucs2
but perl seems to recognise it as utf-8.
Any ideas?I don't know, perhaps someone else does, but another option can be to use OSQL through a CmdExec job
instead. I have a feeling that OSQL outputs ANSI...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Stressed" <k@.c.co.uk> wrote in message news:eEhEQW9lDHA.644@.TK2MSFTNGP11.phx.gbl...
> I've a job set up to run every sunday night, to check the consistency of our
> databases.
> Unfortunately, when writing a script to check the contents of this file, it
> seems that the
> file is in unicode.
> Does anybody know which unicode format is used for the output file, it looks
> like ucs2
> but perl seems to recognise it as utf-8.
> Any ideas?
>|||Stressed,
use osql to do this, This is command line utitlity therefore if you want to run it from T-SQL use
xp_cmdshell extended stored procedure.
see following example
--osql
osql /S<server> /Usa /P /dmaster /Q"dbcc checkdb" >> c:\testthis.txt
--using xp_cmdshell
exec master..xp_cmdshell 'osql /S<server> /Usa /P /dmaster /Q"dbcc checkdb" >> c:\testthis1.txt'
--
- Vishal|||or you can use -o parameter of osql as well.
Ex;
osql /S<server> /Usa /P /dmaster /Q" dbcc checkdb" -o c:\testthis2.txt
--
- Vishal|||After looking into it, it appears to be UCS2 little endian.
In case anybody else falls into the same problems.
"Stressed" <k@.c.co.uk> wrote in message
news:eEhEQW9lDHA.644@.TK2MSFTNGP11.phx.gbl...
> I've a job set up to run every sunday night, to check the consistency of
our
> databases.
> Unfortunately, when writing a script to check the contents of this file,
it
> seems that the
> file is in unicode.
> Does anybody know which unicode format is used for the output file, it
looks
> like ucs2
> but perl seems to recognise it as utf-8.
> Any ideas?
>|||sorry, ucs2 big endian. i lied.
"Stressed" <k@.c.co.uk> wrote in message
news:uTzDi3HmDHA.2528@.TK2MSFTNGP12.phx.gbl...
> After looking into it, it appears to be UCS2 little endian.
> In case anybody else falls into the same problems.
>
> "Stressed" <k@.c.co.uk> wrote in message
> news:eEhEQW9lDHA.644@.TK2MSFTNGP11.phx.gbl...
> > I've a job set up to run every sunday night, to check the consistency of
> our
> > databases.
> >
> > Unfortunately, when writing a script to check the contents of this file,
> it
> > seems that the
> > file is in unicode.
> >
> > Does anybody know which unicode format is used for the output file, it
> looks
> > like ucs2
> > but perl seems to recognise it as utf-8.
> >
> > Any ideas?
> >
> >
>

Thursday, March 22, 2012

DBCC Results into a Table Brings Errors!

Sunday, March 11, 2012

DBCC INDEXDEFRAG script not working

DBCC INDEXDEFRAG , DBREINDEX

DBCC INDEXDEFRAG , DBREINDEX

DBCC INDEXDEFRAG , DBREINDEX

Thursday, March 8, 2012

DBCC INDEXDEFRAG

DBCC DBREINDEX\INDEXDEFRAG

DBCC DBREINDEX\INDEXDEFRAG

DBCC DBREINDEX\INDEXDEFRAG

Saturday, February 25, 2012

DBCC DBREINDEX

DBCC DBREINDEX

DBCC DBREINDEX

Sunday, February 19, 2012

dbcc checkdb to output file

DBfile usage

Blog Archive

About Me