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 freeprocache/dropcleanbuffers - please help

sql server 2000 Enterprise sp3a
I am running a loop in Query Analyzer as follows (pseudo coded) for some
testing
that I'm doing.
declare @.var int, @.id int
set @.var = 1
while (@.var < 4)
begin
begin tran
dbcc freeproccache
dbcc dropcleanbuffers
print 'start delete at : ' + cast (getdate() as varchar)
set @.id = 10
delete from mytable where @.id = 10
print 'end delete at : ' + cast (getdate() as varchar)
rollback tran
set @.var = @.var + 1
end
I'm getting weird execution times for each delete. For instance,
the first pass shows me an execution time of around 500ms which
I expect. The 2nd pass shows 500ms also. The 3rd pass shows me
3ms. I used set statistics io on to check the reads and the number
of physical reads drops. I don't know why the dbcc commands
aren't working. Is it the transaction? Any ideas? There is no
other activity on the server... it's my personal box. Please help.You could try CHECKPOINT before the DROPCLEANBUFFER. Note the word "clean", hence adding a
checkpoint will result in all pages clean in the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dodo Lurker" <none@.noemailplease> wrote in message
news:P9OdnXgvFv_MppLYnZ2dnUVZ_tidnZ2d@.comcast.com...
> sql server 2000 Enterprise sp3a
> I am running a loop in Query Analyzer as follows (pseudo coded) for some
> testing
> that I'm doing.
>
> declare @.var int, @.id int
> set @.var = 1
> while (@.var < 4)
> begin
> begin tran
> dbcc freeproccache
> dbcc dropcleanbuffers
> print 'start delete at : ' + cast (getdate() as varchar)
> set @.id = 10
> delete from mytable where @.id = 10
> print 'end delete at : ' + cast (getdate() as varchar)
> rollback tran
> set @.var = @.var + 1
> end
>
> I'm getting weird execution times for each delete. For instance,
> the first pass shows me an execution time of around 500ms which
> I expect. The 2nd pass shows 500ms also. The 3rd pass shows me
> 3ms. I used set statistics io on to check the reads and the number
> of physical reads drops. I don't know why the dbcc commands
> aren't working. Is it the transaction? Any ideas? There is no
> other activity on the server... it's my personal box. Please help.
>|||Hi Tibor
Thank you
I ended up taking the transaction out (the begin transaction and rollback).
What I did instead
- inserted the rows into a temp table
- performed my delete
- re-inserted the rows
I repeated the delete/re-insert within the loop
When I did this, my problem went away. What do you think the transaction
was doing or not
doing? Would it be a log cache issue?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u6ZDWR72GHA.4632@.TK2MSFTNGP03.phx.gbl...
> You could try CHECKPOINT before the DROPCLEANBUFFER. Note the word
"clean", hence adding a
> checkpoint will result in all pages clean in the database.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dodo Lurker" <none@.noemailplease> wrote in message
> news:P9OdnXgvFv_MppLYnZ2dnUVZ_tidnZ2d@.comcast.com...
> > sql server 2000 Enterprise sp3a
> >
> > I am running a loop in Query Analyzer as follows (pseudo coded) for some
> > testing
> > that I'm doing.
> >
> >
> > declare @.var int, @.id int
> >
> > set @.var = 1
> > while (@.var < 4)
> > begin
> >
> > begin tran
> >
> > dbcc freeproccache
> > dbcc dropcleanbuffers
> >
> > print 'start delete at : ' + cast (getdate() as varchar)
> > set @.id = 10
> > delete from mytable where @.id = 10
> > print 'end delete at : ' + cast (getdate() as varchar)
> >
> > rollback tran
> >
> > set @.var = @.var + 1
> > end
> >
> >
> > I'm getting weird execution times for each delete. For instance,
> > the first pass shows me an execution time of around 500ms which
> > I expect. The 2nd pass shows 500ms also. The 3rd pass shows me
> > 3ms. I used set statistics io on to check the reads and the number
> > of physical reads drops. I don't know why the dbcc commands
> > aren't working. Is it the transaction? Any ideas? There is no
> > other activity on the server... it's my personal box. Please help.
> >
> >
>

dbcc dbreindex on all tables/indexes and slow

This is what i ran and it was slow
declare reindex_cursor insensitive cursor for
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
and table_type = 'Base table'
declare @.table_name sysname
open reindex_cursor
fetch next from reindex_cursor into @.table_name
while (@.@.fetch_status = 0)
begin
print ('dbcc dbreindex(' + @.table_name + ')' )
exec ('dbcc dbreindex(' + @.table_name + ')' )
fetch next from reindex_cursor into @.table_name
end
close reindex_cursor
deallocate reindex_cursor
Could the problem be because of the insensitive that i mentioned in the
cursor declaration ? I can see that the DBCC is still running and not being
blocked but its way too slow. If I do cancel in between, will it rollback
everything ?For something like that I always use a STATIC cursor. You also don't need
to use dynamic sql as DBCC DBREINDEX will take a variable for the table
name. My guess why the operation is so slow is that your log and db files
probably don't have enough room in them for this and are constantly growing.
If your in full recovery mode you might want to backup your logs in between
so try something like this:
SET NOCOUNT ON
DECLARE @.TableName VARCHAR(100), @.Counter INT
SET @.Counter = 1
DECLARE curTables CURSOR STATIC LOCAL
FOR
SELECT Table_Name
FROM Information_Schema.Tables
WHERE Table_Type = 'BASE TABLE'
OPEN curTables
FETCH NEXT FROM curTables INTO @.TableName
SET @.TableName = RTRIM(@.TableName)
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT 'Reindexing ' + @.TableName
DBCC DBREINDEX (@.TableName)
SET @.Counter = @.Counter + 1
-- Backup the Log every so often so as not to fill the log
IF @.Counter % 10 = 0
BEGIN
BACKUP LOG [Presents] TO [DD_Presents_Log] WITH NOINIT , NOUNLOAD
,
NAME = N'Presents Log Backup', NOSKIP , STATS = 10,
NOFORMAT
END
FETCH NEXT FROM curTables INTO @.TableName
END
CLOSE curTables
DEALLOCATE curTables
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23iFKndOsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> This is what i ran and it was slow
> declare reindex_cursor insensitive cursor for
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
> and table_type = 'Base table'
> declare @.table_name sysname
> open reindex_cursor
> fetch next from reindex_cursor into @.table_name
> while (@.@.fetch_status = 0)
> begin
> print ('dbcc dbreindex(' + @.table_name + ')' )
> exec ('dbcc dbreindex(' + @.table_name + ')' )
> fetch next from reindex_cursor into @.table_name
> end
> close reindex_cursor
> deallocate reindex_cursor
> Could the problem be because of the insensitive that i mentioned in the
> cursor declaration ? I can see that the DBCC is still running and not
being
> blocked but its way too slow. If I do cancel in between, will it rollback
> everything ?
>|||Why you don't use an database maintance plan?
Ronald
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23iFKndOsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> This is what i ran and it was slow
> declare reindex_cursor insensitive cursor for
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
> and table_type = 'Base table'
> declare @.table_name sysname
> open reindex_cursor
> fetch next from reindex_cursor into @.table_name
> while (@.@.fetch_status = 0)
> begin
> print ('dbcc dbreindex(' + @.table_name + ')' )
> exec ('dbcc dbreindex(' + @.table_name + ')' )
> fetch next from reindex_cursor into @.table_name
> end
> close reindex_cursor
> deallocate reindex_cursor
> Could the problem be because of the insensitive that i mentioned in the
> cursor declaration ? I can see that the DBCC is still running and not
being
> blocked but its way too slow. If I do cancel in between, will it rollback
> everything ?
>|||Now changing gears to the use of dynamic sql. How can i tell what needs a
dynamic sql and what can accept variables. Can you provide some examples and
how can i figure it out ? Also the definition of insensitive and static
seemed to be the same from BOL. Btw, i got the initial script from google
too.
Thanks
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uXF66WQsDHA.4088@.TK2MSFTNGP11.phx.gbl...
> For something like that I always use a STATIC cursor. You also don't need
> to use dynamic sql as DBCC DBREINDEX will take a variable for the table
> name. My guess why the operation is so slow is that your log and db files
> probably don't have enough room in them for this and are constantly
growing.
> If your in full recovery mode you might want to backup your logs in
between
> so try something like this:
>
> SET NOCOUNT ON
> DECLARE @.TableName VARCHAR(100), @.Counter INT
> SET @.Counter = 1
> DECLARE curTables CURSOR STATIC LOCAL
> FOR
> SELECT Table_Name
> FROM Information_Schema.Tables
> WHERE Table_Type = 'BASE TABLE'
> OPEN curTables
> FETCH NEXT FROM curTables INTO @.TableName
> SET @.TableName = RTRIM(@.TableName)
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SELECT 'Reindexing ' + @.TableName
> DBCC DBREINDEX (@.TableName)
> SET @.Counter = @.Counter + 1
> -- Backup the Log every so often so as not to fill the log
> IF @.Counter % 10 = 0
> BEGIN
> BACKUP LOG [Presents] TO [DD_Presents_Log] WITH NOINIT ,
NOUNLOAD
> ,
> NAME = N'Presents Log Backup', NOSKIP , STATS = 10,
> NOFORMAT
> END
> FETCH NEXT FROM curTables INTO @.TableName
> END
> CLOSE curTables
> DEALLOCATE curTables
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23iFKndOsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> > This is what i ran and it was slow
> >
> > declare reindex_cursor insensitive cursor for
> > select TABLE_NAME
> > from INFORMATION_SCHEMA.TABLES
> > where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
> > and table_type = 'Base table'
> >
> > declare @.table_name sysname
> >
> > open reindex_cursor
> > fetch next from reindex_cursor into @.table_name
> >
> > while (@.@.fetch_status = 0)
> > begin
> > print ('dbcc dbreindex(' + @.table_name + ')' )
> > exec ('dbcc dbreindex(' + @.table_name + ')' )
> > fetch next from reindex_cursor into @.table_name
> > end
> >
> > close reindex_cursor
> > deallocate reindex_cursor
> >
> > Could the problem be because of the insensitive that i mentioned in the
> > cursor declaration ? I can see that the DBCC is still running and not
> being
> > blocked but its way too slow. If I do cancel in between, will it
rollback
> > everything ?
> >
> >
>|||Sorry, I spaced on the Insensitive. As for when you can and when you can't
it isn't always spelled out but if you look at the entry in BOL it usually
gives a hint. If there are quotes around the option you can usually use a
variable.
DBCC DBREINDEX
( [ 'database.owner.table_name'
[ , index_name
[ , fillfactor ]
]
]
) [ WITH NO_INFOMSGS ]
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23CQ4liTsDHA.3416@.tk2msftngp13.phx.gbl...
> Now changing gears to the use of dynamic sql. How can i tell what needs a
> dynamic sql and what can accept variables. Can you provide some examples
and
> how can i figure it out ? Also the definition of insensitive and static
> seemed to be the same from BOL. Btw, i got the initial script from google
> too.
> Thanks
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uXF66WQsDHA.4088@.TK2MSFTNGP11.phx.gbl...
> > For something like that I always use a STATIC cursor. You also don't
need
> > to use dynamic sql as DBCC DBREINDEX will take a variable for the table
> > name. My guess why the operation is so slow is that your log and db
files
> > probably don't have enough room in them for this and are constantly
> growing.
> > If your in full recovery mode you might want to backup your logs in
> between
> > so try something like this:
> >
> >
> > SET NOCOUNT ON
> >
> > DECLARE @.TableName VARCHAR(100), @.Counter INT
> >
> > SET @.Counter = 1
> >
> > DECLARE curTables CURSOR STATIC LOCAL
> > FOR
> > SELECT Table_Name
> > FROM Information_Schema.Tables
> > WHERE Table_Type = 'BASE TABLE'
> >
> > OPEN curTables
> > FETCH NEXT FROM curTables INTO @.TableName
> > SET @.TableName = RTRIM(@.TableName)
> >
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > SELECT 'Reindexing ' + @.TableName
> >
> > DBCC DBREINDEX (@.TableName)
> >
> > SET @.Counter = @.Counter + 1
> > -- Backup the Log every so often so as not to fill the log
> > IF @.Counter % 10 = 0
> > BEGIN
> > BACKUP LOG [Presents] TO [DD_Presents_Log] WITH NOINIT ,
> NOUNLOAD
> > ,
> > NAME = N'Presents Log Backup', NOSKIP , STATS = 10,
> > NOFORMAT
> > END
> >
> > FETCH NEXT FROM curTables INTO @.TableName
> > END
> >
> > CLOSE curTables
> > DEALLOCATE curTables
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:%23iFKndOsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> > > This is what i ran and it was slow
> > >
> > > declare reindex_cursor insensitive cursor for
> > > select TABLE_NAME
> > > from INFORMATION_SCHEMA.TABLES
> > > where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
> > > and table_type = 'Base table'
> > >
> > > declare @.table_name sysname
> > >
> > > open reindex_cursor
> > > fetch next from reindex_cursor into @.table_name
> > >
> > > while (@.@.fetch_status = 0)
> > > begin
> > > print ('dbcc dbreindex(' + @.table_name + ')' )
> > > exec ('dbcc dbreindex(' + @.table_name + ')' )
> > > fetch next from reindex_cursor into @.table_name
> > > end
> > >
> > > close reindex_cursor
> > > deallocate reindex_cursor
> > >
> > > Could the problem be because of the insensitive that i mentioned in
the
> > > cursor declaration ? I can see that the DBCC is still running and not
> > being
> > > blocked but its way too slow. If I do cancel in between, will it
> rollback
> > > everything ?
> > >
> > >
> >
> >
>|||On a seperate note, I'd like to encourage you to read the excellent
whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
It will help you determine the best way to deal with fragmentation and
whetehr you need to do anything about it at all, rather than just rebuilding
every table in the database.
Regards,
Paul.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#iFKndOsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> This is what i ran and it was slow
> declare reindex_cursor insensitive cursor for
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
> and table_type = 'Base table'
> declare @.table_name sysname
> open reindex_cursor
> fetch next from reindex_cursor into @.table_name
> while (@.@.fetch_status = 0)
> begin
> print ('dbcc dbreindex(' + @.table_name + ')' )
> exec ('dbcc dbreindex(' + @.table_name + ')' )
> fetch next from reindex_cursor into @.table_name
> end
> close reindex_cursor
> deallocate reindex_cursor
> Could the problem be because of the insensitive that i mentioned in the
> cursor declaration ? I can see that the DBCC is still running and not
being
> blocked but its way too slow. If I do cancel in between, will it rollback
> everything ?
>|||Thanks Paul, I should have included that again for completeness but I know
that link has been referred to him several times already<g>.
--
Andrew J. Kelly
SQL Server MVP
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uwswsAisDHA.2244@.TK2MSFTNGP09.phx.gbl...
> On a seperate note, I'd like to encourage you to read the excellent
> whitepaper at
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> It will help you determine the best way to deal with fragmentation and
> whetehr you need to do anything about it at all, rather than just
rebuilding
> every table in the database.
> Regards,
> Paul.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:#iFKndOsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> > This is what i ran and it was slow
> >
> > declare reindex_cursor insensitive cursor for
> > select TABLE_NAME
> > from INFORMATION_SCHEMA.TABLES
> > where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
> > and table_type = 'Base table'
> >
> > declare @.table_name sysname
> >
> > open reindex_cursor
> > fetch next from reindex_cursor into @.table_name
> >
> > while (@.@.fetch_status = 0)
> > begin
> > print ('dbcc dbreindex(' + @.table_name + ')' )
> > exec ('dbcc dbreindex(' + @.table_name + ')' )
> > fetch next from reindex_cursor into @.table_name
> > end
> >
> > close reindex_cursor
> > deallocate reindex_cursor
> >
> > Could the problem be because of the insensitive that i mentioned in the
> > cursor declaration ? I can see that the DBCC is still running and not
> being
> > blocked but its way too slow. If I do cancel in between, will it
rollback
> > everything ?
> >
> >
>