Lost with Defrag sproc

I am very new to SQL and inherited a sproc I am not sure is working. I picked a DB out at random to see if the sproc is doing its job and there is object ID’s that have avg_fragmentation of 99%.

 Any help getting this to work correctly would be greatly appreciated.

The Defrag sproc seems to do nothing at all, and I fear that has been the case for a long time.

There is a SQL agent job that is running that has one step that is running EXEC DEFRAG.


So when EXEC Defrag is run it does the following.


SET NOCOUNT ON

      CREATE TABLE #Clients (
            DbName varchar(100) NOT NULL
      )

      INSERT #Clients
      SELECT  [NAme] FROM sys.databases WHERE (database_id % 30) = DAY(GETDATE()) AND [NAME] LIKE 'xs%'


      DECLARE @tablename VARCHAR (128)
      DECLARE @indexName VARCHAR (256)
      DECLARE @execstr   VARCHAR (255)
      DECLARE @objectid  INT
      DECLARE @indexid   INT
      DECLARE @frag      DECIMAL
      DECLARE @maxfrag   DECIMAL
      DECLARE @starttime datetime;
      DECLARE @DbName varchar(100);
      SELECT @maxfrag = 30.0

      WHILE EXISTS (SELECT 1 FROM #Clients)
      BEGIN
            SELECT TOP 1 @DbName = DbName FROM #Clients

            EXEC('use ' + @DbName + '
                        IF EXISTS ( SELECT * FROM sys.procedures WHERE [NAME] = ''_DEFRAG'' )
                              EXEC _DEFRAG
                        ELSE
                              INSERT INTO PRIMARYLMDBSERVER.LDBLog.dbo._fragLog (DBID, DB, table_name, index_name, sdate, edate, process_time, fragmentation)
                              SELECT DB_ID(), DB_NAME(), '''', '''', '''', '''', -1, 0');

            DELETE #Clients WHERE DbName = @DbName
      END

      DROP TABLE #Clients



So I see that during the sproc DEFRAG it also runs another sproc called _Defrag which does the following.




SET NOCOUNT ON

IF ((DB_ID() % 30) = DAY(GETDATE()) )
BEGIN
      DECLARE @tablename VARCHAR (128)
      DECLARE @indexName VARCHAR (256)
      DECLARE @execstr   VARCHAR (255)
      DECLARE @objectid  INT
      DECLARE @indexid   INT
      DECLARE @frag      FLOAT
      DECLARE @maxfrag   DECIMAL
      DECLARE @starttime datetime;
      DECLARE @endtime datetime;
      DECLARE @Max int
            
      SET @Indexid = 0
      SET @Max = 999999

      -- Decide on the maximum fragmentation to allow
      SELECT @maxfrag = 30.0

      -- Create the table
      DECLARE @fraglist TABLE (
            IndexId int,
            TableName varchar (255),
            IndexName varchar (255),
            ObjectId int,
            Fragmentation float )


      INSERT INTO @fraglist  (IndexId, TableName, IndexName, ObjectId, Fragmentation)
      SELECT a.index_id, t.name [TableName], b.name [IndexName], t.object_id, avg_fragmentation_in_percent --, *
      FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a
      INNER JOIN sys.sysindexes AS b ON a.object_id = b.id AND a.index_id = b.indid
      INNER JOIN sys.tables t ON t.object_id = b.id
      WHERE avg_fragmentation_in_percent > @maxfrag
      AND b.indid > 0 -- eliminate the heaps
      order by b.indid asc -- clustered indexes first

      WHILE @IndexID < @Max
      BEGIN

            SELECT TOP 1 @tablename = TableName, @indexid = IndexId, @indexName = IndexName, @objectId = ObjectId, @frag = fragmentation
            FROM @fraglist
            WHERE IndexID < @Indexid
            ORDER BY IndexID ASC
            
            IF @@Rowcount = 0
                  SET @Max = 0

            SET @starttime = GETDATE();

            SET @execstr = 'ALTER INDEX '+@indexName+' ON '+@tablename+' REORGANIZE;';
            
            EXEC (@execstr)

            SET @endtime = GETDATE();

            INSERT INTO PRIMARYLMDBSERVER.LDBLog.dbo._fragLog (DBID, DB, table_name, index_name, sdate, edate, process_time, fragmentation)
            SELECT DB_ID(), DB_NAME(), RTRIM(@tablename), @indexName, @starttime, @endtime, DATEPART(ms, @endtime) - DATEPART(ms, @starttime), @frag

      END

END