0

I would request to have expert advise on scheduling maintenance and backup jobs. Below was the scenario prior to my change:

  1. Full backup of database scheduled to run at 12:30AM everyday.
  2. Differential Backup scheduled to run every 2 hours in the business hour(8AM to 6PM) and every 6 hour in the non-business hour.
  3. Log backup is scheduled to run every 15 minutes as Log-shipping is configured.
  4. Index optimize job(Using script from Great Mr. Ola Hallengren) running every Sunday morning at 1:45AM.

We used to face space issue on the storage disk in the above scenario as Full backup was run before Maintenance job and hence subsequent differential backup was getting bigger and bigger in size until next full backup was run. This prompted me to run Full backup after maintenance job and I also checked the fragmentation level in mid of week and based on the values decided to run maintenance job twice a week, below is the modified plan:

  1. Full backup of database scheduled to run at 12:30AM on all days except on Sunday and Tuesday when maintenance job is scheduled. On Sunday and Tuesday, Full backup is taken at 2:30AM.
  2. Differential Backup scheduled to run every 2 hours in the business hour(8AM to 6PM) and every 6 hour in the non-business hour - No change.
  3. Log backup is scheduled to run every 15 minutes as Log-shipping is configured - No change
  4. Index optimize job(Using script from Great Mr. Ola Hallengren) running every Sunday and Tuesday morning at 1:45AM.

The issue I am facing right now is the size of log backup immediately after maintenance job, Log backup is much bigger than Full backup of database itself. Needless to say, Log backups are transferred to Secondary site, which is then uploaded there for syncing purpose. This taken longer than expected and in between Log-shipping alert gets triggered since primary and secondary is not in sync. Earlier also, Log backup was bigger however used to be much lesser than that of Full backup and was taking considerably lesser time in transferring from primary to secondary server.

I am not really sure, if this is a valid scenario wherein the changes(insert/update/delete) was so voluminous in last 3 days that the maintenance job created bigger log file than full backup and would stabilize gradually or I should be scheduling two full backup on Sunday and Tuesday(when maintenance job is running) - One at 12:30AM and another one after maintenance job.

Appreciate your kind advise.

  • I'm not 100% on what your question is - is it why is your log file so big after running INDEX REBUILD operations? – George.Palacios Feb 19 at 8:45
  • @George.Palacios I am trying to understand relationship between Log backup and Full backup when maintenance job is run. If Full backup is taken prior to Index Rebuild operation, size of log file seems to be lower whereas in the case when Full backup is taken only after maintenance job, log file taken during that interval seems to be bigger. – Learning_DBAdmin Feb 19 at 9:14
  • The size of a full backup will be the same in both cases: taken before or after your index rebuild. The only case when it will be bigger it's when your full backup runs concurrently with index rebuild, in this case full backup will contain more log records than as usual. The size of log backup will be much bigger after index rebuild, it will include all the index pages for all the rebuilt indexes – sepupic Feb 19 at 9:21
  • @sepupic Agreed on Full backup. My concern is on the size of log backup - Is there any relationship between size of log file when full backup is taken prior to maintenance job and when its not taken. – Learning_DBAdmin Feb 19 at 9:27
  • >>>Is there any relationship between size of log file when full backup is taken prior to maintenance job and when its not taken.<<<< No, there is no relationship at all. The next log backup starts where the previous stopped. So if you make a rebuild of 1Tb if indexes, all the 1Tb will finish in your log file, in the subsequent log backups, and it will not affected by full backup at all – sepupic Feb 19 at 9:30
0

According to the scheduled maintenance tasks and second backup strategy here is my suggestion which may suit your requirement to reduce transaction log backup. As you said in the comment, “Log-Shipping is enabled for the databases. Log-Shipping feature support both FULL and BULK_LOGGED recovery model.

So

1st thing you can do if you want to continue with FULL recovery model.

You can add two steps in the schedule maintenance job:

  1. Change recovery model of database to BULK_LOGGED
  2. Execute Index maintenance script
  3. And Change recovery model back to FULL

Pros: It will reduce truncation log backup size. Cons: Point-In-Time recovery will not be possible for the duration.

2nd thing you can do if it suits your SLA (RTO & RPO) as you are backing-up transaction log every 15 minutes, “change recovery model to BULK_LOGGED.

3rd action you can perform to find all unused indexes and remove them, it’ll help to reduce T-Log backup size and maintenance duration as well. You can use this script to find index details.

CREATE TABLE #tbl_index_info(
    [Table Name] [sysname] NOT NULL,
    [Index Name] [sysname] NULL,
    [Index Type] [nvarchar](60) NULL,
    [Index Columns] [sysname] NULL,
    [Row Count] [bigint] NULL,
    [Fill Factor] [tinyint] NOT NULL,
    [user_seeks] [bigint] NOT NULL,
    [user_scans] [bigint] NOT NULL,
    [user_lookups] [bigint] NOT NULL,
    [Index Read] [bigint] NULL,
    [Index Writes] [bigint] NOT NULL,
    [Last Used (Read)] [datetime] NULL,
    [Last Used (Write)] [datetime] NULL,
    [Last Index Re-Built/Re-Organize] [datetime] NULL,
    [index_id] [int] NOT NULL,
    [object_id] [int] NOT NULL
) 
GO

insert into #tbl_index_info
select      t.name [Table Name]
            ,i.name [Index Name]
            ,i.type_desc [Index Type]
            ,c.name [Index Columns]
            ,p.rows [Row Count]
            ,i.fill_factor [Fill Factor]
            ,ius.user_seeks
            ,ius.user_scans
            ,ius.user_lookups
            ,(ius.user_seeks+ius.user_scans+ius.user_lookups) [Index Read]
            ,ius.user_updates [Index Writes]
            ,COALESCE(ius.last_user_seek,ius.last_user_scan,ius.last_user_lookup) [Last Used (Read)]
            ,ius.last_user_update [Last Used (Write)]
            ,STATS_DATE(s.object_id, s.stats_id) [Last Index Re-Built/Re-Organize]
            ,i.index_id
            ,t.object_id

from        sys.tables t
inner join  sys.columns c
on          t.object_id=c.object_id
inner join  sys.indexes i
on          t.object_id=i.object_id
inner join  sys.index_columns ic
on          t.object_id=ic.object_id and i.index_id=ic.index_id and c.column_id=ic.column_id
inner join  sys.dm_db_index_usage_stats ius
on          t.object_id=ius.object_id and i.index_id=ius.index_id
inner join  sys.stats s
on          t.object_id=s.object_id and i.index_id=s.stats_id
inner join  sys.partitions p
on          p.object_id=i.object_id and p.index_id=i.index_id
where i.name is not null and i.is_unique!=1
Order by ius.user_lookups desc
GO

Select      DISTINCT
            a.[Table Name],a.[Index Name],a.[Index Type],
            (SELECT SUBSTRING(
            (
                SELECT ',' + [Index Columns] FROM #tbl_index_info b
                WHERE a.[Table Name]=b.[Table Name] and a.[Index Name]=b.[Index Name]
                FOR XML PATH('')), 2,10000)
                )[Index Columns]
                ,a.[Row Count]
            ,ips.avg_fragmentation_in_percent,a.[Fill Factor],a.user_seeks,a.user_scans
            ,a.user_lookups,a.[Index Read],a.[Index Writes],a.[Last Used (Read)],a.[Last Used (Write)]
            ,[Last Index Re-Built/Re-Organize]
from
#tbl_index_info a
cross apply
            sys.dm_db_index_physical_stats(db_id(),a.object_id,a.index_id,0,DEFAULT) ips
ORDER BY [Index Read] ASC
GO

DROP TABLE #tbl_index_info
GO

4th you need to verify that you are using appropriate FILLFACTOR for indexes. Less FILLFACTOR means more number for pages. So chose FILLFACTOR according to usage of table.

Thanks!

  • This is a production database and can't afford to take risk of losing data during maintenance window. Finding unused and getting them removed is the most difficult and time consuming process as the application is supported by vendor and can't take call myself to delete them without having their confirmation. I need to study more about fill factor and see if I can make use of this. Fill factor is also suggested in another comment. Thank you for your answer. – Learning_DBAdmin Feb 20 at 11:45
  • I would be very cautious about removing "unused" indexes via this methodology. The sys.dm_db_index_usage_stats DMV is reset every time the server restarts. If you have important indexes in place that are only used infrequently, you may end up deleting one of those indexes causing a lot of pain later on. See my post for the details – Max Vernon Mar 4 at 14:19
0

In addition to the above you can schedule so that your log backups don't run during the index maintenance and reschedule your full to run after the maintenance job. You will need to set up a backup log to null as well to run during the maintenance job. The full backup will pick up all the changes but only if you clear the log during the process. Otherwise the full will run and you will have another large log backup to deal with. I do something similar for large ETL loads.

Index maintenance creates a lot of transaction log logging. Another thing you can do is run the maintenance daily. This will result in smaller log backups.

I've dealt with the same issues years ago and switching to daily maintenance helped. Instead of a weekly 3-6 hour process it became a daily 15-30 minute process.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.