Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sarahmfr
Posting Yak Master
214 Posts |
 Posted - 2011-05-07 : 19:40:58
|
| I am inserting a record in a table, updating a related record in another table and inserting another record in a third table I want all the three steps to take place successfully or none at all. i.e begin transaction and rollback if an error occurs in any of those steps.what is the best isolation level to use so as not to delay the performanceand concerning backup what is the best strategy so as not to lose anything.Thankssarah |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-07 : 20:11:17
|
| Isolation level - depends on the system but usually leave it as the default unless it causes issues.Backup - full recovery model.Use a try catch block to handle errors.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
sarahmfr
Posting Yak Master
214 Posts |
Posted - 2011-05-08 : 22:54:19
|
| ThanksIf I used the default read committed I am using SQL 2008 and I used full backup any recommendation for restoring db To make sure that I do not lose anythingsarah |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-05-08 : 23:22:35
|
| Isolation levels aren't related to backup/restore. Full recovery model is best for recovery purposes.In my opinion, read committed snapshot is the best choice for lots of systems these days. It puts SQL Server inline with Oracle. We've had gigantic performance gains by switching to RCSI. It's actually one of the first things Microsoft will ask about when you call them for help with a performance problem.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-05-09 : 11:10:03
|
| RCSI can be a good thing, but you also need to be aware of the overhead associated with using it. TempDB usage goes up as does CPU. On some really hot systems I've seen CPU bump 20%. In this cases it caused problems because the CPU was already sitting at 80%. But, if you have the hardware or the headroom RCSI can be a good thing. |
|
|
|
sarahmfr
Posting Yak Master
214 Posts |
Posted - 2011-05-09 : 12:41:50
|
| Thanks a lotsarah |
|
|
|
|
|
|
transaction and isolation level