How to use CHECKPOINT efficently in a application that uses FILESTREAM

Question

I use FILESTREAM to store BLOBS in my client server application.

In past i had from time to time to clear all BLOBS by executing a command like:

UPDATE BLOBTABLE set BLOBFIELD = NULL

This clears the blobs, i did this to make the DB backup smaller.

But to make the blobs "disappear from disk" i then need to run

CHECKPOINT

Note: this was done as DBA activity, not as part of the software.

Now I realize that in my application I never call CHECKPOINT.

May be i should every time i delete a blob, should i?

Just to experss my self better i make an example of my real case:

My app allows to store files (like pdf documents).

those pdf are saved as blobs in a filestream field.

As the user deletes them (from the ui) I run a DELETE commmand.

I do not call CEHCKPOINT after it, so the garbage collection does not run.

By considering this i realize that i do not have the full thing under control.

So my question is simply: do i need to run CHECKPOINT every time i delete one of those files? is there any drawback in doing this?

Thanks!

Answer 1

A database performs checkpoints in different moments, one of those is when backup is performed.

Since the checkpoint triggers the garbage collection it is not needed (exceptions could be huge or complex scenarios) to call CHECKPOINT in an application because the risk is to reduce the performance.

Better decide to use CHECKPOINT as a maintenance activity if needed, but keeping in mind that a database backup (or even stopping the sql service) has a checkpoint as consequence.