2

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!

UnDiUdin
  • 14,924
  • 39
  • 151
  • 249
  • FILESTREAM data is stored in a folder (by sql server design). If i add a blob to my app i see that the number of files in the folder increases of a value of 3. If I delete the file i expect to go back to -3. – UnDiUdin Oct 06 '14 at 07:45
  • 1
    If it's a moderately active database, it should be automatically checkpointing quite frequently anyway. And unless you're starved for disk space (in which case, rather than spending time pondering this, go and get more disk space) you should just trust the system to *eventually* remove unnecessary files. Trying to force it will usually result in poorer overall performance. – Damien_The_Unbeliever Oct 06 '14 at 12:24
  • @Damien_The_Unbeliever you mean the garbage collector anyway runs from time to time even if not invoked manually with CHECKPOINT? – UnDiUdin Oct 07 '14 at 12:31
  • according to this: http://stackoverflow.com/questions/865659/sql-server-checkpoints it seems that checkpoints occurs on server stop or db backup, so for a db backupped on a daily bases taking backup is like doing a checkpoint. Am i right? – UnDiUdin Oct 07 '14 at 12:56
  • 1
    There's an entire section of MSDN documentation on [Database Checkpoints](http://msdn.microsoft.com/en-GB/library/ms189573.aspx). – Damien_The_Unbeliever Oct 07 '14 at 13:01
  • Yes, backups triggers the checkpoint. if you reply to my qusetion i'll accept it. Thank you for the link. – UnDiUdin Oct 07 '14 at 13:07

1 Answers1

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.

UnDiUdin
  • 14,924
  • 39
  • 151
  • 249