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.
Best Answer
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
SIMPLErecovery, separate from the aggregation results database, which I assume is inFULLrecovery. 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.