June 19, 2008 at 9:02 am
I have a question regarding when a database has a recovery set to Full. When a database file (mdf file) is changed for what ever reason and this action is written to the log file, is this database change actually stored in the database file or the log file or both?
If I run a simple update statement on a table in a database and then delete the database log file, did this update statement get recorded in the database?
Rich
June 19, 2008 at 9:08 am
The data change happens in the data file, but gets logged in the log file.
Think of the log file like a bank account's statement. The money doesn't come out of the stament, it comes out of the account, but the statement had better say that it came out and where it went to!
Full recovery mode means it keeps the records in the log till you do a backup of the log file. Simple recovery only keeps it in the log till the transaction is completed. (That's slightly over-simplified, but it's the basic idea.)
So, if you deleted the log file, the update would still be done in the data file. Your database would be messed up and unusable (it needs the log file to operate), but the data would have been changed.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 19, 2008 at 9:15 am
Ok - that is what I thought by looking for this online. Just wanted to be sure.
I used the delete log file as an example only.
Thanks for the reply on this.
Rich
June 19, 2008 at 9:50 am
Not to bore you with the details, but there is a chance that deleting a log file can result in lost data. All data modifications are written to the log file before they are written to disk. If you delete the log file before the dirty pages get flushed to the data file, you'll lose data. Here's a general breakdown of what happends when you change data.
SQL Server actually uses what is called Write-Ahead logging. The modifications are first made to the transaction log, then the data. To complicate things further, the changes are not actually made in the data file itself. They are made in the data cache, and then the cached data pages are written to disk at checkpoint or by the lazy write process when the data cache space is needed by another process. Log entries are always written to disk before the data file entry happens. Here's a general breakdown of the order of events.
1. Data modification statement made
2. Pages that need modified are read into the data cache
3. Data pages are modified
4. Log record is built in the log cache
5. Log flush process moves the log cache entries to the transaction log on disk
6. checkpoint or lazy write process moves the dirty page from cache to disk.
June 19, 2008 at 11:02 am
Your warning about deleting the log file is very true and was quite necessary.
To add to the boredom :)... sp_detachdb and a regular shutdown (either by issuing a shutdown or by stopping the service) both do checkpoints as part of their process. If the service fails for some reason or if someone issues a SHUTDOWN WITH NOWAIT to "speed up the process", the log files will be necessary for SQL Server insure the durability of the database in its consistent state previous to the shutdown.
So, when you issue the update it gets recorded in the log. If the server shuts down normally, the checkpoint process saves it to the data file. So, technically, you don't need the log file, but it seems risky to remove it just because.
If the server is shutdown abnormally (crashes) or "WITH NOWAIT" and you delete the log files, you'll likely be restoring to your last good backup.
Kyle
June 19, 2008 at 11:06 am
I actually ran into a situation where someone didn't want to keep "the audit logs" for the database and deleted all the log files. The reply to "restore to your last backup" was "I don't bother with backups, since I don't need to go back and get old data. That's the same reason I don't need the log files. Just tell me how to make the database work with no log files."
I suggested Excel instead of a database...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 19, 2008 at 11:11 am
June 19, 2008 at 11:17 am
It's clients like that you wish would migrate to the competition. 😀
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply