2

We are running SCCM 2012 r2 Reporting services and the SQL Server log file has got out of hand due to the backup not being set up properly and I'd like to confirm something before I continue.

I have set up our backup solution to do full backups of the DB and an incremental that is set to truncate the log on a regular schedule now but from what I've read, shrinking your log file is a bad idea due to performance issues, but it seems like this is the sort of case where it seems appropriate as we're sitting around 220GB for the log file.

Firstly, should I definitely be attempting to shrink the log file and what is the best way to do it? Is it simply by using DBCC SHRINKFILE?

Secondly, I believe I should be setting a reasonable limit on the log file size after shrinking but I'm unsure how to also come up with this limit. Is it just a size I'm happy with it taking up in-between truncation backups or is there a best practice.

As you may tell I'm not that familiar with SQL Server so I apologise if I've worded anything incorrectly. Thanks in advance.

|improve this question
  • 2
    What about log backups? – Ramakant Dadhichi Jul 12 '17 at 20:41
  • 2
    Configure log backups for every 15 minutes and you should be fine. Log backup is very essential for Point in Time recovery as well as to control the log growth. It acts as check point and truncates the transaction log itself. Any db which is in Full/bulk logged recovery mode will need to have log backups configured. – Ramakant Dadhichi Jul 12 '17 at 21:03
2

In addition to your Full and Incremental/Differential backups you should be doing a backup on the transaction log in a reasonable frequency to mark all locations in the log file with committed entries as available for reuse instead of doing a truncation on the log file. With a correctly sized log file the transaction log backups will help to keep your log file from growing continuously and might only grow if it a larger volume of changes come in between backups windows then you predicted for.

Now on to log file shrinking. Shrinking your log file is not necessarily bad it will just take a bit of planning to do it correctly. (Make sure you are doing this in a maintenance window with a full backup before you begin and if at all possible without any other users connected.) You can use the DBCC shrink command on it to get it down to 1MB but right after you should regrow the log file back to a size that is appropriate for the rate of change that is happening in your database plus a free space buffer to handle larger than normal amounts of data changes. Also growing the log file in specific consistent increments will create evenly sized VLFs that can help with log performance. For example after you shrink it down to 1MB you regrow the log file in 1GB increments until you hit 5GB in size.

Kimberly Tripp has a couple of good articles explaining VLFs and transaction log throughput that might be helpful to read over. Do take note of her warning about a bug in growing the log file by 4GB.

Here is another VLF reference from Brent Ozar that has some more details and also references Kimberly's posts.

It's worth noting, Thanks @BradC, that shrinking and regrowing your log file should only have to be done once or only if your log file grows causing unevenly sized VLFs. But if you set your auto growth size on the log file to the specific size increments that use used to originally grow it back from 1MB you shouldn't see uneven VLFs even after it auto grows.

|improve this answer
  • Worth mentioning that the process you describe in your second paragraph ought to be a one-time deal (or very rare). Once you have it properly sized, it should use and free space internally, and not change the (on disk) file size, except perhaps in unexpectedly busy periods. – BradC Jul 13 '17 at 7:16
  • Very good point. – Aaron Jul 13 '17 at 7:26
  • Thanks, what's your view on the log truncation frequency? It's a single site and isn't busy at all, I see every 15 minutes as a common answer but that seems very frequent.I'm using @BradC 's query from below and checking the growth currently (~2GB over 24 hours) so your example of a 5GB file seems to work quite nicely and possibly a log truncation daily? – Crimsonfox Jul 13 '17 at 10:11
  • @Crimsonfox Zero reason to do explicit log truncations, ever. Just run transaction log backups. Using Aaron's method, get the log into a good size (once only), and leave it that size forever. As far as frequency, just have to think about recovery options in a disaster scenario, like what if you can't take a final "tail log" backup? If you're only doing log backups nightly, you could lose a day's worth of work, and you might as well be running in SIMPLE recovery mode. – BradC Jul 13 '17 at 13:26
  • @Crimsonfox, BradC is correct there usually no reason to ever truncate the log file. You could think of the transaction log backup as a truncation of a sort, it's just marking the internal items that have been committed as over-writable in the slots in the log file. – Aaron Jul 13 '17 at 14:16
1

It sounds like your database is in full recovery mode, but you are not running transaction log backups. That's not a compatible combination.

You have 2 choices:

  1. Leave it in full recovery mode, and take (and keep) regular transaction log backups in addition to your full and differential backups.
  2. Switch it to simple recovery mode.

Note this is a business decision, not a technical one.

Full recovery mode is only necessary if you need "point-in-time" recovery, the ability to rewind to any arbitrary point between full/differential backups if something goes wrong.

If the database is in simple recovery mode and something bad happened (corruption, someone deletes a table, etc), then you'd have to go back to the latest full or differential backup, and you could lose more recent changes made to the database. But for certain kinds of databases (reporting/processing databases, development/test/QA environments, etc), this is acceptable.

Once you've decided which mode you need to support and made the appropriate changes, your tran log shouldn't be yo-yo-ing up and down in size. Use a query like the following to see how much internal space is actually being used in your file:

SELECT DB_NAME() as dbname, type_desc, name as logical_name, 
    CONVERT(decimal(12,1),size/128.0) as TotalMB,
    CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0) as UsedMB,
    CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0) as FreeMB,
    physical_name
FROM sys.database_files WITH (NOLOCK)
ORDER BY type, file_id;

Between tran log backups you'll see the "used" space in the log file grow, and it should drop down when the log backup runs. The total file size should stay the same. I would suggest shrink the log file down to the largest "used" value you see plus, say, 20%, and leaving it with a growth setting of 2GB or 4GB, so it can grow a bit if it needs to.

|improve this answer
  • Thanks Brad, if I could mark 2 answers as correct I would, a combination of yours and @Arron's would be perfect! – Crimsonfox Jul 13 '17 at 10:12
0

The primary performance issue with shrinking the log is the fact that (outside of extraordinary situations, like yours) it will need to grow again. It can actually take longer to grow the log file than the data file (new space in the data file can basically be marked as "free", where new log file space has to actually be written over with zeros). Plus, if you shrink the log file down and then use that space for something else, then when the log file does need to grow, it may not be able to.

DBCC SHRINKFILE is what I would use. If in doubt, yuou can set up what you want via SSMS (right-click the DB; choose Tasks -> Shrink -> Shrink Files), and have it generate the script for you. Basically, you just need to specify the file to shrink and the target size.

Log file shrinks usually happen quickly. However, you won't get an error if the file didn't shrink. SQL does not rearrange the log file to free up space at the end of the file, andd will only drop space from the end. Issue the command multiple times, if needed; this can generate enough activity to shift the current activity point in the log, and move it past the end. If all else fails, try again later.

You can limit the log file growth, but you do risk that chance that you'll need more space than you've allowed at some point, and operations will fail because the log is full. If you've got free space, I would probably just check on the log periodically, to make sure all looks good.

|improve this answer
  • Doesn't seem to address whether they are in the correct recovery mode and/or whether they are taking tran log backups, all of which needs to be determined before these considerations. – BradC Jul 13 '17 at 7:20
  • @BradC - True - I read his statement that he was doing incremental backups as if it said transaction log backups, and they could be differentials instead. And, if you can successfully take a transaction log backups, you're using the FULL or BULK LOGGED recovery model. – RDFozz Jul 13 '17 at 14:20

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.