SQL Server Performance

Deleting records

Discussion in 'Getting Started' started by Rick H., Jan 5, 2010.

  1. Rick H. New Member

    When a record is deleted in the database, is it permanently deleted?
    That is --- can I say that there is NO way that data can be retrieved off a hard drive.
    Thanks
    Rick H.
  2. Reid New Member

    Rick,
    I came across this forum post that might give you some info, but seems like there is no simple way to retrieve it.
    http://database.ittoolbox.com/groups/technical-functional/sql-server-l/how-to-retrieve-deleted-record-in-ms-sql-2000-683644
    I know that a deleted record is logged in the transaction log and can be retrieve by programs (someone mentions one in the posts on the link above). Otherwise unless you have a backup and want to restore your DB to that backup's state you will have a tough time.
    Hope this helps.
  3. FrankKalis Moderator

    [quote user="Rick H."]
    When a record is deleted in the database, is it permanently deleted?
    That is --- can I say that there is NO way that data can be retrieved off a hard drive.
    [/quote]
    That really depends on your definition of "retrieve". When you issue a statement in SQL Server that "deletes" rows, these rows are deleted logically first. that means that at this point there is no way for you to retrieve them via any SQL statement or whatever. However, these rows are not yet deleted physically.They are "just" marked as deleted and have become what is called ghost records. These ghost records are then later on cleaned up by some specialized asynchronous task and only thereafter they are physically removed from the database. In the meantime between the delete operation and the kicking in of this task however I guess if you are really knowledgeable at how the SQL Server storage engine works and how to use a hex editor, you may be able to recover such a ghost record. So I guess the answer should be: There are ways outside of SQL Server that data can be retrieved off a hard drive, but for the vast majority of us mere mortals these ways are way too complex, dangerous & complicated to be feasable.
    Here is some more reading material on this topic:
    http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Ghost-cleanup-in-depth.aspx
    http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/23/644607.aspx
    http://weblogs.sqlteam.com/brettk/archive/2005/06/21/6214.aspx
  4. Rick H. New Member

    Thanks!!!
    Now I can assure management that no other steps are necessary after data is deleted.
  5. Adriaan New Member

    Management asking questions again?
    Mind you, deleted data may easily be retrieved from backups you are keeping. So by managerial logic, requiring that all deletions must be final, you should not keep any backups.
    [:cool:][:cool:][:cool:][:cool:][:cool:][:cool:][:cool:][:cool:][:cool:][:cool:][:cool:][:cool:][:cool:][:cool:][:cool:]
    Why bother with backups?
    [:cool:][:cool:][:cool:][:cool:][:cool:][:cool:][:cool:][:cool:][:cool:][:cool:][:cool:][:cool:][:cool:][:cool:][:cool:]
  6. Muhammad Imran New Member

    Aggreed with Frank Kalis that SQL Server keeps log for each deleted records.
    You can query these logs via 'fn_dblog' sql server function.
    Code:
    Select [RowLog Contents 0] FROM sys.fn_dblog(NULL, NULL) WHERE AllocUnitName = 'dbo.TableName'
    AND Context IN ( 'LCX_MARK_AS_GHOST', 'LCX_HEAP' ) AND Operation in ( 'LOP_DELETE_ROWS' )
    But this log is in Hex format. and you need to convert this Hex format to your actual data.
    Given below is the article that can help you to recover the deleted records in the same way defined above.

    http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/
  7. Luis Martin Moderator

    Welcome to the forums!
    Even this thread is almost 2 years old, we appreciate your collaboration:).
  8. jonsborn New Member

    The delete operation allows you to delete an existing database record. You can delete single records, a group of records, all the records in a table, or all the records in a database. When you delete a record, you assuredly discard the data you entered in all the fields in that record.

Share This Page