Disabling logging on certain tables

Question

I'm using SQL Server 2005. I have two tables that contain aggregate information. The information is constantly being updated, generating almost 5GB of log data a day. (That's larger than the entire database!) I'd like to disable logging on these tables, as rolling back is not really necessary. I would however like to keep logging on the other tables in the database.

Is it possible to disable logging on certain tables within the database? If not, can I place the two tables in the same schema, then disable logging on the schema? Is the only option to move the two tables to a separate database, and disable logging there?

Update: I guess I'll explain why I really don't need to log the activity on these tables.

The two tables are filled with GPS data, so they get quite large. The first table is capturing raw locations from six Android tables in the field. New data from each of the tablets comes in every 5-10 seconds. That information is then aggregated as locationA, locationB, travelTime. The goal is to ultimately have the shortest travel times between all locations, based on actual driving data. The data is only for a small city, and only accurate to four decimal places, so it is manageable. However, as new raw data comes in, there are slower travel times that need to be updated, and new ones that need to be inserted.

Once the raw data is aggregated, it is purged. We're not going backwards to longer travel times, so that is why rolling back does not matter so much in these tables.

Answer 1

• Is it possible to disable logging on certain tables within the database?
• Can I place the two tables in the same schema, then disable logging on the schema?
• Is the only option to move the two tables to a separate database, and disable logging there?

Logging of user operations cannot be disabled.

There is a class of operations called minimally-logged operations that only allows a transaction to be rolled back (as opposed to also being able to roll forward). However, it doesn't sound like what you're doing would meet the criteria for that to apply, nor do I think this would solve the problem anyway.

The SQL Server solution would be to land the staging tables in a new database in SIMPLE recovery, separate from the aggregation results database, which I assume is in FULL recovery. While this wouldn't decrease the volume of logging, it would eliminate the need to back up the log generated by the data loads. (See my blog post here about logging, and some of the factors involved in choosing a database's recovery model.)

Separating the tables into another database would provide the flexibility to land the log file on a faster I/O subsystem, probably a small array of locally-attached solid-state drives.

Also, consider that SQL Server alone may not be the best solution to solve the problem. There are other RDBMS solutions that offer the ability to completely disable logging for certain tables. The data could be staged and aggregated in another system, and the results merged into the existing SQL Server database, which is well-protected by full logging and backups.

Answer 2

No, there is no way to prevent logging in SQL Server regardless of schema, table or database level.

Matter of fact every transaction that perform modifications or changes in SQL Server database is logged except version store related transactions involving TempDB when snapshot isolation is used. (logging in transaction log guarantees that transaction can be rolled back( For certain operation you can reduce logging (it is called minimally logged operations) by changing recovery model to BULK Logged --- you can learn more about it in SQL Server Books Online here

Answer 3

The answer is, as always, it depends.
When you said:

I have two tables that contain aggregate information. The information is constantly being updated

What kind of aggregation is that?
Try using a VIEW instead, no need to update, and no logging.

If that is not an option, try using shorter transactions, and backing up log between them.

Using a different Database means security administration overhead etc. and will have it's own transaction log (even with SIMPLE recovery model the log is written, SQL Server simply truncates the log automatically, but does not release disk space) so i would not recommend that option.