• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 33
  • Last Modified:

Does SQL server reduce the LDF file when do the backup?

Does SQL server reduce the LDF file when doing the backup?
Varshini S
Varshini S
3 Solutions
Nitin SontakkeDeveloperCommented:
Yes, it does.

However, unfortunately, the answer is not that simple. It all depends on what is the 'Recovery Model' of the database.

You may wish to dig deeper if you wish. http://www.sqlservercentral.com/articles/books/89519/
Scott PletcherSenior DBACommented:
The q can be interpreted different ways.

I think you mean:
Does SQL Server reduce the size of the LDF file after backing up the log file?
The answer is no.  you have to explicitly shrink a log file to reduce its size.

But f you mean:
Does SQL back up the entire log file when backing up the entire db?
The answer is no.  SQL backs up the active portion of the log necessary to recover the db to a consistent pont.  That's usually not a lot of data, but it can be in very specific cases.
Varshini SAuthor Commented:
My question is, does SQL server reduces the LDF file size after doing the database backup?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
No.  Neither backing up the log nor the db will automatically reduce the log file size.  As I stated above, you must explicitly shrink a log file to reduce its size, that is, issue a "DBCC SHRINKFILE(2, ...)" command.
HuaMin ChenSystem AnalystCommented:
No, it will not reduce .ldf file. When doing backup, it would create .bak file.
Varshini SAuthor Commented:
But I heard when the database in "simple recovery model" when you do the backup the LDF file size will be reduced.  Is it true?
Scott PletcherSenior DBACommented:
No.  The log file will be "truncated" when you backup the db, as SQL Server calls it, but that does not shrink the physical size of the file (although it sounds like it does).  It simply marks log space as available to be written to again.

As background:
The log file is a sequential file.  When SQL gets to the end of the file, it wraps around and starts writing at the front of the file again.  But SQL can't write over any part of the log file that is still in active use.

In simple recovery, you can't backup the log file, but when you backup the db itself, SQL will mark the previously used log file space as "this can be overwritten now".  (Unless you had replication or some other process that required the log records to be kept longer.)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now