dump transaction options

the no_log option does exactly what truncate_only option does except that the operation of dumping the transaction itself is not logged. what is the use of logging that operation, can it be rolled back, and how much space it would take anyway?? where is the information logged? the dumptrdate column in the sysdatabases table changes irrespective of the options provided with the dump transaction command.
sajalkAsked:
Who is Participating?
 
jbiswasCommented:
All dump operations are written to the transaction log. The reason you cannot use the dump tran with truncate_only when the log is completely full is because that operation gets written to the log before it is actually truncated. However you don't have that space in the log. It is only in these situations that you should use the dump tran with no_log. No this cannot be rolled back but the tran log keeps info on all operations done, that affect the database. The dumptrdate changes on executing any kinda dump.

Use dump transaction with truncate_only to truncate the log when you are not concerned with the recovery of recent transactions (for example, in an early development environment)


Use dump transaction with no_log to truncate the log without recording the event  only when your usual method of dumping the transaction log (either the standard dump transaction command or  dump transaction with truncate_only) fails because of insufficient log space.

In production databases you should immediately dump the database after using either of these options.The dump transaction with no_log command frees very little space in the transaction log. If you continue to load data after entering dump transaction with no_log, it is possible to fill the log completely, causing any further dump transaction commands to fail. Use the alter database command to allocate additional space to the database.



All occurrences of dump tran with no_log are reported in the SQL Server error log. The message includes the user ID of the user executing the command. Messages indicating success or failure are also sent to the error log. no_log is the only dump option that generates error log messages.
0
 
sajalkAuthor Commented:
however, i still do not understand the purpose of using truncate _only option. especially, because the action is written to the transaction log which i have no way of seeing, whereas, with no_log, at least the nt error log would have the entry of the action. also, no_log gives you a little more room.
i would appreciate if care to reply.

thanx much.
sajal.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.