troubleshooting Question

Lost with Defrag sproc

Avatar of Alice7
Alice7 asked on
Microsoft SQL Server 2005Microsoft SQL Server 2008
3 Comments2 Solutions530 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
lcohan
Database Analyst

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros