Solved

dump transaction options

Posted on 1998-08-03
2
2,910 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:sajalk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 2

Accepted Solution

by:
jbiswas earned 50 total points
ID: 1089362
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
 

Author Comment

by:sajalk
ID: 1089363
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question