x

perc logfile not reduced after logbackup

Hi,

I developed a mechanism which executes a logbackup if the size is above a treshold. I use DBCC SQLPERF(LOGSPACE) to calculate the size of being used. After a logbackup I assume the perc_used is reduced (at least for the committed part). However, this is not the case for some (sharepoint) databases and there are NO active transactions. As a result, I get a lot of very small logbackups.

1) Does somebody have an explanation for this? 2) Is there a way to calculate the committed size of a logbackup?
more ▼

asked Feb 06 '12 at 10:51 AM in Default

Wilfred van Dijk gravatar image

Wilfred van Dijk
927 18 20 24

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

In general, you should be doing log backups on a regular basis aimed at recovery of your data, not at maintaing the size of the log file itself. If you don't need point in time recovery, set the database to simple and then you don't have to worry about the log backups any more.

As to the issue, the way the log gets written, the last entries could be at the very end of the file. So even though you do a backup, you've only removed the earlier entries which are earlier in the file. It won't move those from the end. If you were to issue another checkpoint and then do a backup, likely you'll get to shrink the file.

By the way, shrinking the file over and over again creates massive fragmentation. Also, assuming you've got auto-grow turned on for this log, you're probably looking at ever increasing numbers of VLFs. I'd check that. [Michele Ufford][1] has a script for looking at it.

[1]: http://www.google.com/url?sa=t&rct=j&q=&esrc=s&frm=1&source=web&cd=4&ved=0CD4QFjAD&url=http%3A%2F%2Fsqlfool.com%2F2010%2F06%2Fcheck-vlf-counts%2F&ei=RLQvT9SjMoTy0gGa-LnxCg&usg=AFQjCNG5J2QJTafYp6_O3pu9eIHUep_4-Q&sig2=zLUPiBp1buDPbv7JSMZG-g
more ▼

answered Feb 06 '12 at 11:09 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.6k 19 21 74

Forgot to say this mechanism is an addition the the regular logbackup. It's just a mechanism to prevent large logfiles due to some user actions (like adding a lot of documents in a sharepoint database)
Feb 06 '12 at 11:32 AM Wilfred van Dijk
Sounds interesting. Can you please give more details about the mechanism? Like frequency etc?
Feb 06 '12 at 11:39 AM Usman Butt
  1. load the output of DBCC SQLPERF(logspace) into a table
  2. calculate the used amount of the logspace (logsize/100) * perc_used
  3. if this is above a treshold or the last logbackup is older than ## minutes create a one-time job which executes the backup log statement. This job wil be deleted when it's finished (logfile is not shrinked)
These steps are in another SQL job which runs every 5 minutes
Feb 06 '12 at 02:07 PM Wilfred van Dijk
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x186
x124

asked: Feb 06 '12 at 10:51 AM

Seen: 698 times

Last Updated: Feb 06 '12 at 05:17 PM