February 1, 2010 at 7:17 am
We have a classified database (SQL 2K8 Enterprise) that interacts with a "classic ASP" application. This app/database is classified for program "A". Folks can update information within this app as long as the information falls under program "A". However, if they happen to enter anything that doesn't fall under program "A", technically the entire app and database are now classified under that new program (say "B"). This is bad and is called a "spill". We have the authority to clean up these "spills" by removing the offending data, thus preserving the app/database as program "A".
Here's my issue. If I run a DELETE statement to remove the offending program "B" records, the "B" information is still in the transaction log. Is there any way to turn off transaction log entries for a single DELETE statement? At worse, we can truncate the log and lose anything since the last log backup. A small price to pay...
Second issue... I need to ensure that after the DELETE the record is no longer physically present in the SQL Server data files. Is this the case? Are the data files at the point where I could never recover that deleted record? If not, what can I do to ensure that the deleted records cannot be recovered?
Thanks in advance for any insight and advice...
February 1, 2010 at 7:24 am
milzs (2/1/2010)
We have a classified database (SQL 2K8 Enterprise) that interacts with a "classic ASP" application. This app/database is classified for program "A". Folks can update information within this app as long as the information falls under program "A". However, if they happen to enter anything that doesn't fall under program "A", technically the entire app and database are now classified under that new program (say "B"). This is bad and is called a "spill". We have the authority to clean up these "spills" by removing the offending data, thus preserving the app/database as program "A".Here's my issue. If I run a DELETE statement to remove the offending program "B" records, the "B" information is still in the transaction log. Is there any way to turn off transaction log entries for a single DELETE statement? At worse, we can truncate the log and lose anything since the last log backup. A small price to pay...
Second issue... I need to ensure that after the DELETE the record is no longer physically present in the SQL Server data files. Is this the case? Are the data files at the point where I could never recover that deleted record? If not, what can I do to ensure that the deleted records cannot be recovered?
Thanks in advance for any insight and advice...
Simple answers.
No, you can not turn off transaction logging.
The "spilled" records will still exist in the transaction log. They will be backed up in the transaction log backups. The space used in the ldf file will eventually be reused.
February 1, 2010 at 7:33 am
OK, so.....
(A) If I change the recovery model from FULL to SIMPLE, then perform the DELETE will the delete action not be logged? I can then change recovery back to FULL.
(B) Another idea -- could I do the DELETE, do a transaction log backup, then physically delete that log backup file? I understand the ramifications of data loss.
(C) I'm more worried about the MDF files. Are the records I DELETE physically removed from them? If so it is right away? Do I need to shrink the database first?
Thanks... Steve
February 1, 2010 at 7:48 am
milzs (2/1/2010)
OK, so.....(A) If I change the recovery model from FULL to SIMPLE, then perform the DELETE will the delete action not be logged? I can then change recovery back to FULL.
(B) Another idea -- could I do the DELETE, do a transaction log backup, then physically delete that log backup file? I understand the ramifications of data loss.
(C) I'm more worried about the MDF files. Are the records I DELETE physically removed from them? If so it is right away? Do I need to shrink the database first?
Thanks... Steve
(A) No, even when using the SIMPLE recovery model, transactions are still logged. Plus, this would break your transaction log backup chain and require you to run a full backup after switching back to the FULL recovery model.
(B) You could, but as you noted you'd break your transaction log chain again and need to run a full backup after deleting the transaction log backup. Question also comes up, how soon after the "spill" do you delete the offending record(s)? The insert/update to the database that resulted in the spill over will still be in the log files as well.
(C) Is the record actually deleted? Probably not, it is probably marked as deleted and the space eventually reused. Would shrinking the database help? Not really, in fact shrinking the database could impact your systems performacne due to index fragmentation and possibly file system fragmentation as the database has to expand again.
February 1, 2010 at 8:44 am
Even if someone did get a hold of a backup log, they couldn't simply read the transactions even with 3rd party log readers since (I believe) they need the database to obtain the metadata required to read the log, although you could encrypt the log backup as well. Since you are using 2008 Ent, you could always use Transparent Data Encryption and/ or column-level encryption to make sure that all records of the transactions are encrypted and even the data at rest would be obfuscated.
February 1, 2010 at 9:15 am
Why not to update this record with another information of the same size:"Maitenance record... Please Ignore" ?
February 1, 2010 at 9:31 am
When a row is deleted, the previous values are written into the transaction log. This cannot be prevented. The log entry will remain in the log, and readable with sufficient permissions, until that portion of the log is overwritten. That may happen quite some time after the log gets truncated (checkpoint in Simple recovery, log backup or explicit truncation in full)
Meanwhile in the data file, the spot on the page that the row occupied is marked as empty. It is not zeroed out or anything, just marked as empty. The previous values remain on the page, and readable with sufficient permissions, until something is written over that spot.
Both of those require, I believe, sysadmin permissions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2010 at 9:31 am
ns_nataly (2/1/2010)
Why not to update this record with another information of the same size:"Maitenance record... Please Ignore" ?
Unfortunately because of security rules, it doesn't work this way. Anyway, the record length could be variable since the main column is TEXT.
February 1, 2010 at 9:37 am
ns_nataly (2/1/2010)
Why not to update this record with another information of the same size:"Maitenance record... Please Ignore" ?
Won't help with the log problem. When a record is updated the old and new values are written into the transaction log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2010 at 9:52 am
So does anyone have any recommendations? The requirement would read like "Given one or more records in SQL Server 2008, these records need to be completely removed from the database and underlying file structure. After deletion, no remnants of the record(s) shall remain in the log files or the physical data files."
Thanks... Steve
February 1, 2010 at 10:27 am
I would say the only way to absolutely guarantee data deletion would be very extreme.
E.g. do the delete, create a new database and set up SSIS packages to import all of the data structures/data logically (not through backup/restore), then detach the previous database and use a secure wipe tool to delete the data files and logs.
February 1, 2010 at 10:30 am
To me it looks like an application issue. Unfortunatley, it appears it allows the wrong data to be entered into the wrong database. It maybe better to focus your efforts on the front end rather than the backend of the system.
February 1, 2010 at 10:38 am
Lynn Pettis (2/1/2010)
To me it looks like an application issue. Unfortunatley, it appears it allows the wrong data to be entered into the wrong database. It maybe better to focus your efforts on the front end rather than the backend of the system.
It's not really an app issue. The system is approved for program "A" to be worked. Users can send messages and enter information about program "A". If they "accidentally" (i.e. don't follow the rules) discuss another program other than "A", that's the problem. There's no way we could set the app up to prevent discussion of stuff outside program "A".
Thanks everyone for the advice. If you have any more, keep it coming... 🙂
Steve
February 1, 2010 at 10:59 am
milzs (2/1/2010)
So does anyone have any recommendations? The requirement would read like "Given one or more records in SQL Server 2008, these records need to be completely removed from the database and underlying file structure. After deletion, no remnants of the record(s) shall remain in the log files or the physical data files."
Export the data that you want to keep, and script all the DB objects. Detach the database. Use a secure deletion program to delete the data and log files and overwrite the sectors on the disk that the data and log files used to occupy. Create a new database. Recreate the objects and import all the data.
That is the only way that you can satisfy that requirement.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2010 at 11:03 am
milzs
MIght I suggest that you go to
http://technet.microsoft.com/en-us/default.aspx
and in the search window enter "ghost clean up"
In the list that follows you may find this of interest
There are many, many more references about 920 ... they may assist you in getting a better grasp on what SQL Server is doing "behind the scene" so to speak, and how effects what you are attempting
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply