[dba-SQLServer] Very strange behavior

Thanks Francisco. I assume the tables mentioned are user tables?

On Fri, Sep 12, 2008 at 11:39 AM, Francisco Tapia <fhtapia at gmail.com> wrote:

> Arthur... I have built my defrag indexes scripts for Sql 2005 following
> this
> model... that I wrote up on my blog
> http://sqlthis.blogspot.com/2005/04/defrag-those-indexes-maintenance.html
>
> the initial simply finds the fragmented indexes... I have a much more
> complete script that I can post later today, but here is the basic portion
> on the defrag section... in where I simply defrag...
>
>
>
> SET NOCOUNT ON
>
> DECLARE @TableName AS VARCHAR(500),
>        @TableOwner AS VARCHAR(500),
>        @Rows AS BIGINT,
>        @aAvg AS DECIMAL(10,3),
>        @SQL AS VARCHAR (1000)
>
> WHILE EXISTS(SELECT * FROM db_defrag)
> BEGIN
>    SELECT TOP 1
>        @TableOwner = TableOwner,
>        @TableName = TableName --TableName,
> AVG(avg_fragmentation_in_percent)aAvg, AVG(ROWS) aRows,
>        , at Rows = AVG(ROWS), @aAvg = AVG([avg_fragmentation_in_percent])
>        FROM db_defrag
>    GROUP BY TableOwner, TableName
>    ORDER BY AVG(ROWS) DESC
>
>    SET @SQL = 'BEGIN TRY ' +
>    'ALTER INDEX ALL ON ' + @TableOwner + '.[' + @TableName + '] REBUILD
> WITH(MAXDOP=2, ONLINE=ON)'
>    + ' END TRY '  +
>    + ' BEGIN CATCH '
>    + 'ALTER INDEX ALL ON ' + @TableOwner + '.[' + @TableName + '] REBUILD
> WITH(MAXDOP=2, ONLINE=OFF)'
>    + ' END CATCH '
>
>    IF GETDATE() <  '5/4/08 23:59'--CONVERT(VARCHAR(12), GETDATE(), 110) +
> ' 22:00'
>    BEGIN
>        PRINT 'Defragging TABLE ' + @TableName
>        EXECUTE(@SQL)
>        PRINT 'Clearing from db_defrag table'
>        DELETE FROM db_defrag WHERE TableName = @TableName
>        INSERT INTO db_defrag_complete(TableOwner, TableName, ROWS)
> VALUES(@TableOwner, @TableName, @Rows)
>        SET @SQL = 'UPDATE STATISTICS ' + @TableOwner + '.' + @TableName
>        EXEC(@SQL)
>    END
>    ELSE
>    BEGIN
>        PRINT 'Skipping TABLE ' + @TableName
>        DELETE FROM db_defrag WHERE TableName = @TableName
>        INSERT INTO db_defrag_skipped(TableOwner, TableName, ROWS,
> [avg_fragmentation_in_percent]) VALUES(@TableOwner, @TableName, @Rows,
> @aAvg)
>    END
>
> END
>
> -Francisco
> http://sqlthis.blogspot.com | Tsql and More...
>
>
> On Wed, Sep 10, 2008 at 9:14 PM, Arthur Fuller <fuller.artful at gmail.com
> >wrote:
>
> > In the simple case of Northwind you are correct, but try it against
> > Adventureworks and you will see the problem. Try running these
> statements:
> >
> >
> > EXEC
> > sp_MSForeachtable ('ALTER INDEX ALL ON ' + schema_name() + '.' ? +
> 'REBUILD
> > WITH ( PAD_INDEX = OFF, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON
> > )')
> >
> > EXEC
> > sp_MSForeachtable ('ALTER INDEX ALL ON ? REBUILD WITH ( PAD_INDEX = OFF,
> > SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON )')
>  > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> >
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>