4

On my SQL database I run a full backup at 6am and transactional backups every 15 minutes on week days starting at 8am.

Every Monday the first transactional backup is 7 Gb. I want to know why, my database is only 500Mb.

Am I correct in thinking that something is running between 6am and 8am that is causing this massive tran backup? Or could it be something over the weekend that causes the first tran backup to be so large.

migrated from serverfault.com May 11 '15 at 10:25

This question came from our site for system and network administrators.

  • What activity goes during the first log backup or that duration when you see large log file? or is there any maintenance activity during that log backup? – KASQLDBA May 11 '15 at 10:42
  • do you re-index during that time? – RayofCommand May 11 '15 at 10:49
  • Are you performing Index maintenance after the full backup? – James Anderson May 11 '15 at 10:49
  • Can you once again search completely and look at each job which runs on SQL Server. Any Archive,delete,update etc jobs run. What is Autogrowth of Log file.Look also for weekly jobs that run. I guess weekly index rebuild followed by reorganize and then update stats which is done using MP and checkdb and other tasks are causing massive increase in log file. – Shanky May 11 '15 at 12:00
  • 1
    Are you saying that there's a chunk of time during which you aren't running t-log backups? If so, my first suggestion would be to put that on a regular schedule that runs all the time. – Ben Thul May 11 '15 at 14:50
3

You most probably re-index your database between the log backups. This means your log backup contains a lot of transactions from these maintenance operations. Please check your maintenance plans. The size of transaction log can be huge compared to your actual database, imagine this situation. You delete 100k rows and add 100k rows between your backups. Your database will in the end be the same size, but you will have a big transaction log.

  • That makes sense however I can't find where the reindex job has been configured. The reindex job must only occur once a week, while the rest of the backups run daily (on week days) – Simon Foster May 11 '15 at 11:13
  • please check in Management Studio the left panel. Management --> Maintenance Plans - there are the plans stored. – RayofCommand May 11 '15 at 11:22
  • No mention of rebuilding the index there – Simon Foster May 11 '15 at 11:31
  • Connect to the instance with SSMS and check Jobs under SQL Server Agent, as 3rd party applications usually save their maintenance jobs there. Could be reindexing, could be some data import/transformation. And ask the company or the local developers that support the application(s) that use this server as a backend. – Razvan Zoitanu May 11 '15 at 15:23
  • yes, could also be a scheduled task....I guess it's easier to read logs what has been done to your server. but there must be something :) – RayofCommand May 11 '15 at 15:51

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.