Help with delete

[andy]

I am having an issue with the way SQL server deletes data. I am
trying to delete data from the DB and have it completetly gone.

Here is what I did

1. added a table with a row of image data
2. inserted a text file into the row with a string i could search
for.
3. opened the mdf file up in a hex editor and found the data
4. deleted the row in sql server
5. reopened the file in the hex editor and the data was still there

no matter what i do i cant get rid of the data from the file. i have
tried shrinkdatabase, update and updatetext to overwrite the data and
nothing works.

anyone know how to REALLY delete things from an SQL DB?

Thanks

[Ton]

Andy,

You're delete is still in the transaction log and the lazy writer didn't
write to the datafiles.

The lazy writer writes to the datafiles, when the interval has exceeded or
when the buffer is full. So when you delete something it isn't directly
written to the datafiles. Also be carefull opening datafiles in a hexeditor,
because you will destroy everthing !!

To understand this you have to learn something about the architecture of
sqlserver.

Gr Ton
"andy" <aj*******@comcast.net> wrote in message
news:a8*************************@posting.google.co m...

I am having an issue with the way SQL server deletes data. I am
trying to delete data from the DB and have it completetly gone.

Here is what I did

1. added a table with a row of image data
2. inserted a text file into the row with a string i could search
for.
3. opened the mdf file up in a hex editor and found the data
4. deleted the row in sql server
5. reopened the file in the hex editor and the data was still there

no matter what i do i cant get rid of the data from the file. i have
tried shrinkdatabase, update and updatetext to overwrite the data and
nothing works.

anyone know how to REALLY delete things from an SQL DB?

Thanks

[andy]

Is there any way to bypass this functionality or force SQL server to
write the data? The only proven method I have is to export the entire
database and import it back into a new DB and that is extremely time
consuming.

How about setting the allow bulk insert flag on the DB or something?

"Ton" <t.****@wanadoo.nl> wrote in message news:<41***********************@news.wanadoo.nl>.. .

Andy,

You're delete is still in the transaction log and the lazy writer didn't
write to the datafiles.

The lazy writer writes to the datafiles, when the interval has exceeded or
when the buffer is full. So when you delete something it isn't directly
written to the datafiles. Also be carefull opening datafiles in a hexeditor,
because you will destroy everthing !!

To understand this you have to learn something about the architecture of
sqlserver.

Gr Ton
"andy" <aj*******@comcast.net> wrote in message
news:a8*************************@posting.google.co m...

I am having an issue with the way SQL server deletes data. I am
trying to delete data from the DB and have it completetly gone.

Here is what I did

1. added a table with a row of image data
2. inserted a text file into the row with a string i could search
for.
3. opened the mdf file up in a hex editor and found the data
4. deleted the row in sql server
5. reopened the file in the hex editor and the data was still there

no matter what i do i cant get rid of the data from the file. i have
tried shrinkdatabase, update and updatetext to overwrite the data and
nothing works.

anyone know how to REALLY delete things from an SQL DB?

Thanks