Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jun 2007
    Posts
    6

    Unanswered: How Does Mssql Work?

    I'm new to the forums, but have been utilizing forum responses from here for a long time. Anyway, here goes.

    I'm currently reverse-engineering 5 systems that utilize MSSQL 2000 for a backend database. Essentially, i need to find out how MSSQL manages system memory for table updates, deletes, etc. As well, how .mdf / .ldf files are structured (pre-allocated space, or allocated-on-the-fly???).

    My goal is to delete records in a table and then ensure that those records can't be retrieved (overwrite free space -- if this is even necessary)

    Thanks in advance for any help

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I've got rid of the two other dupe threads.

    As for the questions - at the most paranoid level you can shrink your files and then grow them again specifying that you want the "empty" space overwriting with 1s (or 0s - I forget) but this is assuming you need to protect the actual file disk itself. Do you really need to worry about this?

    Perhaps best to start at the beginning - why do you feel the need to reverse engineer SQL Server?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Just rebuild the clustered index, and you should be able to remove any traces. -> BUT <-
    When you delete a record from a table, the before image is recorded in the transaction log. The transaction log is REQUIRED for recoverability. If the system is in FULL recovery mode, you will not be able to remove the entries from the log, because this would compromise your recovery strategy for things like point in time restores. In a worst case scenario, you will end up with a corrupt database that can not be started, due to log inconsistencies. Does this fit in with the requirements?

  4. #4
    Join Date
    Jun 2007
    Posts
    6
    Thanks for responding so quickly pootle. I'll give it a shot with the shrink and grow. I'm sure i'll find the sp on Google. To answer your question, yes I do need to make sure that their is no evidence left on the disk itself.

    To give a little more background information, i'm developing a program for school that has some really "quirky" requirements. Basically the point of the program is to become familiar with SQL Server's storage architecture and developing ways to exploit it for security analysis.

    Another question I wanted to piggy-back off my previous was, is there any way to disable logging to the .ldf file? I want to make changes to the data file without affecting the log file (Although, I know that there is some sort of checksum executed against both files to make sure that they are "synched" before they are loaded up in server). Any ideas would be helpful.

  5. #5
    Join Date
    Jun 2007
    Posts
    6
    Quote Originally Posted by MCrowley
    The transaction log is REQUIRED for recoverability. If the system is in FULL recovery mode, you will not be able to remove the entries from the log, because this would compromise your recovery strategy for things like point in time restores. In a worst case scenario, you will end up with a corrupt database that can not be started, due to log inconsistencies. Does this fit in with the requirements?
    If the recovery mode is set to simple (which mine is), is there a way to remove log entries?

  6. #6
    Join Date
    Nov 2005
    Posts
    122
    Quote Originally Posted by rationalBean
    If the recovery mode is set to simple (which mine is), is there a way to remove log entries?
    Make sure that all open transactions are either committed or rolled back.
    Execute the CHECKPOINT command in QA and then shrink the log file.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by rationalBean
    Another question I wanted to piggy-back off my previous was, is there any way to disable logging to the .ldf file? I want to make changes to the data file without affecting the log file (Although, I know that there is some sort of checksum executed against both files to make sure that they are "synched" before they are loaded up in server). Any ideas would be helpful.
    No. But you can do various things to remove them as soon as they are written.

    I would read Kalen Delaney's Inside SQL Server 2005: The Storage Engine as a starting point. This isn't really something you will crack on a forum. You need some solid background understanding.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jun 2007
    Posts
    6
    Thanks Pootle. I'm kind of on a time-crunch, so i probably don't have time to go hunting for this book. When you mentioned growing the database file, were you referring to a DBCC function? I found all the documentation I need on shrinking. Thanks again for the quick reply

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I can not think of any such class that would have this as a requirement..

    You sould like an entry level student trying to do post grad stuff....stuff that our friends at M$ might know

    So....what's really with all the cloak and dagger stuff?

    You say you want to hide all the changes in the log and on the db?

    Fine, dump all the data out to flat files, move them off to another box, then format the hard drive, rebuild the box, then move the data back and load it.

    You really have to come clean and tell us what you're doing
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by rationalBean
    When you mentioned growing the database file, were you referring to a DBCC function? I found all the documentation I need on shrinking. Thanks again for the quick reply
    Alter database.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jun 2007
    Posts
    6
    Thanks for all the help pootle. I ended up shrinking the log file, and then using some hex editing software I had hanging around to "remove " evidence of modification.

    Quote Originally Posted by Brett Kaiser
    You sould like an entry level student trying to do post grad stuff....stuff that our friends at M$ might know

    You really have to come clean and tell us what you're doing
    Fine, i'm attempting to develop a worm that will be injected into the world database of "awesome dudes" and remove the people that I don't much care for.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rationalBean
    Fine, i'm attempting to develop a worm that will be injected into the world database of "awesome dudes" and remove the people that I don't much care for.
    would you kindly start here on this site, there are a number of in-duh-viduals that i'd like to remove as well

    hint: not to worry, none of them have made an appearance in this particular thread
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by r937
    would you kindly start here on this site, there are a number of in-duh-viduals that i'd like to remove as well

    hint: not to worry, none of them have made an appearance in this particular thread
    I know you do not like me, but must we play these games every few months. Or is this just my paranoia? There goes my paranoia about being paranoid.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sean, no smiley?

    no, i meant someone else, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    i do not use emoticons but i was jesting
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •