Vijay
asked on
Checkpoint in SQL Server 2016
What is the funcationality for below t-sql: will it truncate/shrink.ldf ?
use [TestDB];
go
checkpoint;
go
use [TestDB];
go
checkpoint;
go
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Pawan.
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:
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.