App not using transactions 2

[SQLScholar]

Hey all,

I have recently contacted a supplier of an application we use, that uses SQL as a backend.

I want to turn the database from Simple to Full recovery.

However when i contacted the supplier they said it waste of resources because the app "does not use transactions".

The only time i can think (off the top of my head) where the transactions wouldnt be logged - is if they were bulk uploading. But since 99.9999% of the changes will be single rows, it sounds strange.

Can anyone enlighten me? I thought other then bulk it always logged.

Dan

----------------------------------------

http://www.cardboardcoder.com

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[RiverGuy]

A T-SQL batch with one statement, without a TRANSACTION declared by the user is still a transaction. The vendor probably meant that they are not using explicitly declared transactions around multiple T-SQL statements, which is kind of scary in a way. So in theory, you would still get some benefit out of Full recovery, if you really need it.

 

[pandatime]

Actually, what they're saying is - we don't care if we lose all made changes to the data after the last full or diff backup.

You should probably clarify their position.

 

[ramam1]

Dan, the reason to use Full is to enable point in time recovery. That is if you need the ability to revert your database to the state it was in at some previous point in time which you don't know in advance.
You can do point in time recovery in Simple recovery mode by restoring to a backup you've taken. With Full recovery mode, assuming you operate it correctly, you can pick any date/time or even transaction # (from the DBs perspective, not the applications perspective) that you want to revert to and you can do it.

Note that in real life apps often use config files that are outside the DB - if those change you would need to have previous versions or recreate them if you happen to want to revert to a period of time when they were different - this is true for any recovery model.

Your supplier contact is probably not a DBA and didn't understand what the recovery modes in MSSS are.

 

[SQLBill]

We have some users that use an application that creates reports from one of our databases. There are no transactions involved anywhere. Why not? For this requirement, we restore the full production database nightly to a reporting server and the users run their reports on that database all day long. Since they don't work with current data for their reports, it works perfectly for them. So there is no need to do any backups of that database. Maybe your database/application is something similar.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875