Recovery Model Simple - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Recovery Model Simple

Author

Topic

psfaro
Starting Member

49 Posts

Posted - 2011-02-21 : 12:10:16

Hi ,

I've a Database with recovery Model Simple, because all nights, MSSQL runs an a Package that deletes and inserts about 1 million of records in that database.

Recently i notice an error in a trasaction that envolves 3 Tables, but just one table was updated.

Is that any problem of using "Recovery model Simple" with transactions ? I know if i've a problem must restore last backup.

Running a Package (SSIS) uses "Bulk INSERT " , to insert records in Database .

I'm very worried with the problem with the transaction.
Is is possible to happen ?

MSSQL Enterprise Edition 2008 R2

Regards
Pedro Faro

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-02-21 : 12:50:11

Recovery model just affects how much is logged by operations that can be minimally logged and what is required to mark log records as inactive. Nothing to do with transaction's atomicity or durability.

Do bear in mind that SQL will not automatically roll a transaction back just because one statement within fails.

--
Gail Shaw
SQL Server MVP

psfaro
Starting Member

49 Posts

Posted - 2011-02-21 : 13:07:28

Hi Gail,

Tanks for the answer.

The problem i've mencioned is random, but i'm sure that it occurs on a BEGIN TRANSACTIOn ... COMMIT /ROLLBACK block

One customers said that when it happens ,the Server was importing too much data .

Is it possible that MSSQL can exceed The data Locks or something that could ignore the trasaction

Regards

Pedro

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-02-22 : 03:07:13

No. SQL will not ignore transactions and I doubt you're hitting any capacity limits. As I mentioned, be aware that a transaction will not automatically roll back if a statement within throws an error. You have to catch and handle the error. So a transaction with 3 updates where 2 fail with errors could quite easily exhibit the behaviour that you mentioned

If you could maybe explain the error more we might be able to help.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources