Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We use Microsoft SQL Server as a repository of images and data about those images. We record in real time and never care about recovery. If the database slows down and can't handle the number of images, we report this event to the user.

So why do I need to use transactions? Even if the user deletes the images, we do not care about attempting any type of recovery.

All we care about is performance to store the image data. It seem that the transaction process and logs are as big as a master file.

Any thoughts?

share|improve this question

closed as off-topic by Mike Fal, Shawn Melton, Kin, Martin Smith, ypercubeᵀᴹ Jan 2 '14 at 20:19

If this question can be reworded to fit the rules in the help center, please edit the question.

Do you have any persistence requirements at all? If all images were to be somehow lost following a hardware fault would that be a problem or is all of it disposable? – Martin Smith Jan 2 '14 at 20:01
This question appears to be off-topic because the core of the question ignores how RDBMS systems work. – Mike Fal Jan 2 '14 at 20:03
Why are you using a database at all? – Aaron Bertrand Jan 2 '14 at 20:07
Can you tell us more about what this system is doing with the images? It's possible that using SQL Server may not be the best solution for your needs. – Jon Seigel Jan 2 '14 at 20:07
This question appears to be off-topic because it requires much more detail about your use case including disaster recovery requirements. – Martin Smith Jan 2 '14 at 20:14

SQL Server is a A.C.I.D compliant relational database platform, and transaction logs are a critical aspect of this component. Without a transaction log, SQL Server could not ensure any of those attributes. SQL Server wasn't designed to be a 'file store', it was designed to be a data store. Essentially you guys might be using the wrong tool for the job.

If your transaction logs are as big as your actual data files you might not be truncating the data within the log files, or there's a lot of optimization the app layer needs in finishing old transactions. Are you in the SIMPLE recovery mode?

Yes, I have been in shops that use SQL Server to store binary data or in this case, .zip files, it works but there's issues as well. Typically they go with the VARCHAR(MAX) data type, which could let you fill up the entire hard drive with 1 row of data, and it happened before.

If you have to, look at FILESTREAM or the 2014 in memory tables, although I don't think in memory tables work with BLOB object types yet. Neither of those will help your transaction log issue though.

share|improve this answer
memory tables are still logged, but you can delay durability – swasheck Jan 2 '14 at 20:07
I would appreciate a comment on the -1 so we can all learn from down votes. The reply was factually correct and addressed the question directly. – Ali Razeghi Jan 2 '14 at 21:47
Was not me, but a common theory is that answering off-topic questions encourages users to keep asking off-topic questions. – Aaron Bertrand Jan 2 '14 at 22:03
Thanks Aaron, I see. Appreciate it. – Ali Razeghi Jan 2 '14 at 22:03
That doesn't mean delete it, though. Who cares about a down-vote? You're never going to explain every single down-vote. Some people are just arrow-happy. – Aaron Bertrand Jan 2 '14 at 22:04

Not the answer you're looking for? Browse other questions tagged or ask your own question.