How to delete physical file associated with File stream

Question

How to delete physical file associated with File stream

binutb

SSCommitted

Points: 1741
More actions
June 26, 2018 at 8:10 am

#406792

Hi
How to delete physical file associated with File stream.
ie:
we are using file stream for storing scanned images. image details stored in a table and images associated with file stream
i wish to delete both table data and associated file stream physical file
Table structure
CREATE TABLE [CR].[ATTACHMENT_DT](
    [ID] [numeric](18, 0) NOT NULL DEFAULT (NEXT VALUE FOR [CR].[SEQ_ATTACHMENT_DT]),
    [ATTACHMENT_ID] [numeric](18, 0) NOT NULL,
    [SORT_ORDER] [int] NULL,
    [FILE_FSTREAM] [varbinary](max) FILESTREAM NULL,
    [UPDATED_ON] [datetime] NOT NULL,
    [UPDATED_BY] [varchar](16) NOT NULL
Pls advise the sql for delete data from above table and associated file stream physical file
Also how to reduce the database size after deleting data from file stream table
Regards
Binu

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

jasona.work SSC Guru Points: 50083 More actions · Answer 1

When you delete data from a filestream table, SQL eventually automatically goes and cleans up the file on-disk. This will happen (potentially) the next time a garbage collection process runs. See this post by Paul Randall for more on that: https://www.sqlskills.com/blogs/paul/filestream-garbage-collection/

As for "reduce the database size" what exactly are you talking about? The MDF / LDF files or the Filestream folder on disk? If it's the latter, then when the garbage collection runs, the now unneeded file(s) will be removed and the space on disk will be freed up. If the former, then you'd have to do a shrink operation, BUT because the file(s) you delete don't actually exist *in* those files, you won't get a lot of space freed up.

binutb SSCommitted Points: 1741 More actions · Answer 2

Hi,

Database size is 103 GB (MDF + LDF + FILESTREAM)

Type	FileName	FileGroup	CurrentSizeMB	FreeSpaceMb
ROWS	SHIPMATE	PRIMARY	7274.1875	22.5
LOG	SHIPMATE_LOG	NULL	71768.125	27267.69531
FILESTREAM	SHIPMATE_FS	SHIPMATE_FG	24150.35938	NULL
			103192.6719	MB

I have deleted all file stream physical file from folder, but DB size is not reduced from 103 GB
FILESTREAM size again 24 gb

how to reduce FILESTREAM size

Regards
Binu

jasona.work SSC Guru Points: 50083 More actions · Answer 3

Did you read the article I linked to?
You *don't* reduce the size of the filestream, the automated garbage collection will remove the files when SQL is sure it doesn't need them anymore.
You *might* be able to force it to clean up sooner by running a backup of the database, but you'll still need to wait for a checkpoint to run.

binutb SSCommitted Points: 1741 More actions · Answer 4

Hi,

deleted all file stream related file from folder.
size of database folder in decreased to 20 GB (103 gb is before deleting files)
In Management studio, right click on database, properties - general tab Size is 103 gb

I have taken database backup (backup file size is also less) and restored as another name
new database also showing 103 gb when taking size in database properties
but new database folder size is less (20 gb , all file mdb, log and file stream folder)

How can correct size in the property window.

Binu