1

I have created filestream group at C:\Test\FilestreamGroup1

and a table with varBinary Filstream column

Now when file is uploaded then it physically stored at FilestreamGroup1...

Now here I want to know two things

  • In which format file is stored at FilestreamGroup1 (for every single uploaded file I found 2 encoded file)?
  • secondly how to delete uploaded file physically (i.e. deleting a record from the table is like execute delete command, but doing this I'll not result in physical deletion of file from NTFS...so how can I delete a file physically)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
RollerCosta
  • 5,020
  • 9
  • 53
  • 71
  • Are you look to do this in TSQL? – Preet Sangha Jun 04 '12 at 05:35
  • 100% duplicate of [FILESTREAM files being left behind after row deleted](http://stackoverflow.com/questions/1625001/filestream-files-being-left-behind-after-row-deleted). Someone not using search is always a good reason to close. – TomTom Jun 04 '12 at 06:14

4 Answers4

2

If you want to delete files from FileSystem instanly then you need to force garbage collection manually by using checkpoint

Link

Community
  • 1
  • 1
RollerCosta
  • 5,020
  • 9
  • 53
  • 71
2

This is not a StackOverflow question, this belongs to ServerFault (admin). It toucehs dev though-

i.e. deleting a record from the table is like execute delete command, but doing this I'll not result in physical deletion of file from NTFS...so how can I delete a file physically

Do you know what the primary reason is to hav a database? Guarantee data integrity.

A delete must keep the data around until a backup is taken. What is your backup policy? YOU may note that when you make an update, another copy of the file is created.... for that simple reason. The old one must still b e available for backup, and that is just how they integrate it.

In which format file is stored at FilestreamGroup1 (for every single uploaded file I found 2 encoded file)?

No, files are stored raw. What would be the sense to encode them... if there are SQL functions to get the path and it is a supported scenario that the client does not use SQL to load the file (but: asks SQL for the file name and path, then accesses it via NTFS file share). This also supports interop (as any program loading from a network can be fed a SQL driven location.

I strongly assume you have 1 copy only and somehow make an update resulting in a second file written.

http://msdn.microsoft.com/en-us/library/cc645962.aspx

has an explanation how to access FileSTream data with SQL.

http://technet.microsoft.com/en-us/library/cc645940(v=sql.105).aspx

has an explanation how to access FIleStream data using Win32.

FILESTREAM files being left behind after row deleted

explains while files are left behind when a row is deleted. I found that using the extremely trivial goodle search for "sql filestream delete file" and it was item 1 on the result list - did you even try google?

Community
  • 1
  • 1
TomTom
  • 61,059
  • 10
  • 88
  • 148
0

secondly how to delete uploaded file physically (i.e. deleting a record from the table is like execute delete command, but doing this I'll not result in physical deletion of file from NTFS...so how can I delete a file physically)

Checkpoint does not remove the files, files are removed in a backgroundprocess and it can take quite a while. To force deletion use

sp_filestream_force_garbage_collection

EDIT: works with SQL Server 2012 only

Michael Sander
  • 2,677
  • 23
  • 29
-1

Write "checkpoint" after deleting a row. it will remove physical existence of file.

Run the below query and check, the file getting deleted from file system automatically

DELETE FROM TableName CHECKPOINT

Thanks.

sam
  • 1
  • 1
  • Welcome to stackoverflow. It would be nice if you improved your answer by a reference and/or a bit of explanation. – Shahbaz Jul 16 '12 at 12:08