SQL Server Performance Forum – Threads Archive
Preventing Data from being recovered
Due to legal issues I have been asked to setup a db in which data transactions cannot be recovered.The data in the Db itself is ok but no info about the transactions or timing of transations should be recoverable. Not keeping a db backup and clearing the log should do it.
I run in Simple recovery mode but I’m not sure that the trans log is actually cleared until the next full backup is performed (which in this case is never). Can anyone suggest how to make sure to clear the Trans log or what else to do to meet this UNRECOVERABLE requirement? Thx in advance.
What do you mean that you don’t want data transactions recovered? The reason I ask this is that everytime there is a checkpoint, the data in the log is posted to the database, which means that the transactions are always there (at least until the checkpoint). If you want to clear the log, you can run TRUNCATE_ONLY option of the BACKUP LOG command. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
And also you can use DBCC SHRINKFILE to keep the limited size on Tlog. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Brad the issue is really related to timing.
What I want to be able to "unrecover" is not the data but when it was affected.
It’s a strange requirement for a UK projects, think of it as it’s ok to view the current data but NOT ok to see when changes were made. I’m also trying to avoid any sort of backup.
Keep in mind that sql ‘transactions’ are not the same as transactions against your data. If you store information in your tables in columns like ‘lastmodified’ or ‘lastupdated’ etc, then the information is in your data and anything you do to the sql transactions&log is mute. Sql transaction logs themselves are not easily read without a lot of knowledge and usually a third party tool. But I almost chocked when I saw that you wanted to avoid any sort of backup. Without a db backup (at least once a day) if your server were to fail, you would not only be missing history, you would be missing your current data too. If you dont want people to be able to see what was changed (even if they are able to access your backup drive and sql server) then backup your database daily and just replace the previous backup. Put the backup somewhere that no one but you or the sql admin account can get to it. Chris
If performance is not a primary requirement, you could augment your Simple Recovery Model with more frequent CHECKPOINTs to truncate the T-Log. You can use the sp_configure recovery model to influence the frequency of your CHECKPOINTs. Remember that no method will absolutely guarantee the realization of these legal requirements since transaction logging is an integral feature of SQL Server. If you really want to avoid transaction tracking, you should turn to either non-logged operations (not practical) or low-end databases that don’t perform useful logging like Access, FoxPro etc. Nathan H.O.
Moderator
SQL-Server-Performance.com
This is interesting, since most people do want just the opposite way.
Just curious, what transactions are you talking about? Frank
http://www.insidesql.de
http://www.familienzirkus.de
My solution I implemented is to have the Db in simple recovery mode and have a ClearLog stored procedure that runs every 10 mins.
The Procedure is pretty straight fwd: checkpoint
dbcc shrinkfile ([Db_log], 1)
]]>