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

Does SQL server reduce the LDF file when doing the backup?
Varshini SAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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 ChenProblem resolverCommented:
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.)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.