0

I have a log file on a SQL Server database that grows to 70 times its regular size outside of regular business hours.

During regular business hours the transaction log for this DB will safely sit (with no growth operations) around 1Gb but every Saturday morning it grows to 70Gb (about the same size as the data file incidentally.)

The normal backup process (Unitrends device) runs a full SQL backup every morning at 7am. A transaction log backup runs every hour between 9am and 8pm, except 1pm; and a differential backup occurs at 1pm.

On Saturday mornings the full backup is around 75Gb, but on Sunday mornings the full is double the size at 150Gb; the diff from Saturday at 1pm is around 40Gb (normally mon-fri it's 1Gb).

The log file size in SQL Server, if I shrink it, will stay at 1Gb all week. But unless I shrink the log file after this problem on a Saturday it stays at 70Gb (99% of it unused) that causes the backup to take a lot longer.

There are no maintenance plans running on Saturdays during the day. No tasks are scheduled in our application for this time frame either.

I'm not a full time DBA, so I think I could have missed something that is causing this. Or maybe this is normal behavior under some circumstances; there is a db check and index rebuild every week, but in the evening before the full backup.

I know I could shrink the file every Saturday - but I'd rather uncover the real cause of it.

The SQL Server version is 2008 R2.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
rin-tin-tin
  • 21
  • 7
  • If you know it grows at specific times, you should be able to profile the database during that time, and also check what services are running etc. Also try and check the error log for possible information. – Allan S. Hansen Feb 10 '15 at 15:57
  • 1
    Well, something is going on Friday night and into Saturday morning to cause this. SQL Server doesn't just blow up a log file to 70GB for no reason. Can you tell us more about this database? Is it a publisher in replication, are you using mirroring, snapshots, Availability Groups, etc.? When the log file is growing on Saturday mornings, what does `SELECT log_reuse_wait_desc FROM sys.databases WHERE name = N'your database';` say? If the log grows to 70GB *every week* and you need to reserve at least 70GB on the disk to account for this growth, what are you getting out of shrinking it to 1GB? – Aaron Bertrand Feb 10 '15 at 15:57
  • It's just a regular DB server - not a log shipping recipient, no clustering. Shrinking it makes the backup take a lot less time - unitrends backs up the entire log file in it's diff backups, so these are taking 90 minutes for 70Gb instead of 5 minutes for 1Gb. – rin-tin-tin Feb 10 '15 at 16:22
  • @AllanS.Hansen I thought I may have just missed something obvious. I've already checked all logs; next task is watching the server one Saturday morning to get the exact time the file grows (the transaction logs backup are all <10Mb all morning.) – rin-tin-tin Feb 10 '15 at 16:26
  • I'd be dollars to donuts that you have an index defragmentation job running during that time. Take a look at this (http://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/) to see how you can read the log backup file to see what kind of traffic is represented in it. – Ben Thul Feb 10 '15 at 18:20
  • @BenThul That explains part of it because the reindexing occurs at 1am, but by 1pm the log file is already at 40Gb. – rin-tin-tin Feb 10 '15 at 20:28
  • @rin-tin-tin: It starts at 01:00, but when does it *end*? – Ben Thul Feb 10 '15 at 21:05
  • @BenThul it takes 45 minutes to complete. However - the log file is already 40Gb before that process starts. – rin-tin-tin Feb 10 '15 at 21:56
  • @rin-tin-tin: Then you've got something else running. A scheduled purge, perhaps. The log file doesn't grow for no reason (as others have stated). Either way, examining the log backups using the method in the link I posted could shed some light on it. Or you could sit and monitor it like you suggested before. – Ben Thul Feb 10 '15 at 23:06
  • @BenThul - you were correct, this was mostly reindexing. There was another job running during the day that contributed to some growth, but the bulk of it was reindexing. – rin-tin-tin Feb 23 '15 at 14:13

0 Answers0