3

Our product contains several tables which contain custom application security info. These securityinfo tables can in some instances contain a considerable amount of rows (13 Million in one case) the tables are there to speed up queries and can potentially be rebuilt from scratch but this is costly interms of time/processing power etc.

The way the application has been designed, it is more efficient (during the running of the program) to remove records for a security section and then insert the new records rather than identifying the ones to keep/update and inserting the ones which arent already there.

Unfortunately this process can generate a large amount of transaction logs when the database recovery model is set to full.

Is it possible in SQL 2005+ to have a table which isn't logged (recovery mode simple) in a database which has a recovery model of full?

We can rebuild the securityinfo tables when the web application/database first starts in the event of a recovery.

Alternatively would having a parallel database with a simple recovery model be the best solution.

Cheers Tim

Tim
  • 7,401
  • 13
  • 61
  • 102

1 Answers1

2

As far as I can tell, recovery models can only be set per database, probably because the transaction log is for the whole DB. Or as BOL puts it:

A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available.

(Emphasis is mine). So your best bet here is to move this particular table to another DB, or perhaps look into in-memory DBs/caches since the table sounds like a cache that can be rebuilt from an authoritative data source easily.

dsolimano
  • 8,870
  • 3
  • 48
  • 63