Why do the log DBs have a lot of waisted space?

Reply

Glitch replied on 7 Feb 2012 12:16

rated by 0 users

Could it be the database partitions are too big initially (Database Settings)?

Reply

Dan B replied on 13 Feb 2012 8:54 AM

rated by 0 users

J Sloan & srastega -

You should not shrink the database after performing index maintenance.  If you do so, you will likely undo all the work that was done to rebuild the indexes.

If you shrink before index maintenance, the database will have to grow again to accomodate the index rebuilds.  Either way, shrinking is a bad idea and should not be a part of any regular database maintenance.

Here is a snippet from technet that gives a good formula to approximate space requirements when rebuilding indexes:

All work done by DBCC DBREINDEX occurs as a single, atomic transaction. The new indexes must be completely built and in place before the old index pages are released. Performing the rebuild requires adequate free space in the data file(s). With not enough free space in the data file(s), DBCC DBREINDEX may be unable to rebuild the indexes, or the indexes may be rebuilt with logical fragmentation values above zero. The amount of free space needed varies and is dependent on the number of indexes being created in the transaction. For clustered indexes, a good guideline is: Required free space = 1.2 * (average row size) * (number of rows).

For nonclustered indexes, you can predict free space necessary by calculating the average row size of each row in the nonclustered index (length of the nonclustered key plus the length of clustering key or row ID). Then multiply that value by the number of rows. If you rebuild indexes for an entire table, you will need enough free space to build the clustered index and all nonclustered indexes. Similarly, if you rebuild a nonunique clustered index, you will also need free space for both the clustered and any nonclustered indexes. The nonclustered indexes are implicitly rebuilt because SQL Server must generate new unique identifiers for the rows. When you use DBCC DBREINDEX, it is good practice to specify the index you want to defragment. This gives you more control over the operations being performed and can help to avoid unnecessary work.

In this regard, the space is not wasted.  It is simply what is required to do what you want it to do.

- Please note that the transaction log is included in the total space used that is listed under database properties.  This may skew the results you see slightly.

Sincerely,

Dan

Reply

Dan B replied on 14 Feb 2012 9:16 AM

rated by 0 users

Access Denied.

What do I need to do to get access to that doc? 

Reply

J Sloan replied on 14 Feb 2012 9:24 AM

rated by 0 users

Resolution

Reply

J Sloan replied on 14 Feb 2012 9:24 AM

rated by 0 users

alter procedure dbo.USP_DBMANAGER_TASK @task_id int = 0, @debug tinyint = 0 as
begin
  set NOCOUNT ON

  if @debug > 0 print N'==>Enter usp_dbmanager_task()';
  declare @sql nvarchar(4000)

  declare @taskName nvarchar(50)
  declare @referenceInfo int
  declare @historyId int
  declare @status int
  declare @maintenance_id int
  declare @count int
  declare @tbName sysname
  declare @str nvarchar(4000);

  if @task_id = 0
    declare AllActiveTasks cursor for
    select DT.WSE_DB_MAINTENANCE_ID, T.[NAME] TASK_NAME, isnull(REFERENCE_INFO, 0)
      from dbo.WSE_DB_MAINTENANCE DT with (nolock) join dbo.wse_tasks T on DT.wse_task_id = T.wse_task_id
                                                 join dbo.wse_task_status TS on DT.wse_task_status_id = TS.wse_task_status_id
    where TS.NAME = N'ACTIVE'
     for read only;
  else
    declare AllActiveTasks cursor for
    select DT.WSE_DB_MAINTENANCE_ID, T.[NAME] TASK_NAME, isnull(REFERENCE_INFO, 0)
      from dbo.WSE_DB_MAINTENANCE DT with (nolock) join dbo.wse_tasks T on DT.wse_task_id = T.wse_task_id
                                                   join dbo.wse_task_status TS on DT.wse_task_status_id = TS.wse_task_status_id
     where TS.NAME = N'ACTIVE' and t.wse_task_id = @task_id
     for read only;

  -- update current views
  select @count = count(*) from dbo.wse_partitions where deleted = 0 and offline = 0
  select @status = count(*)
    from MASTER..SYSDATABASES
   where [name] in (
         select [DB_NAME] from dbo.wse_partitions where offline = 0 and DELETED = 0
      )
 if (@status != @count)
      exec dbo.USP_UPDATE_VIEWS
 
  open AllActiveTasks
  fetch next from AllActiveTasks into @maintenance_id, @taskName, @referenceInfo
  while @@fetch_status = 0
  begin
    -- generate history data
    insert into dbo.WSE_DB_TASK_HISTORY (wse_db_maintenance_id, START_TIME) values (@maintenance_id, getDate())
    select @historyId = @@IDENTITY
   
    if @debug = 1 print N'  ==> ' + @taskName + N',  ' + cast(@referenceInfo as varchar);
    if @taskName = N'Virtualizer Cleanup' /* 9 */
    begin
        declare @vir_id int;

        set @str = N''
        select top 1 @vir_id = [wse_report_spec_virtualizer_def_id], @tbName = [table_name]
          from dbo.wse_report_spec_virtualizer_def
         where updated_date < getdate() - isnull(@referenceInfo, 5) and wse_task_status_id in (1, 0)
         order by [wse_report_spec_virtualizer_def_id];

        while @@rowcount > 0
        begin
            if @debug > 0 PRINT 'CHECKING ' + @tbName;
            if exists (select 1 from tempdb.INFORMATION_SCHEMA.TABLES where [table_name] = @tbName and [table_type] = N'BASE TABLE')
            begin
                begin transaction
                set @sql = N'drop table ' + @tbName;
                exec sp_executesql @sql;
               
                if @@error = 0
                    commit transaction;
                else
                    rollback transaction;     
            end -- if exists (select * from tempdb.INFORMATION_SCHEMA.TABLES where [table_name] = @tbName and [table_type] = N'BASE TABLE')

            set @str = @str + cast(@vir_id as varchar) + N', ';

            select top 1 @vir_id = [wse_report_spec_virtualizer_def_id], @tbName = [table_name]
              from dbo.wse_report_spec_virtualizer_def
             where updated_date < getdate() - isnull(@referenceInfo, 5) and wse_task_status_id in (1, 2)
               and [wse_report_spec_virtualizer_def_id] > @vir_id
             order by wse_report_spec_virtualizer_def_id;
  
        end  --while @@rowcount > 0
        if len(@str) > 0
        begin
            set @sql = N'update dbo.wse_report_spec_virtualizer_def set wse_task_status_id = 3, updated_date = getdate(), description = ''Cleaned up by dbAdmin'''
                     + N' where wse_report_spec_virtualizer_def_id in (' + @str + N' 0)';
            if @debug > 0 print @sql;
            exec sp_executesql @sql;
        end
    end  -- if @taskName = N'Virtualizer Cleanup' /* 9 */

    else if @taskName = N'REINDEX PARTITION'  /* 2 */
    begin
        declare @dateIndex int

        select @dateIndex = datepart(dw, getDate())
        if (@dateIndex = isNULL(@referenceInfo, 6)+1 )  -- (Sunday as 0, Monday as 1, .. Saturday as 6 vs datepart is Sun as 1 .. Sat as 7))
        begin
            if @debug > 0 print N'Running reindex task'
           
            DBCC dbreindex (N'wse_dbadmin_table_reindex', N'pk_wse_dbadmin_table_reindex', 100);
            DBCC dbreindex (N'wse_dbadmin_table_reindex', N'uni_wse_dbadmin_table_reindex_table_name', 90);
           
   declare @reindex_id smallint,
     @table_name varchar(64),
     @index_name varchar(64),
     @fill_factor smallint;

   select top 1 @reindex_id = [wse_dbadmin_table_reindex_id]
     from dbo.wse_dbadmin_table_reindex
    order by [wse_dbadmin_table_reindex_id] asc;

   while @@rowcount = 1 and @reindex_id is not null
   begin
    select @table_name = table_name,
        @index_name = index_name,
        @fill_factor = fill_factor
      from dbo.wse_dbadmin_table_reindex
     where [wse_dbadmin_table_reindex_id] = @reindex_id ;
     
    if @index_name is null or LEN(@index_name) < 1
     set @str = N'DBCC DBREINDEX (''' + @table_name + N''', '''', ' + +CAST(@fill_factor as varchar) + N')';
    else if @fill_factor is null
     set @str = N'DBCC DBREINDEX (''' + @table_name + N''', ''' + @index_name + N''', '''')';
    else
     set @str = N'DBCC DBREINDEX (''' + @table_name + N''', ''' + @index_name + N''', ' +CAST(@fill_factor as varchar) + N')';
    if @debug > 0 print @sql;
    exec (@str);
    
    select top 1 @reindex_id = [wse_dbadmin_table_reindex_id]
      from dbo.[wse_dbadmin_table_reindex]
     where [wse_dbadmin_table_reindex_id] > @reindex_id
     order by [wse_dbadmin_table_reindex_id] asc;
   end
   
   -- reindex partitions
            declare @partitionId int
            select @partitionId = min(wse_partition_id) FROM dbo.wse_partitions with (nolock)
            where  INDEXED_DATE is NULL and LAST_ENTRY < getdate()-3 and deleted = 0 and offline = 0 and read_only = 0

            set @str = '('

            while @partitionId is not null
            begin
              select @tbName = [db_name] from dbo.wse_partitions with (nolock) where wse_partition_id = @partitionId

              if @debug = 1 print 'shrink database ' + @tbName

              set @SQL = @tbName+'.dbo.usp_reindex_task'
              exec (@SQL)
              DBCC SHRINKDATABASE (@tbName, 10)
              set @str = @str + cast(@partitionId as varchar) + ','
              select @partitionId = min(wse_partition_id) FROM dbo.wse_partitions with (nolock)
               where INDEXED_DATE is NULL and LAST_ENTRY < getdate()-3
                 and deleted = 0 and offline = 0 and read_only = 0 and wse_partition_id > @partitionId
            end -- while
            set @str = @str + '0)'

            -- update partitions table
            set @SQL= 'update dbo.wse_partitions set INDEXED_DATE = getDate() where wse_partition_id in ' + @str
            exec (@SQL)           
        end

        -- SQL Server 2000 sync space usage
        declare @version_num int;
        set @version_num = dbo.udf_dbserver_get_version();

        if (@version_num = 8)
        begin
            declare @newname nvarchar(256)     
            dbcc updateusage (0) -- update catalog database
           
            -- do partition databases
            select @partitionId = min(wse_partition_id) FROM dbo.wse_partitions with (nolock)
             where  INDEXED_DATE is NULL and deleted = 0 and offline = 0 and read_only = 0;
            while @partitionId is not null
            begin
                select @newname = [db_name] from dbo.wse_partitions with (nolock) where wse_partition_id = @partitionId;
                set @sql = N'dbcc updateusage ( ''' + @newname + ''' )';
                --print @sql;
                exec (@SQL);           

                select @partitionId = min(wse_partition_id) FROM dbo.wse_partitions with (nolock)
                 where INDEXED_DATE is NULL and deleted = 0 and offline = 0 and read_only = 0 and wse_partition_id > @partitionId;
            end -- @partitionId is not null
        end -- @version_num = 8

    end  --  if @taskName = 'REINDEX PARTITION'

    else if @taskName = 'DELETE PARTITION'  /* 3 */
    begin
      select @count = count(*) from dbo.wse_partitions with (nolock) where [DELETED] = 0 and [offline] = 0
      if (@count = 0)  -- no more active partition
      begin
          if @debug = 1 print 'DELETE PARTITION: No more active partitions';
          exec dbo.usp_log N'error', 'USP_DBMANAGER_TASK (delete partition): no active partition after this operation (delete partition)';
          set @status = 1;
      end
      else
      begin
          exec @status = dbo.USP_DELETE_PARTITIONS;
      end
    end  --  if @taskName = 'DELETE PARTITION'

    else if  @taskName = 'CLEAN WEBCATCHER LEFTOVER'  /* 4 */
    begin
      declare @securityCount int
      declare @uncategorizedCount int
      declare @securityMAX int
      declare @uncategorizedMAX int
      declare @webcatcherId int
      declare @statusSecurity int
      declare @statusUncategoized int

      select @securityCount      = dbo.udf_table_rowcount('dbo.wc_security_url '),
             @uncategorizedCount = dbo.udf_table_rowcount('dbo.wc_uncategorized_url ');

      select top 1 @webcatcherId = WEBCATCHER_ID,
                   @securityMAX = SECURITY_MAX,
                   @uncategorizedMAX = UNCATEGORIZED_MAX,
                   @statusSecurity = SEND_SECURITY,
                   @statusUncategoized = SEND_UNCATEGORIZED
      from dbo.WEBCATCHER
      order by created_date desc;

      if @@rowcount > 0
          update dbo.WEBCATCHER set SEND_SECURITY  = case when @securityMAX < @securityCount then -1 else @statusSecurity end,
                                    SEND_UNCATEGORIZED = case when @uncategorizedMAX < @uncategorizedCount then -1 else @statusUncategoized end
           where WEBCATCHER_ID = @webcatcherId;
    end -- if  @taskName = 'CLEAN WEBCATCHER LEFTOVER'

   else if @taskName = 'CLEAN FAILED_OVER BATCH' or @taskName = 'PROCESSE FAILED_BATCH'  /* 5 or 7*/
    begin
      if @taskName = 'PROCESSE FAILED_BATCH'
      begin
        declare @error_id   int,
                @done       int,
                @product_id int;
        select @str = N'(',
               @done = 0,
               @product_id = dbo.udf_db_get_prod_id(N'WSE ETL');
        select @error_id=min(wse_error_table_id) FROM dbo.wse_error_tables with (nolock) where wse_product_id = @product_id;

        -- make sure the table exists (don't run more than 7200 seconds (2 hr))
        while @error_id is not null and @done < 7200
        begin
          select @tbName = table_name FROM dbo.wse_error_tables with (nolock) where wse_error_table_id = @error_id;
          if exists (select 1 from sysobjects where [name] = @tbName)
          begin
              set @str = @str + cast(@error_id as varchar) + ',';
              set @SQL = '
              insert into dbo.[INCOMINGBUFFER](
                     [SOURCE_SERVER_IP],[DATE_TIME],[DISPOSITION_CODE],[SOURCE_IP],[DESTINATION_IP],[PROTOCOL_NAME],[URL],[URL_FILE_PATH],[URL_FILE_EXT],[PORT],[CATEGORY],[FILE_TYPE_NAME],[BYTES_SENT],[BYTES_RECEIVED],[DURATION],[KEYWORD],[HITS],[USER_PAT
H_NAMESPACE],[USER_PATH_CATALOG_SERVER],[USER_PATH_DOMAIN],[USER_PATH_LOGIN_NAME] )
              select [SOURCE_SERVER_IP],[DATE_TIME],[DISPOSITION_CODE],[SOURCE_IP],[DESTINATION_IP],[PROTOCOL_NAME],[URL],[URL_FILE_PATH],[URL_FILE_EXT],[PORT],[CATEGORY],[FILE_TYPE_NAME],[BYTES_SENT],[BYTES_RECEIVED],[DURATION],[KEYWORD],[HITS],[USER_PAT
H_NAMESPACE],[USER_PATH_CATALOG_SERVER],[USER_PATH_DOMAIN],[USER_PATH_LOGIN_NAME]
              from dbo.' + @tbName;
              if @debug > 0
                print @sql;
              exec (@SQL)
              set @SQL = 'drop table dbo.' + @tbName;
              exec (@SQL)
          end
          else
          begin
              -- wait for processing
              declare @msg nvarchar(1000)
              set @msg = N'USP_DBMANAGER_TASK(): Process failed_batch failed to locate table ' + @tbName;
              exec dbo.usp_log N'error', @msg;
              WAITFOR DELAY N'00:00:10';
              set @done = @done + 10;
          end  -- if exists (select 1 from sysobjects where [name] = @tbName)

          select @error_id=min(wse_error_table_id) FROM dbo.wse_error_tables with (nolock) where wse_error_table_id > @error_id and wse_product_id = @product_id;
        end -- while
        set @str = @str + '0)'

        -- delete records
        set @SQL= N'delete dbo.wse_error_tables where wse_error_table_id in ' + @str
        if @debug > 0
            print @sql;
        exec (@SQL)

        -- don't forget add-on tables
        exec usp_wtg_admin_tasks @maintenance_id, @taskName, @referenceInfo, @debug;
        exec usp_hosted_admin_tasks @maintenance_id, @taskName, @referenceInfo, @debug;
     end  -- if @taskName = 'PROCESSE FAILED_BATCH'

      if (@taskName = 'CLEAN FAILED_OVER BATCH' )
      begin
        select @error_id = min(wse_error_table_id) FROM dbo.wse_error_tables with (nolock)
         where CREATED_DATE < getDate() - isNULL(@REFERENCEINFO, 20);

        -- make sure to drop table if it exists
        while @error_id is not null and @error_id > 0
        begin
          select @tbName = TABLE_NAME FROM dbo.wse_error_tables with (nolock) where wse_error_table_id = @error_id;
          select @count = count(*) from sysobjects where [name] = @tbName;
          if @count > 0
          begin
            set @SQL = 'drop table ' + @tbName
            exec (@SQL)
          end

          select @error_id = min(wse_error_table_id) FROM dbo.wse_error_tables with (nolock)
     where CREATED_DATE < getDate() - isNULL(@REFERENCEINFO, 20) and wse_error_table_id > @error_id;
          if @@rowcount < 1
            break;
        end -- while

        -- delete records
        delete dbo.wse_error_tables where CREATED_DATE < getDate() - isNULL(@REFERENCEINFO, 20);

        -- don't forget add-on tables
        exec usp_wtg_admin_tasks @maintenance_id, @taskName, @referenceInfo, @debug;
        exec usp_hosted_admin_tasks @maintenance_id, @taskName, @referenceInfo, @debug;
      end  -- if (@taskName = 'CLEAN FAILED_OVER BATCH' )

    end  -- if @taskName = 'CLEAN FAILED_OVER BATCH' or @taskName = 'PROCESSED FAILED_BATCH'

    else if @taskName = 'ETL CLEAN_UP'  /* 6 */
    begin
      delete dbo.wse_etl_tracking    where START_TIME < getDate()- isNULL(@REFERENCEINFO, 45);
      delete dbo.wse_etl_detail      where START_TIME < getDate()- isNULL(@REFERENCEINFO, 45);
      delete dbo.wse_etl_summary     where START_TIME < getDate()- isNULL(@REFERENCEINFO, 45);

      delete dbo.wse_error_logs      where created_date < getDate()-isNULL(@REFERENCEINFO, 45);

      delete dbo.wse_db_task_history where start_time < getDate()-isNULL(@REFERENCEINFO, 15);
      delete dbo.wse_ibt_job_history where start_time < getDate()-isNULL(@REFERENCEINFO, 15);

      -- don't forget add-on tables
      exec usp_wtg_admin_tasks @maintenance_id, @taskName, @referenceInfo, @debug;
      exec usp_hosted_admin_tasks @maintenance_id, @taskName, @referenceInfo, @debug;
    end  -- if @taskName = 'CLEAN ETL_TRACKING'

    else if @taskName = 'AUTO_PURGE'   /* 8 */
    begin
      begin transaction
      update dbo.wse_partitions set DELETED = 1 where DELETED = 0 and MAX_DATE < getDate() - isNULL(@REFERENCEINFO, 31)
      select @count = count(*) from dbo.wse_partitions where [DELETED] = 0 and [offline] = 0
      if (@count = 0)  -- no more active partition
      begin
          print 'AUTO_PURGE: No more active partitions'
          rollback transaction
          exec dbo.usp_log N'error', 'USP_DBMANAGER_TASK: no active partition after this operation (auto purge partitions)'
          set @status = 1
      end
      else
      begin
          commit transaction
          exec dbo.USP_DELETE_PARTITIONS
       end
    end  -- if @taskName = 'AUTO_PURGE'

    select @status = @@ERROR

    -- update history
    update dbo.wse_db_task_history
       set end_time = getdate(), wse_task_status_id = case when @status = 0 then 3 else 4 end
     where wse_db_task_history_id = @historyid;
    fetch next from AllActiveTasks into @maintenance_id, @taskName, @referenceInfo
  end -- while

  close AllActiveTasks
  deallocate AllActiveTasks

  set NOCOUNT OFF
  if @debug > 0 print N'==>Exit  usp_dbmanager_task()';
  return 0;
end  -- USP_DBMANAGER_TASK

Reply

Dan B replied on 14 Feb 2012 10:27 AM

rated by 0 users

Well... that's disappointing.

I thought it was rather a step in the right  direction that you guys had moved the SHRINKDATABASE command ahead of the reindexing task.  At least that would result in good clean indexes, even though the shrink is wasted.

Now, this KB simply moves the shrink back AFTER the reindexing task.

This:
              if @debug = 1 print 'shrink database ' + @tbName
              DBCC SHRINKDATABASE (@tbName, 10)

              set @SQL = @tbName+'.dbo.usp_reindex_task'
              exec (@SQL)
Becomes this:
              if @debug = 1 print 'shrink database ' + @tbName

              set @SQL = @tbName+'.dbo.usp_reindex_task'
              exec (@SQL)
              DBCC SHRINKDATABASE (@tbName, 10)

So, what is the goal?  To maximize space utilization OR to maximize index operation?  Can't have both with these commands.

How about you cut your losses and reorganize the indexes instead?  I would think that would take care of most of the performance concerns without the space requirement.  You would be less likely to encounter blocking and, as a side bonus, you could even get rid of the shrink command altogether.

Thanks,

Dan

Reply

J Sloan replied on 14 Feb 2012 1:23

rated by 0 users

don't shoot the messenger... Honestly, I'd do the shrink afterwards too.  Guess we need a *CASE* to report it as a *BUG*...

any volunteers?

Reply

srastega replied on 15 Feb 2012 8:53 AM

rated by 0 users

So I'm going to assume that it is OK to perform the above modification?  Ill go ahead and submit the bug if no one has yet.  Thanks for looking into this guys.

Reply

srastega replied on 15 Feb 2012 12:05

rated by 0 users

A co-worker of mine had this responce.

"That was a good read. Here are a couple of my thoughts. I’ll leave the decision up to you as to file as bug or DB maintenance adjustment. Both have valid points.

The second responder indicated Shrinking is bad, sure is if you don’t consider how you’re using it. There is a time to and a time not to. They mention a index maintenance procedure. I noticed it calls the standard DBCC rebuild index, agreed it requires additional space to sort and rebuild indexes, but how often does that occur and do you know if a fill percentage is specified in that maintenance? That too could cause the lingering of unused space."

Reply

Dan B replied on 15 Feb 2012 1:51

rated by 0 users

J Sloan

My apologies.  I am not trying to shoot anyone.  I just think that this is an opportunity for improvement.  Simply put - shrinking the data file will absolutely fragment your indexes.

Please allow me to illustrate with this example.  The following is taken from an existing production websense partition on our logging server.  Before my manual shrink, but after going through the DB_MANAGER_TASK shrink and index rebuild the data file was roughly 75 GB with 30 GB Used.  That means about 40 GB of space overhead, but the indexes were nice and clean.

Pre-Shrink:

Now, after the shrink:

As you can see, the indexes are now almost perfectly fragmented.  All at the cost of the shrink operation, which in this case took 30+ minutes, 11.5 million IO ops and >15 minutes of CPU time. 

Now, I can rebuild these indexes but that will require the database to autogrow somewhere around 80 times at the current setting of 512 MB per.  Not exactly an inexpensive operation in itself and the best I can hope for is to find myself in the situation I was in at the start of this example.  Nice indexes, lots of space overhead.

But, if I reorganize these indexes instead, the indexes won't be perfectly defragmented, but we won't need to use all that extra space.  As an added bonus, the indexes will be online for the duration of the reorg.

Post Reorganize:

 Not too bad, especially on the larger tables, and no growth was required to complete the operation.

Given that the DBCC index commands are deprecated, perhaps it is a good time to redo this section of the database maintenance routine.

 -Dan

Reply

J Sloan replied on 15 Feb 2012 3:05

rated by 0 users

Very well thought out response.  I enjoyed the read. 

Do you have the specific steps you'd recommend?  I can pass those onto a DB developer I know.  Additionally, if you make a case on this for a Feature Request, we can get that into the system and see if we can get this into 7.6.5.

And, i'm not any SQL expert by any stretch of the imagination...

Reply

ranch replied on 18 Apr 2012 1:16

rated by 0 users

Is the DB shrink fix going to come out in 7.6.5?

I am curious as we identified this as a problem in our setting as well.