| Author |
Topic |
|
MaverickUK
Yak Posting Veteran
89 Posts |
 Posted - 2005-01-17 : 04:27:12
|
Hi folksI've been working with SQL Server 2000 since it's release, although my main work is on the application size of things VB/C++/C# etc...Ever since I've worked with it, I've never quite understood transaction logs.This is what I believe about them currently* SIMPLE logs the least amount of information* FULL logs the most amount of information* They log actions against the database (INSERT statements, etc.. )What I don't understand is What are they used for? What is the *real* difference between SIMPLE and FULL logging? When should I use SIMPLE and when should I use FULL logging? What can I do when I end up with log files 10x the size of the data file? |
|
|
RM
Yak Posting Veteran
65 Posts |
Posted - 2005-01-17 : 04:50:15
|
| Search BOL for transaction logs and recovery models |
 |
|
|
MaverickUK
Yak Posting Veteran
89 Posts |
Posted - 2005-01-17 : 04:57:58
|
| Thanks for the suggestion, but I've already read through these and I'm still not completely straight on them.If they are used to make sure the database can recover from transactions, why does it keep on growing? Shouldn't the transaction log data be disguarded after a transaction is processed? |
|
|
|
MaverickUK
Yak Posting Veteran
89 Posts |
Posted - 2005-01-17 : 05:54:00
|
| Disregard above response please :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-17 : 18:26:55
|
| FULL logging means that all changes to the database are logged [there are some exceptions]. After a "disaster" you can recover from a previous full backup and then "apply" the tranaction logs, in sequence. You can also choose to apply a part of a log - e.g. to recover to a "point in time" - perhaps to a couple of minutes BEFORE the disaster occurred.Log backups can be "shipped" to a standby-server, so that in the event of failure of the main server the standby can take over - with only a short period of lost data.Log files can also be used for replication - the logged data can be sent to another server (either continuously, or periodically, and applied to that database so that it is kept in step with the primary database.With FULL logging enabled the transaction log will grow until a transaction backup is made, at which point the log is truncated. (The log can also be truncated without backing up, but this is rather extreme.). Failure to backup the log results in ... errmmm ... rather large log files!When SIMPLE logging is used the transactions in the log are basically discarded after each COMMIT is performed - thus there is no ability to apply the logs forwards from a full backup, but they don't grow ad infinitum either.Kristen |
|
|
|
MaverickUK
Yak Posting Veteran
89 Posts |
Posted - 2005-01-18 : 05:32:56
|
| Thanks KristenSo basically the role of thumb is, using SIMPLE logging when 100% data recovery isn't as important as keeping the log files small?Then using FULL logging when data integraty is of upmost importance?Another question, if I may. With FULL logging, with the log file always continue to grow? If so, how do people deal with this? Do they just keep adding disk space to a database server for as long as the database is active? |
|
|
|
MaverickUK
Yak Posting Veteran
89 Posts |
Posted - 2005-01-18 : 05:35:23
|
| Sorry, I really should read posts more carefully :( I see you've really answered what I've just asked.But to clarify. If I made a transaction log backup, would it then clear the transaction log? So the size of the transaction log would still be as large as it was (unless a SHRINK command was run), but it would be empty? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
|
|
RM
Yak Posting Veteran
65 Posts |
Posted - 2005-01-18 : 05:55:14
|
| As per BOL When SQL Server finishes backing up the transaction log, it automatically truncates the inactive portion of the transaction log. This inactive portion contains completed transactions and so is no longer used during the recovery process. Conversely, the active portion of the transaction log contains transactions that are still running and have not yet completed. SQL Server reuses this truncated, inactive space in the transaction log instead of allowing the transaction log to continue to grow and use more space. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-18 : 13:45:51
|
| "So the size of the transaction log would still be as large as it was (unless a SHRINK command was run"Yes, as RM has described.Note that shrinking is not advised - the effort to re-extend the log is additional overhead, and like-as-not the log will grow back to that size within a few days.However, after a massive deletion, or some other exceptional activity, SHRINKing might be worthwhile.Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-18 : 14:05:15
|
| >> When SQL Server finishes backing up the transaction log, it automatically truncates the inactive portion of the transaction log.Sort of true but not immediately.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
SQL Server transaction logs