CODEWITHSUNDEEP
sqlsql-server-2008filestream
RollerCosta
RollerCosta

Reputation: 5186

SQL Server 2008: Filestream how to physically delete uploaded file from filestreamgroup?

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

Upvotes: 1

Views: 5972

Answers (4)

Michael Sander
Michael Sander

Reputation: 2737

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

Upvotes: 0

sam
sam

Reputation: 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.

Upvotes: -1

TomTom
TomTom

Reputation: 62101

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?

Upvotes: 2

RollerCosta
RollerCosta

Reputation: 5186

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

Link

Upvotes: 2

Related Questions