Checkpoint in SQL Server 2016

What is the funcationality for below t-sql: will it truncate/shrink.ldf ?

use [TestDB];
go
checkpoint;
go
VijayAsked:
Who is Participating?

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

x
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.

Olaf DoschkeSoftware DeveloperCommented:
A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also, records information about the transaction log.
(See https://docs.microsoft.com/en-US/sql/relational-databases/logs/database-checkpoints-sql-server)

It may help to be able to shrink the LDF, once that's done, see https://technet.microsoft.com/library/ms179355.aspx and https://technet.microsoft.com/en-us/library/ms189085.aspx and important https://technet.microsoft.com/en-us/library/ms345414.aspx

"No checkpoint has occurred since the last log truncation" (quoted from the last link) can be a reason for delayed log truncation. But also notice:
A checkpoint is essential but not sufficient for truncating the log under the full recovery model or bulk-logged recovery model. After a checkpoint, the log remains intact at least until the next transaction log backup.

If you, in general, have problems with large LDF files, reading up and understanding the internal structure of the LDF file can help, a typical reason is long-running transactions.

Anyway, you don't enforce log shrinking with a checkpoint, but you remove one reason for a delay of log truncation.

Also, last not least, if you want to really shrink the physical size of the LDF file, you need to use DBCC SHRINKFILE, but look at the bigger picture here:  https://technet.microsoft.com/en-us/library/ms178037.aspx

In the long run you back up the logs and thus keep all truncations, so the disk space usage always grows and never shrinks just because the active MDF and LDF file sizes can shrink. SQL Server is not only about active data, it's a database server implementing all ACID criteria, especially consistency. Once you could shrink the LDF it's also likely to grow up again, as the need for that space once was there and likely repeats, unless you can explain it with an unusual operation like bulk operations in full recovery mode. You finally can move off old log backups and store them elsewhere, so removing unused space from the active files always only is a temporary reduction.

Bye, Olaf.
0
Pawan KumarDatabase ExpertCommented:
What is the funcationality for below t-sql: will it truncate/shrink.ldf ?

Well it depends.
Under the simple recovery model, unless some factor is delaying log truncation, an automatic checkpoint truncates the unused section of the transaction log.

In contrast, under the full and bulk-logged recovery models, once a log backup chain has been established, automatic checkpoints do not cause log truncation.

From - https://msdn.microsoft.com/en-us/library/ms189573(v=SQL.110).aspx?WT.mc_id=aff-n-in-loc--pd

Meaning of checkpoint - It writes all dirty pages for your current database to disk. Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk.
0

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
VijayAuthor Commented:
Thank you Pawan.
0
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
SQL

From novice to tech pro — start learning today.