store attachment files outside DB

Question

Post reply

store attachment files outside DB

mahen-305646

SSC Enthusiast

Points: 148
More actions
November 29, 2007 at 7:23 pm

#119642

Hi All,
I would like to know, SQL 2000/2005 has a feature to store a text/pdf/doc file external to the DATABASE. Breif my requirement. I my current setup users are required to store some doc/text files when they create/modify any record. Currently all such docs are being stored with the table inside the same database. Which has grown so big over 20GB just for 100000 records/rows. Which intrun affects the performance of the application. So I am exploring the option to store all those docs outside DB. At the same time there will be a reference/link to the respective doc dynamically/automatically generated and stored in the same table. Any features available to carry out the same in SQL2000/SQL2005.
Also, how do I track the sql query which is currently running. Is there any commands to capture those info like which userid, sqltext,duration, tables.....?
Thanks n advance for you help.
Rgds,

Viewing 10 posts - 1 through 9 (of 9 total)

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

Ronsi-384491 SSC-Addicted Points: 475 More actions · Answer 1

Hi... here is query to track who & when sp is executed

SELECT distinct so.name, s2.text

FROM sys.dm_exec_query_stats AS s1

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

left join sys.objects so on s2.objectid = so.object_id

WHERE s2.objectid is not null

and so.type = 'p'

order by last_execution_time

Giri Duddu SSCommitted Points: 1638 More actions · Answer 2

u can store the table which are storing files into seperate filegroup which should be placed on seperate disk. Then if will not effect the performance of ur application for other tables.

mahen-305646 SSC Enthusiast Points: 148 More actions · Answer 3

Hi

Thanks a lot for your reply.

As said by dgvsbabu, I move the table to another datafile/filegroup which is located on different volume. But its all accounted under database, that is when the files stored are getting increase, then the size of the database also grow.

Is there any other way to store the files outside DB.

Rgds,

M

Ronsi-384491 SSC-Addicted Points: 475 More actions · Answer 4

sorry ... should be

select top 10 s2.text ,s4.* --login_name,nt_user_name,nt_domain,program_name,host_name

FROM sys.dm_exec_query_stats AS s1

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

JOIN sys.dm_exec_requests as s3 ON s1.sql_handle = s1.sql_handle

JOIN sys.dm_exec_sessions as s4 ON s3.session_id = s4.session_id

left join sys.objects so on s2.objectid = so.object_id

WHERE s2.objectid is not null

order by last_execution_time desc

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 5

SQL 2005 does not really support storing an updating something in the file system. There are some things in SQL 2008 suggesting that it is a direction MS is intending to go.

You could store the files in a directory somewhere and then store a path to the file in the database. If you wanted to continue to use your existing stored procedures and all of your application logic, you would probably have to use the CLR to efficiently stream the information to and from the operating system.

One of the reasons this has not been natively supported is it does not allow for a consistent data backup. If you take a database backup and a backup of your folder with all of your files, they could be slightly different giving you currupt data (a path in which there is not really a file, or an orphaned file). So, implementing this on any high-availability system generates some concerns.

It would also be worth pointing out that you will probably hurt your performance overall. Getting a file from a directory (even when you have the full path) does require the OS to find that file. Finding the file in a table with an index is likely to be more efficient in a lot of cases. If you were to take the earlier suggestion of moving the binary data into another filegroup and put that filegroup in a completely different database file, you may get a better result that is much easier for you to implement.

mahen-305646 SSC Enthusiast Points: 148 More actions · Answer 6

mahen-305646

SSC Enthusiast

Points: 148

December 1, 2007 at 3:14 pm

#756256

Thank you very much for your support.

I got it clear.

Steve Jones - SSC Editor SSC Guru Points: 736252 More actions · Answer 7

SQL Server 2008 will have things built in to help ensure that the files are synchronizes with the data, they can be backed up, but also queried with T-SQL.

There is a lot of debate about storing in or out of the db. I prefer outside for easy access to the files, as well as a separate backup. Often the files don't change, so I don't want them in a full backup.

If you store outside, then you should have some utility to reconcile the files and paths with the data in the db. I can and likely will, get out of synch.

mahen-305646 SSC Enthusiast Points: 148 More actions · Answer 8

Hi Ronsi,

thanks for the script. But it seems works in SQL2005. Do you have one for SQL2000?

Rgds

Ronsi-384491 SSC-Addicted Points: 475 More actions · Answer 9

Hi..

You can use sp_tracexxx

There's an article about this SPs

"Using SQL Trace to Audit Database Access"[/url]

Or

Auditing Store Procedure Execution

for eventID, EventName and Description : please see SQL Server Books Online, search "sp_trace_setevent"

Ronsi