We have a highly transactional application. We have a requirment to upload huge data into our database that can happen for a duration of 5-10 minutes. During our upload time, the tables are getting locked that makes the application to wait for the lock to be released.
I have been reading some blogs and following questions on stack overflow - one suggestion was to use READ COMMITTED SNAPSHOT. But i can't seem to use it because my database have filestream non transacted access set to full. I get this message when i try to set the snap shot isolation on:
When the FILESTREAM database option NON_TRANSACTED_ACCESS is set to FULL and the READ_COMMITTED_SNAPSHOT or the ALLOW_SNAPSHOT_ISOLATION options are on, T-SQL and transactional read access to FILESTREAM data in the context of a FILETABLE is blocked.
Though it sets the isolation level, i am concerned about the file table block which is mentioned.
Can someone help me solve this problem?