[dba-SQLServer] Very strange behavior

yes, user tables...
-Francisco
http://sqlthis.blogspot.com | Tsql and More...


On Fri, Sep 12, 2008 at 3:00 PM, Arthur Fuller <fuller.artful at gmail.com>wrote:

> 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
> >
> >
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>